Compiler Kosten der Suche nach wild char '%%' in like-Klausel

Question

Ich habe eine dynamische gespeicherte Prozedur und muss die Where-Klausel in der Case-Anweisung in SQL Server 2008 dynamisch hinzufügen.

Meine Vorgehensweise ist die folgende: -

CREATE PROCEDURE SPGETDATA
@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)

AS
BEGIN
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '' THEN 
'%'+ @STRNAME + '%' ELSE '%%' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '') THEN 
'%' + @STRCODE + '%'  ELSE '%%' END** 

END

Der Benutzer kann entweder @strname oder @strcode auswählen. Aber nicht beide auf einmal.

In diesem Fall ist eine "like"-Anweisung in Ordnung, aber die Alternative ist immer eine Belastung für die Abfrage, denn sie wird immer als

@STRNAME like '%%'

oder wie unten

@STRCODE like '%%'

Nun, wenn ich diesen Ansatz verwenden, wird Compiler kosten einige Zeit zu suchen, wie '%%' auch gibt es nichts zu entsprechen oder wird es umgehen und nichts kosten? Ich habe auch den Ausführungsplan überprüft, aber er zeigt nichts für die like-Klausel an.

Daher muss ich dies in webApps verwenden, so dass die Geschwindigkeit der sp berücksichtigt werden muss. Und die Tabelle hat Millionen von Zeilen.

Der Ausführungsplan ist für beide gleich. Wenn ich wie cluase in Abfrage verwenden oder entfernen Sie es aus Abfrage es zeigt - Clustred index sacn 100%.

Bitte um Hilfe.

Answer 1

Erstens, wenn eine der Spalten nullbar ist, dann wird getestet col LIKE '%%' ist kein No-Op, sondern entspricht eigentlich dem Test, dass die Spalte col IS NOT NULL was vielleicht nicht der gewünschte Effekt ist.

Zweitens, wenn die Spalte nicht nullable ist, so dass es wirklich ein No-Op dann ist dies nicht ein besonders guter Ansatz, da SQL Server wird no die Prüfung zu optimieren. Siehe Dynamische Suchbedingungen in T-SQL für bessere Ansätze.

Wahrscheinlich wird es in Ihrem Fall aber keinen großen Unterschied machen.

Da Sie immer eine Suche mit einem führenden Platzhalter für die eine oder andere Spalte durchführen und Sie SELECT * dann ist es wahrscheinlich, dass Sie trotzdem eine vollständige Tabellensuche durchführen müssen.

Ein Fall, in dem es einen Unterschied machen könnte, wäre, wenn Sie einen engeren Index auf einer oder mehreren Spalten haben, der anstelle des Scannens des gesamten geclusterten Indexes/der Tabelle gescannt werden könnte. Selbst dann müsste SQL Server jedoch immer noch Lesezeichenabfragen durchführen, um die * so dass die Auswertung des Restprädikats für diese spezielle Abfrage recht kostengünstig ist.

Der erzeugte Plan wäre jedoch für die Anfrage mit dem anderen Parameter völlig ungeeignet, so dass dieser Versuch einer Catch-All-Abfrage zu einem Parameter-Sniffing-Problem führen könnte, wie im Folgenden gezeigt wird.

Test-Tabelle

CREATE TABLE myTable
(
id int primary key,
STRNAME VARCHAR(100) NOT NULL,
STRCODE VARCHAR(100) NOT NULL,
IsDELETED BIT NOT NULL DEFAULT 0,
Filler CHAR(7000) NULL,
)
INSERT INTO myTable(id, STRNAME, STRCODE)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)), 
       ISNULL(name,type), 
       ISNULL(name,type)
FROM master..spt_values

CREATE INDEX ix ON myTable(STRNAME)

Erster Aufruf mit dem Parameter name (Scan count 1, logical reads 7)

EXEC sp_executesql N'
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN 
''%''+ @STRNAME + ''%'' ELSE ''%%'' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN 
''%'' + @STRCODE + ''%''  ELSE ''%%'' END 
', 
N'@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)
', @STRNAME = '(rpc)', @STRCODE=''

Aufrufen mit Code-Parameter unter Wiederverwendung desselben Plans (Anzahl der Scans 1, logische Lesungen 7690)

EXEC sp_executesql N'
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN 
''%''+ @STRNAME + ''%'' ELSE ''%%'' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN 
''%'' + @STRCODE + ''%''  ELSE ''%%'' END 
', 
N'@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)
', @STRNAME = '', @STRCODE='(rpc)'

Aufruf mit Code-Parameter zur Erstellung eines spezifischen Plans (Scan count 1, logical reads 2517)

EXEC sp_executesql N'
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN 
''%''+ @STRNAME + ''%'' ELSE ''%%'' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN 
''%'' + @STRCODE + ''%''  ELSE ''%%'' END 
OPTION (RECOMPILE)
', 
N'@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)
', @STRNAME = '', @STRCODE='(rpc)'

Answer 2

Sie könnten etwas in dieser Art schreiben:

IF COALESCE(RTRIM(LTRIM(@STRNAME))), '') <> '' 
BEGIN
  SELECT  myTable.*
  FROM myTable
  WHERE IsDELETED = 0  
    AND STRNAME LIKE '%'+ @STRNAME + '%'
END
ELSE -- IF COALESCE(RTRIM(LTRIM(@STRCODE))), '') <> '' 
BEGIN
  SELECT  myTable.*
  FROM myTable
  WHERE IsDELETED = 0  
    AND STRCODE LIKE '%'+ @STRCODE + '%'
END

Wie auch immer, die Verwendung von LIKE verhindert, dass die DB Indizes verwendet. Das wird Ihre höchsten Ausführungskosten sein.

Answer 3

Ich würde zwei Versionen der Abfrage erstellen, eine mit LIKE '%%' und die andere ohne sie, und vergleichen Sie dann ihre Ausführungspläne.