Entfernen der String-Duplikation in einer SQL Server 2008-Datenbank

Question

Hintergrund: Professioneller Werkzeugentwickler. SQL/DB-Laie.

Einrichten: .Net 3.5 Winforms-Anwendung, die mit MS SQL Server 2008 kommuniziert.

Szenario: Ich befülle eine Datenbank mit Informationen, die ich aus einer großen Anzahl von Dateien extrahiert habe. Dabei handelt es sich um etwa 60 Millionen Datensätze, denen jeweils eine Nachricht beliebiger Größe zugeordnet ist. Mein ursprünglicher Plan war ein nvarchar(max) Feld im Datensatz, um die Nachrichten zu speichern, aber nach einem Testlauf mit einer Teilmenge der Daten würde dies die Datenbank zu groß machen (hochgerechnet 113 GB). Beim Ausführen einiger Abfragen auf diesem anfänglichen Testdatensatz (1,3 GB Datenbank) entdeckte ich, dass es eine beträchtliche Menge an Nachrichtenduplikaten gab und dass wir dies nutzen konnten, um die Nachrichtendaten auf etwa ein Sechstel zu verkleinern. Ich habe einige Ansätze ausprobiert, um dies zu erreichen, aber keiner war zufriedenstellend. Ich habe jetzt ein paar Tage lang herumgesucht, aber entweder a) scheint es keine gute Antwort zu geben (unwahrscheinlich), oder b) ich weiß nicht, wie ich das, was ich brauche, gut genug ausdrücken kann (wahrscheinlicher).

In Betracht gezogene/erprobte Ansätze:

1. Masseneinfügung von Nachrichten in Datensätze mit einer nvarchar(max) Feld. - zu viel Redundanz aufweisen.
2. Bleiben Sie bei dieser Meldungsspalte, aber finden Sie einen Weg, die Datenbank dazu zu bringen, die Meldungen zu "komprimieren". - keine Ahnung, wie man das macht.
3. Fügen Sie eine Nachrichtentabelle für eindeutige Nachrichten hinzu, deren Schlüssel eine ID ist, auf die der/die Hauptdatensatz/e "zeigen". - funktioniert zwar prinzipiell, aber die Umsetzung der Eindeutigkeit erweist sich als mühsam und verlangsamt sich, wenn mehr Nachrichten hinzugefügt werden.
4. Entfernen Sie Duplikate auf dem Client. - erfordert, dass alle Nachrichten für jede Bevölkerungsgruppe an den Kunden weitergeleitet werden. Dies ist nicht skalierbar, da sie in den Speicher passen müssen.
5. Hinzufügen einer zusätzlichen (indizierten) Hash-Spalte zur Nachrichtentabelle und Übermittlung der Nachrichten mit einem entsprechenden (lokal erzeugten) Hash-Wert. Suchen Sie danach, um die Nachrichten einzugrenzen, die tatsächlich getestet werden müssen. - kompliziert, es muss einen besseren Weg geben.

Dieser dritte Ansatz läuft auf die Erstellung einer String-Dictionary-Tabelle hinaus. Nach einigen Iterationen dieser Idee kam ich zu folgendem Ergebnis:

1. Die Datenbank hat eine Nachrichtentabelle, die eine (automatisch zugewiesene) int ID Primärschlüssel zu einer nvarchar(max) Nachricht.

2. Der Client stapelt die Nachrichten und übergibt mehrere Datensätze zum Einfügen an eine gespeicherte Prozedur.

3. Die gespeicherte Prozedur iteriert durch den Stapel eingehender Datensätze und für jede Nachricht:

   i. Die Tabelle des Nachrichtenverzeichnisses wird auf eine vorhandene Instanz der Nachricht geprüft (SELECT).

   ii. Falls gefunden, merken Sie sich die ID der vorhandenen Nachricht.

   iii. Wenn nicht gefunden, fügen Sie einen neuen Nachrichtendatensatz ein und merken Sie sich die ID des neuen Datensatzes (OUTPUT).

4. Die IDs für alle Nachrichten (alte und neue) werden als Ergebnismenge von der Prozedur zurückgegeben.

5. Der Client erzeugt die Haupttabellensätze mit Einträgen ( int Fremdschlüssel) für die Nachrichten, die mit den von der Prozedur zurückgegebenen IDs ausgefüllt werden.

Probleme:

1. Die Suche nach vorhandenen Nachrichten wird mit zunehmender Anzahl von Nachrichten immer langsamer und wird zum begrenzenden Faktor.
2. Ich habe versucht, Indizierung (UNIQUE) die Nachricht Spalte, aber Sie können nicht indexieren eine nvarchar( max ) Spalte.
3. Ich habe mir die Volltextsuchfunktionen von MS SQL Server 2008 angesehen, aber das scheint mir zu viel des Guten zu sein.
4. Ich habe darüber nachgedacht, zu versuchen, die Nachrichten in Stapeln zusammenzufassen, aber ich sehe keine Möglichkeit, die entsprechende Liste der IDs (alte und neue) leicht zu erhalten, in der richtigen Reihenfolge ) an den Kunden zurückzugeben.

Ich habe den Eindruck, dass ich versuche, eine Art Normalisierung meiner Daten zu erreichen, aber nach meinem Verständnis von Datenbankdesign ist dies eher eine "Zeilennormalisierung" als eine echte Normalisierung, bei der es um eine "Spaltennormalisierung" geht. Ich bin überrascht, dass dies nicht schon überall mit entsprechender Unterstützung benötigt wird.

Meine Frage lautet also: Was ist hier der richtige Ansatz?

Jede Hilfe ist sehr willkommen.

Sam

Answer 1

Sam, ich glaube, du warst auf der richtigen Spur mit Ansatz Nr. 5 . Und ich glaube wirklich nicht, dass es so kompliziert zu implementieren wäre, wie Sie vielleicht denken. Ein lokal erzeugter Nachrichtenhash ist leicht zu erstellen und würde alle Suchvorgänge (in der Datenbank) erheblich beschleunigen.

Das gilt natürlich nur, wenn die Nachrichten wirklich eine nvarchar(max) . Wenn Sie mit weniger Platz auskommen können (512 Ich denke für nvarchar ), dann könnten Sie Eindeutigkeitsbeschränkungen in SQL und einen Index auf die Spalte setzen, was die Suche sehr viel schneller machen würde - definitiv meine Empfehlung, wenn Sie denken, dass Sie die Nachrichtenlänge reduzieren könnten.

Wenn Sie sich für den Ansatz des Nachrichten-Hashes entscheiden, glaube ich, dass Sie eine clevere Technik anwenden können, um die Dinge zu beschleunigen. Verwenden Sie die Masseneinfügung, um alle Datensätze in die Datenbank einzufügen, ohne sich um doppelte Nachrichten zu kümmern. Danach könnten Sie eine recht einfache Abfrage schreiben, um die Nachrichtentabelle von doppelten Nachrichten zu bereinigen, und dann die eindeutigen Beschränkungen weiterhin durchsetzen.

Answer 2

Sie hatten die Lösung in Ihrem Artikel. Bei großen Daten wie nvarchar(max) müssen Sie die Suchmenge reduzieren - wie Sie sagten:

Hinzufügen einer zusätzlichen (indizierten) Hash-Spalte zu die Nachrichtentabelle und übermitteln Sie die Nachrichten mit einem entsprechenden (lokal erzeugten Hash-Wert. Suchen Sie auf dieser um die Nachrichten einzugrenzen, die gegen die tatsächlich getestet werden muss. - kompliziert, da muss es einen besseren Weg geben.

Dies ist der Weg zur Lösung des Problems.

Oder wenn Sie nicht mit Hashes arbeiten wollen, machen Sie die ersten 150 Zeichen oder so zu einem Hash (z.B. varchar(150)), um die Suche nach Duplikaten zu reduzieren. Es wird nicht ganz so eindeutig sein wie ein Hash, aber je nach Ihren Daten könnte es funktionieren. (Sie könnten auch 75 der ersten Zeichen und 75 der letzten Zeichen verwenden). Einige Tests der Daten sollten Ihnen zeigen, welche Teilstrings am eindeutigsten sind.

Answer 3

Es gibt zwei praktische Aspekte (und Gründe) für die Normalisierung: die Sensibilität der Datenanordnung (und der entsprechende Wartungsvorteil) und die Leistung.

Was die Sensibilität betrifft, so müssen Sie zumindest aus der Perspektive des abstrakten DB-Designs berücksichtigen, ob die Daten wirklich doppelt vorhanden sind oder nicht. Auch wenn Sie zwei Nachrichten mit identischen Daten haben, stellen sie in Wirklichkeit nicht unbedingt "dasselbe" dar. Die eigentliche Frage ist: Macht die Tatsache, dass zwei Nachrichten denselben Text haben, sie zu derselben Nachricht? Mit anderen Worten: Angenommen, Nachricht A und Nachricht B haben denselben Text, würden Sie dann wollen, dass eine Änderung in Nachricht A auch in Nachricht B erscheint?

Wenn Ihre Antwort "ja" lautet, dann ist Ihr String-Wörterbuch der richtige Ansatz. Wenn nein, dann brauchen Sie nicht まったくもって Es gibt keine doppelten Daten, nur Daten, die gleich aussehen, es aber nicht sind.

Aus Sicht der Leistung würde ich wahrscheinlich denken, dass das Zeichenkettenwörterbuch mit dem zusätzlichen Nachrichtenhash der beste Ansatz wäre; ich glaube nicht, dass dies wirklich so kompliziert ist, wie Sie es betrachten. Standard-Hash-Algorithmen sind in praktisch jeder Sprache (einschließlich T-SQL) verfügbar, und ich würde die Möglichkeit von Kollisionen oder sogar Verteilung von Hash-Werten in diesem Szenario nicht als furchtbar wichtig betrachten, da Sie es nur als "Hinweis" verwenden, um die Ausführung einer Abfrage zu beschleunigen.