Substring vs. LIKE

leave a comment »

Wenn eine TSQL-Aufgabe in der WHERE Klausel einer Abfrage mit der SUBSTRING Funktion oder dem LIKE Operator implementiert werden kann, soll man sich für die Variante mit dem LIKE Operator entscheiden. Entweder ist diese Option besser oder beide Varianten performieren gleich gut.

Eine SUBSTRING Variante ist nie besser als der LIKE-Operator. Deshalb wird es empfohlen (aufgrund der Performance), den LIKE Operator zu verwenden. Außerdem, ist die SUBSTRING-Option fehlerfreundlich und nicht intuitiv.

Das ist eine Ableitung einer generellen Empfehlung, dass ein Funktionsaufruf in der WHERE Klausel, wo eine Tabellenspalte als Funktionsargument verwendet wird, durch einen entsprechenden Operator ersetzt werden soll.

Code-Beispiel

Als Beispiel nehmen wir die Tabelle Person.Contact aus der AdventureWorks Datenbank. Falls ein Index auf die Spalte LastName nicht existiert, legen wir einen an:

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name='IX_Contact_LastName')
CREATE NONCLUSTERED INDEX IX_Contact_LastName ON Person.Contact (LastName)

Jetzt sind wir sicher, dass der entsprechende Index existiert und wir können zwei Varianten einer Abfrage probieren.

Abfrage 1 (low selectivity)

Zunächst holen wir alle Kontakten mit Nachnamen, die mit A anfangen. Hier sind die Abfragen:

SELECT * FROM Person.Contact WHERE SUBSTRING(LastName, 1, 1)='A'
GO
SELECT * FROM Person.Contact WHERE LastName LIKE 'A%'

Werfen wir nun einen Blick auf die Ausführungsinformationen:

Beide Abfragen sind logisch äquivalent und liefern 911 Zeilen. Das sind ungefähr 4,5% aller Zeilen, aber mit allen Spalten. Deshalb hat der SQL Server geschätzt, dass die Verwendung des Indizes IX_Contact_LastName und entsprechende Key-Lookups teuer sind und sich für das Scannen der ganzen Tabelle entschieden. Die Abfrage ist nicht genug „selektiv“ für eine Indexverwendung und aus diesem Grund ist es egal, wie die WHERE Klausel implementiert ist.

Abfrage 2 (high selectivity)

Wiederholen wir jetzt diese Aufgabe mit der Änderung, dass der Nachname mit Acho beginnen soll. Hier sind die biden angepassten Abfragen:

SELECT * FROM Person.Contact WHERE SUBSTRING(LastName, 1, 4)='Acho'
GO
SELECT * FROM Person.Contact WHERE LastName LIKE 'Acho%'

Selbstverständlich sind die Abfragen wieder logisch äquivalent. Diesmal liefern sie nur eine Zeile zurück – diese Abfrage ist sehr „selektiv“. Das ist ein Zeichen für den SQL Server Optimizer, dass eine Indexverwendung sinnvoll wäre. Er hat entschieden den Index IX_Contact_LastName zu verwenden. Betrachten wir die Ausführungsdetails:

Wir sehen, dass in beiden Fällen der Index verwendet wird, aber nur die LIKE-Variante verwendet den Index-Seek Operator. In der ersten Abfrage wird die SUBSTRING Funktion für jede äußere Zeile evaluiert und deshalb musste der SQL Server Optimizer den Index-Scan Operator verwenden.

Die LIKE-Variante limitiert den Optimizer nicht und er konnte den besten Plan generieren. Nur fünf Seiten hat der SQL Server lesen müssen, um das Ergebnis liefern zu können. In der ersten Variante waren 60 Seiten dafür notwendig.

Fazit

Verwenden Sie immer LIKE anstatt SUBSTRING. Eine Abfrage mit dem SUBSTRING-Funktionsaufruf performiert nie besser als eine logisch äquivalente Abfrage mit dem LIKE-Operator.

Written by Milos R.

5. Mai 2011 um 21:44

Veröffentlicht in Performance

Tagged with , , , ,

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: