SSIS – binäre Collation bei der Suche

In einem früheren Blog-Eintrag habe ich beschrieben, wie die Suche im SQL Server Integration Services (im Standard) abhängig von der Groß- und Kleinschreibung ist. Das liegt daran, dass der Cache im Integration Services binär angelegt werden, so dass „Martin“, „martin“ und „MARTIN“ 3 unterschiedliche ELemente sind, wohingegen bei einem SELECT DISTINCT Vorname FROM Personen dies (in der Standard-Collation) nur als einen Wert zurückliefern würde.

Dieses Phänomen tritt aber natürlich nicht nur bei Groß- und Kleinschreibung auf, sondern bei allen Strings, die laut Collation gleich sind.

Damit führt das Standard-Szenario nicht zum gewünschten Ergebnis, wenn ich zum Beispiel Vornamen in eine Dimensionstabelle umsetzen will:

Paket-Beispiel Vornamen auf IDs umsetzen

Laden der Dimensionen (Vorname)Beispiel für Faktenimport

 

In meinem Beispiel habe ich die Personen-Tabelle wie folgt gefüllt:

Inhalt der Tabelle Personen

Auf der Nachnamen-Spalte habe ich die Standard-Collation (SQL_Latin1_General_CP1_CI_AS) verwendet, die Vornamen-Spalte habe ich so eingestellt, dass auch Akzente ignoriert werden (Collation Latin1_General_CI_AI) (OK, das ist ein bisschen gestellt, aber in der Praxis hatte ich einen ähnlichen Fall).

Somit liefert select distinct vorname from person:

SELECT DISTINCT Vorname FROM Person

und select distinct nachname from person:

SLECT DiSTINCT Nachname FROM Person

Man beachte, dass bei der Standard-Collation ß wie ss behandelt wird.

Nun ist klar, dass beim Lookup nach Vorname im Faktenimport nicht alle Vornamen einen Treffer liefern und somit das ETL einen Fehler schmeißt.

Außerdem ist es vermutlich nicht sinnvoll, dass Michel und Michél gleiche Vornamen sind. Deswegen beitet es sich in diesem Fall an, dass alle unterschiedlichen Schreibweisen der Vornamen auch unterschiedliche Dimensionselemente werden. Dazu kann man im Dimensions-SELECT statt „SELECT DISTINCT Vorname FROM Personen“ besser „SELECT DISTINCT vorname COLLATE Latin1_General_BIN as Vorname from person“ verwendet. Damit instruiert man den SQL Server die binäre Collation zu verwenden und somit sind beim SQL Server selbst die einzelnen Vornamen nicht mehr gleich – und schon geht’s.

Eine andere Alternative wäre gewesen, über eine Funktion Strings, die gleich sein sollen, in einen eindeutigen String zu überführen (wie in meinem letzten Blog-Eintrag mit UPPER()). Bei Akzenten ist das aber nicht so einfach.

Eine weitere Alternative ist, den Lookup nicht mit vollem Cache zu machen, was aber negative Auswirkungen auf die Performance hat. Also könnte man auch zuerst einen Lookup mit vollem Cache und im Fehlerfall einen nachgelagerten Lookup ohne Cache ausführen. Im zweiten Lookup kann man dann über die Collation genau definieren, welche Strings gleich sein sollen und welche nicht.

In einem solchen Fall muss man also auf jeden definieren, welche Strings im DWH als gleich angesehen werden sollen. Über die COLLATION kann man das – wie gezeigt – sehr fein einstellen.

Natürlich sind solche Szenarien selten, da in der Regel nicht beliebige Freitextwerte in einer Dimension vorkommen – aber, wie in meinen Projekten geschehen, ab und zu gibt es dann doch solche Fälle.

Performance und GUIDs

In einem Kunden-Projekt sollte ich die Performance der SQL-Zugriffe einer .NET-Applikation verbessern.

Hier beschreibe ich die Ergebnisse, da sie sich auch auf andere Szenarien verallgemeinern lassen.

Die erste Veränderung war, keine GUIDs als clustered primary keys zu verwenden. Stattdessen setzen wir nun ints (mit Identity) ein. Dies führt zu einer immensen Beschleunigung bei den INSERTs in die Datenbank. Dies ist sehr gut nachvollziehbar, da nun die INSERTs immer am Ende der Tabelle statttfinden, so dass kein zeitaufwändiges Umorganisieren der Seiten innerhalb der Tabelle notwendig wird.

Leider ging es aber in meiner Aufgabe um die SELECT-Performance und nicht um die INSERT-Performance. Aber auch diese verbesserte sich durch die Verwendung der integer-Werte deutlich (Abfragezeit ungefähr halbiert). Als Test verwendete ich die Abfrage von 500 Datensätzen, die ich zu Beginn zufällig ausgewählt hatte. Vor jeder Abfrage wurde natürlich der Cache geleert 🙂 Die Steigerung lässt sich dadurch erklären, dass alle Indizes nun vom Platzbedarf viel kleiner wurden (1 int = 4 byte, 1 guid = 16 byte –> ca. 4x so viele Daten gehen auf eine Index-Page [natürlich abhängig von den weiteren Feldern des Index]). Außerdem ist ein Zähler besser verteilt als eine Guid.

Als nächste Verbesserung verwendete ich in m:n-Tabellen als clustered primary key nicht einen Zähler, sondern einen zusammengesetzten Schlüssel aus den beiden referenzierten Tabellen (+ ein weiteres Feld, um die Eindeutigkeit sicherzustellen). Dabei verwendete ich als erstes Feld das Feld der beiden, das in den meisten Abfragen bekannt ist. (Auf der umgekehrten Reihenfolge lag natürlich auch ein Index). Dadurch muss beim Standard-Zugriff nicht mehr über einen non-clustered Index zugegriffen werden, wodurch ein Zugriff eingespart wird. Dies brachte eine weitere Halbierung der Zugriffszeit.

ALs letztes gab es spezielle Szenarien, in denen nach Texten gesucht werden musste – ein Beispiel: Man möchte alle Aufträge ermitteln, die in einer Position einen bestimmten Positionsfreitext enthalten. Dann wird folgendes SQL-Statement abgesetzt:

SELECT * FROM Auftrag a INNER JOIN Auftragsposition pos on pos.AuftragID = a.AuftragID WHERE pos.Positionsfreitext like ‚Test%‘

Natürlich war auf der Auftragspositions-Tabelle ein Index auf Positionsfreitext. Ich erweiterte diesen Index um die AuftragID. Dadurch kann der Join direkt über den Index abgewickelt werden und ein Zugriff auf die Tabelle wird eingespart, was für diesen Spezialfall ebenfalls eine deutliche Performance-Steigerung einbrachte.