Wir alle wissen um den Operator LIKE als festen Bestandteil der SQL-Befehle. Jeder Entwickler nutzt LIKE in seinen Abfragen, um Wildcard-Suchen durchzuführen. Häufig habe ich aber auch erlebt, dass Programmierer – pauschal – den LIKE-Operator verwenden. Damit will man sicherstellen, dass sowohl Begriffe mit Wildcard-Elementen oder ohne diese Elemente gesucht werden können. In diesem Moment kann der LIKE-Operator für die Performance der Abfrage zu einem Fiasko werden?
LIKE-Operator in Microsoft SQL Server
Der LIKE-Operator in Microsoft SQL Server wird verwendet, um Zeichenfolgenmuster in Datenbanktabellen zu suchen. Er ermöglicht es, nach Zeichenfolgen zu suchen, die einem bestimmten Muster entsprechen, wobei Platzhalter wie ‚%‘ (für Null oder mehr Zeichen) und ‚_‘ (für ein einzelnes Zeichen) verwendet werden können.
Testumgebung
Eine Tabelle für Kunden enthält ein Attribut [c_nationkey] vom Datentypen INT. Dieses Attribut speichert den Referenzwert zu einem Land, in dem der Kunde ansässig ist. Die Testtabelle enthält 1.600.000 Datensätze aus der Demo-Datenbank ERP_DEMO.
CREATE DATABASE demo_db;
GO
USE demo_db;
GO
SELECT * INTO dbo.customer
FROM ERP_Demo.dbo.customer;
GO
CREATE NONCLUSTERED INDEX x1 ON dbo.customer (c_nationkey);
GO
Abfragen auf das Attribut [c_nationkey] sollten – bei kleinen Datenmengen – immer den Index präferieren und mit Hilfe eines NESTED LOOP Operators die fehlenden Attribute aus der Tabelle mit einem RID-Lookkup ermitteln.
Problembeschreibung
In der Tabelle werden regelmäßig Abfragen auf das Attribut [c_nationcode] durchgeführt (ca. 1.000.000 in 12 Stunden). Das Problem ist der Code, mit dem die Abfrage generiert wird. Da auch andere Attribute abgefragt werden, wurde ein generischer Ansatz gewählt, bei dem sowohl das Attribut als auch der Suchwert dynamisch als SQL-Code implementiert wird. Um mögliche Wildcard-Abfragen ebenfalls zu berücksichtigen, wurde der Code so gestaltet, dass sowohl das abzufragende Attribut als auch der Parameterwert dynamisch sind.
/* Template für die Generierung der Abfrage */
SELECT * FROM dbo.customer WHERE {fieldname} LIKE @P0;
Wenn eine Abfrage auf das Attribut c_nationkey durchgeführt wurde, ergab die Konkatenation des Templates folgenden Aufruf:
/* Suche nach Ländercode */
DECLARE @P0 NVARCHAR(MAX) = N'47'
SELECT * FROM dbo.customer WHERE c_nationkey LIKE @P0;
Der Datentyp für die Variable @P0 musste ein String-Datentyp sein, da ansonsten der LIKE-Operator nicht einzusetzen ist. Der aus dieser Abfrage resultierende Ausführungsplan zeigt einen FULL-Scan auf die Tabelle und einen Hinweis darauf, dass einen Typenkonvertierung durchgeführt werden muss.
Moment – warum wird eine Typenkonvertierung zu einem NVARCHAR durchgeführt? Die Typenhierarchy von Microsoft SQL Server sieht vor, dass numerische Datentypen vor textbasierten Datentypen priorisiert werden (Precedence of Data Types). Die Priorisierung legt fest, das ein Datentyp, der in der Hierarchie unter einem anderen Datentypen liegt, zu dem höherliegenden Datentypen konvertiert werden muss.
Folgt man dieser Hierarchie-Ordnung, dann sollte es eigentlich keine Probleme mit dem Datentypen NVARCHAR geben.
NVARCHAR liegt an Position 25 und ist nachranging zum Datentypen INT. Warum also bricht Microsoft SQL Server bei dieser Abfrage die Regel?
Lösung
Das Problem in dieses kuriosen Verhaltens ist weder der falsch deklarierte Datentyp des Parameterwertes noch die Anzahl der möglichen Datensätze. Das Problem ist der LIKE-Operator. Rezitiert man noch einmal die Implementierung des LIKE-Operators, stößt man auf die folgende Aussage:
Er ermöglicht es, nach Zeichenfolgen (ASCII und UNIODE) zu suchen
https://learn.microsoft.com/de-de/sql/t-sql/language-elements/like-transact-sql
Der LIKE-Operator sucht nach ZEICHENFOLGEN! Das bedeutet für die Abfrage des oben gezeigten Beispiels, dass die Daten des Attributs [c_nationkey] zu einem NVARCHAR() umgewandelt werden müssen bevor LIKE-Operator die Daten mit dem Parameterwert vergleichen kann und die passenden Datensätze zurück liefert. Wenn der Datentyp eines Attributs konvertiert werden muss, führt das IMMER zu einem FULL SCAN, da es alle Datensätze in der Tabelle betrifft.
Nachdem der Fehler ermittelt und die Abfrage leicht verändert wurde, wurde ein Ausführungsplan erzeugt, der den Erwartungen entsprach.
DECLARE @P0 NVARCHAR(MAX) = N'47'
SELECT * FROM dbo.customer WHERE c_nationkey = @P0;
Natürlich ist auch diese Abfrage noch nicht perfekt, da erneut eine Typenkonvertierung durchgeführt werden muss. Jedoch ist dieser Ausführungsplan deutlich performanter, da nicht mehr die Inhalte des Attributs konvertiert werden sondern der Parameterwert (NVARCHAR) wird nun korrekt zu einem INT konvertiert und der Index kann effektiv genutzt werden.
Herzlichen Dank fürs Lesen!
