Tagcloud am SQL Server vorbereiten - ohne extra Tabelle

Question

Hallo zusammen,

ich experimentiere schon geraume Zeit, komme aber nicht weiter. In einem Forum soll eine Tagcloud erstellt werden nach folgenden Bedingungen:

1. es dürfen nur Nomen verwendet werden (erster Buchstabe muss groß, die folgenden bis zum nächsten Leerzeichen kleingeschrieben sein)
2. es darf keine zusätzliche Tabelle herangezogen werden, keine zusätzlicher Wartungsaufwand
   
3. die Wörter dürfen nur aus dem öffentlichen Bereich des Forums stammen
4. es dürfen nur die 30 am häufigsten Vorkommen verwendet werden
5. Floskeln, wie Anreden oder dergleichen müssen rausgefilter werden
6. Letztendlich soll die TC eine View auf der Tabelle sein

Die Punkte 3, 4, 5 und 6 sind klar und einfach. Die ersten Beiden kosten mich meine grauen Zellen.

Gespeichert sind alle Nachrichten die der Tabelle "Posts", Spalte ist "Message" vom Typ ntext. Die Spalte / Tabelle hat entsprechende Indizes und hat etwa 70000 Zeilen, pro Tag etwa 100 neue Zeilen im Schnitt.

Ich habe zwar eine extra tabelle für die Tags angeregt, stößt aber auf taube Ohren, das Ganze muss vollautomatisch geschehen.

Habt ihr Ideen dazu?

Vielen Dank,

Stephan

Answer 1

Hallo Stephan,

a) ntext ist schlecht. Nimm nvarchar( MAX )

b) Eigene Tabelle ist gut, volldynamisch ist für die Performance schlecht

c) Volldynamisch schließt eine eigene Tabelle nicht aus. Lediglich beim speichern eines Eintrags muss dann eine Routine laufen, die die passenden Bestandteile rausfiltert und in die Tabelle einträgt.

d) Ich würde zwei neue Tabellen vorschlagen, eine für die Keywords, eine für die Verlinkung zum Posting. Alternativ kann man auch eine Zählspalte einbauen, das führt aber dazu, dass man beim Suchen nicht auf die Posting IDs zurückgreifen kann, sondern eine performancetechnisch suboptimale Abfrage per LIKE machen muss.

Wenn die Forenbetreiber keine neue(n) Tabelle(n) wollen, würde ich persönlich sagen, sie sollen es selbst machen :)

Das "Argument" "kein zusätzlicher Wartungsaufwand" ist kein Argument, sondern eine (schlechte) Ausrede. Der Wartungsaufwand ist bei dem, was Du hier als Vorgaben hast, höher als bei Verwendung von zusätzlichen Tabellen. Insbesondere das immer und immer wieder stattfindende Parsen der Inhalte und die Anforderung, bestimmte Worte dann auch noch aus der Zählung rausnehmen zu können, macht das ganze nicht einfacher, sondern erheblich schwerer und vor allen Dingen zieht es die Performance extrem runter.

[Tags]
ID - int (PK)
Keyword - nvarchar( 255 )
Exclude - bit (Angabe, ob Keyword von Zählung ausgeschlossen werden soll)

[TagToPostings]
ID - int (PK)
TagId - int (FK zu Tags.ID)
PostingId - int (FK zu Postings.ID)

Das Parsen würde ich wahrscheinlich in der Anwendung machen, kommt aber auch aufs verwendete Forensystem an. Dort hat man in der Regel per Regex erheblich mehr Möglichkeiten, das Ganze performant zuparsen und die Keywords dann wegzuschreiben. Alternativ wird die Postingtabelle um ein Flag erweitert, das angibt, ob der Postinginhalt bereits geparst wurde oder nicht. Eine separate Anwendung schaut dann periodisch nach, ob es neue oder geänderte Postings gibt und falls ja, wird die Verarbeitung angestoßen und die Keywords werden ausgelesen, aus der Tags Tabelle ermittelt, neue Keywords ggfs. dort eingetragen und in der TagToPostings Tabelle hinterlegt.

P.S.: Alles andere würde ich an deiner Stelle gar nicht erst versuchen.

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 2

Hallo Stephan,

das stimme ich Stefan zu, so was On-the-fly zu ermitteln ist ein Ding der Unmöglichkeit. Text Operationen sind so schon teuer, zum richtigen Parsen gibt es in T-SQL auch nicht so die herausragenden Möglichkeiten und Du müsstest es grundsätzlich immer auf die gesamte Tabelle anwenden; da wird ein Forenanwender schon mal ein paar Minuten warten müssen, bis er ein Ergebnis sieht.

Eine Option ist eben eine separate Tabelle mit den Tag. Als On-Board Hilfsmittel könntest Du einen Volltext-Index verwenden, der parst den Text für Dich bereits im Hintergrund und über eine Stopword-List kannst Du festlegen, welche Begriff nicht indiziert werden sollen. Über die DMV sys.dm_fts_index_keywords (Transact-SQL) => Spalte "document_count" kannst Du dann ermitteln, wie oft ein Begriff indiziert wurde. Die DMV ist aber nicht indiziert, also solltest Du regelmäßig den Inhalt der DMV in eine eigene Tabelle übertragen, wo die Spalte eben indiziert ist; dann kannst Du performant die Top 30 ermitteln.

Hat noch einen netten Nebeneffekt: Du kannst den Volltextindex auch gleich für eine Foren-Suchfunktion verwenden.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]