Dynamische Suchbedingungen in T-SQL
By Frank Kalis
Original von Erland Sommarskog; deutsche Übersetzung von Frank Kalis
Einführung
Eine sehr weitverbreitete Anforderung an ein Informationssystem ist es, eine oder mehrere Funktionen zu haben, bei denen der Benutzer in der Lage ist, die Daten durch freie Auswahl möglichst vieler verschiedener Kriterien zu durchsuchen. Dies ist eine enorme Herausforderung, da Sie nicht nur den gewünschten Output produzieren müssen, sondern auch die Antwortzeiten innerhalb eines akzeptablen Zeitrahmens halten müssen, zumindest bei häufig verwendeten Suchen. Zu guter Letzt muss der Code leicht zu warten sein, damit neue Wünsche und Anforderungen leicht implementiert werden können.
In diesem Artikel möchte ich verschiedene Lösungsansätze für dieses Problem betrachten. Es gibt zwei Wege, zwischen denen Sie sich entscheiden müssen: Dynamisches SQL und Statisches SQL. Lassen Sie es mich gleich direkt vorwegnehmen: Dynamisches SQL ist oftmals die effektivere Lösung, sowohl im Hinblick auf Performance, Entwicklung als auch Wartbarkeit. Um aber dynamisches SQL benutzen zu können, müssen Sie den Benutzern direkte SELECT Berechtigungen auf die beteiligten Tabellen geben. Etwas, was bei weitem nicht immer möglich ist. In solchen Fällen bleibt Ihnen nur das statische SQL oder eine Hybridlösung, die auf einem View oder eine benutzerdefinierten Funktion basiert.
Zu Anfang beschreibe ich zwei Wege, die dynamisches SQL verwenden und versuche auf die Stolperfallen aufmerksam zu machen, die Sie vermeiden sollten. Anschliessend behandle ich Techniken in statischem SQL, die Ihnen eine Fülle von Methoden und Tricks zeigen, welche Sie kombinieren können, um die Suchfunktion zu implementieren. Abschliessend präsentiere ich eine Hybridlösung, die sowohl dynamisches als auch statische SQL verwendet.
Inhaltsverzeichnis:
Einführung
Die Fallstudie: Aufträge suchen
Dynamisches SQL
Einführung
sp_executesql verwenden
EXEC() verwenden
Fazit: EXEC vs. sp_executesql
Statisches SQL
Einführung
Ein einfacher, aber langsamer Ansatz
Verwendung von IF Statements
Umachandar's Trickkiste
Verwendung von Temp Tabellen
Tricks für komplexe Bedingungen
Eine Hybridlösung – Verwendung von statischem und dynamischem SQL
Schlussfolgerung
Feedback and Danksagung
Revision Geschichte
Die Fallstudie: Aufträge suchen
Über den gesamten Text hinweg werden wir daran arbeiten, eine Gespeicherte Prozedur zu implementieren, die Informationen über die Aufträge sowie die Kunden und die Produkte zu den jeweiligen Aufträge in der Northwind Datenbank abfragt. Northwind ist eine Beispieldatenbank, die mit SQL 7 und SQL 2000 ausgeliefert wird. Dies wird das Interface sein, das wir dem Benutzer zeigen (oder wahrscheinlicher einer GUI oder dem Programmierer einer Zwischenschicht):
CREATE PROCEDURE search_orders
@orderid int = NULL,
@fromdate datetime = NULL,
@todate datetime = NULL,
@minprice money = NULL,
@maxprice money = NULL,
@custid nchar(5) = NULL,
@custname nvarchar(40) = NULL,
@city nvarchar(15) = NULL,
@region nvarchar(15) = NULL,
@country nvarchar(15) = NULL,
@prodid int = NULL,
@prodname nvarchar(40) = NULL AS
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE ???
ORDER BY o.OrderID
In der SELECT Liste sehen Sie, welche Informationen der Benutzer erhält. Hier ist die Spezifikation der Parameter:
| Parameter | Funktion |
|---|---|
| @orderid | Gibt nur diesen Auftrag zurück. |
| @fromdate | Gibt nur Aufträge zurück, die am @fromdate oder später geordert wurden |
| @todate | Gibt Aufträge zurück, die am @todate oder früher geordert wurden |
| @minprice | Gibt nur Auftragsdetails zurück, die mindestens @minprice kosten |
| @maxprice | Gibt nur Auftragsdetails zurück, die maximal @maxprice kosten. |
| @custid | Gibt nur Aufträge zurück, die von diesem Kunden geordert wurden. |
| @custname | Gibt nur Aufträge zurück von Kunden, deren Namen mit @custname beginnt. |
| @city | Gibt nur Aufträge von Kunden zurück, die in dieser Stadt wohnen. |
| @region | Gibt nur Aufträge von Kunden zurück, die in dieser Region leben. |
| @country | Gibt nur Aufträge von Kunden zurück, die in diesem Land leben. |
| @prodid | Gibt nur Auftragsdetails zu diesem Produkt zurück. |
| @prodname | Gibt nur Auftragsdetails zurück von Produkten, die mit @prodname beginnen. |
Lässt ein Benutzer eine Suchbedingung aus, soll diese Bedingung nicht auf die Suche angewendet werden. Von daher soll ein einfaches EXEC search_orders alle Aufträge in der Datenbank zurückgeben.
Dieses Beispiel ist ziemlich simpel, da jede Bedingung mit einer einfachen Bedingung, die =, <=, >= oder LIKE verwendet, implementiert werden kann. In vielen Alltagssituationen werden Sie mehr Bedingungen haben, die Tabellen oder Spalten betreffen, auf die Sie zugreifen müssen. Ich habe aber ganz bewusst das Beispiel einfach gehalten, um mich hier auf das Wesentliche konzentrieren zu können. Ich hoffe, dass, wenn Sie diesen Artikel gelesen haben, Sie ein solidere Basis haben, von der aus Sie die komplexeren Bedingungen, denen Sie vielleicht begegnen, in Angriff nehmen können. So müssen Sie zum Beispiel hin und wieder mal nach einer Liste von Werten, wie @prodid = '14,56,76' suchen. Für diesen Fall möchte ich Sie auf meinen Artikel Array und Listen im SQL Server verweisen, da ich hier nicht näher auf dieses Problem eingehen werde.
Beachten Sie, dass die Order Details Tabelle 2155 Zeilen enthält. Sie werden kaum Performanceunterschiede zwischen den verschiedenen Lösungen beobachten, da selbst schlechte Ausführungspläne die Daten innerhalb von 1 bis 2 Sekunden zurückgeben. Aber in vielen Alltagssituationen existieren Millionen von Datensätzen, die durchsucht werden müssen. Hier wird die Vermeidung schlechter Pläne essentiell.
In diesem Text diskutiere ich sieben verschiedene Implementierungen von search_orders, unspektakulär search_orders_1 bis search_orders_8 benannt. Einige davon sind vollständig wiedergegeben, andere habe ich gekürzt. Aber alle sind verfügbar im dynsearch Verzeichnis auf meiner Website.
Oftmals ist es bei solchen Prozeduren der Fall, dass der Benutzer in der Lage sein soll, anzugeben, wie der Output sortiert sein soll. Da dieser Artikel dafür aber nicht lang genug ist, möchte ich dazu nicht viele Worte verlieren. Wenn Sie eine dynamische SQL Variante verwenden, sind Sie wahrscheinlich am besten damit bedient, die ORDER BY Klausel ebenfalls dynamisch zu erstellen. Verwenden Sie hingegen statisches SQL, lesen Sie bitte folgenden Abschnitt aus meinem allgemeinen Artikel über dynamisches SQL.
Dynamisches SQL
Einführung
Performance
Vielleicht hat man Ihnen geraten: Verwenden Sie kein dynamisches SQL, es ist langsam! Stimmt, an der falschen Stelle verwendet, kann dynamisches SQL tatsächlich Ihre Performance beeinträchtigen, teilweise sogar erheblich. Führen Sie eine Prozedur aus, die nur statisches SQL verwendet, ist SQL Server in der Lage, den gecachten Ausführungsplan wiederzuverwenden. Falls die Prozedur aber dynamisches SQL enthält, ist dieser Teil des Codes nicht im Ausführungsplan enthalten und technisch gesehen damit auch nicht Teil der Gespeicherten Prozedur. Für dynamisches SQL kann es sein, dass SQL Server den Ausführungsplan jedesmal neu erstellen muss. Aber für eine dynamische Suche, wie unsere search_orders gibt es sowieso keinen einzelnen Plan, der jede mögliche Kombination der Inputparameter abdeckt. Ein Plan gibt vielleicht die Daten in weniger als einer Sekunde zurück, wenn der Benutzer die order id angibt, mag aber vielleicht zehn Minuten brauchen, wenn der Benutzer die Stadt angibt oder umgekehrt. In diesem Fall ist die eine oder zwei Sekunden, die zur Erstellung des Ausführungsplans benötigt werden ein nicht zu hoher Preis. Und, wie wir sehen werden, erhalten wir trotzdem einen gecachten Abfrageplan, auch für dynamisches SQL. Dieser Plan enthält sogar eine Kombination der Inputparameter, die der Benutzer tatsächlich angibt. Aus Performancegesichtspunkten ist dynamisches SQL sogar oftmals die überlegene Lösung für diese Art von Problemen.
Sicherheit
Nichtsdestoweniger müssen Sie sich über einen wichtigen Sicherheitsaspekt im Klaren sein. Bedenken Sie, dass Benutzer bei Verwendung einer Gespeicherten Prozedur keine direkten Zugriffsrechte auf die Tabellen und Views haben müssen, die in der Prozedur referenziert werden; Sie brauchen nur Ausführungsrechte auf die Prozedur selber. Wenn eine Gespeicherte Prozedur ausgeführt wird, kommen die Rechte des Besitzers der Prozedur zur Anwendung. Da hingegen dynamisches SQL nicht Teil der gespeicherten Prozedur ist, werden die Rechte des aktuellen Benutzers angewendet, wenn der dynamische Teil ausgeführt wird. Um also dynamisches SQL einsetzen zu können, müssen Sie Ihren Benutzern SELECT Rechte direkt auf den Tabellen einräumen. Ist dies nicht machbar in Ihrem System, können Sie kein dynamisches SQL verwenden.
...nun, dies ist nicht 100% richtig. Manchmal können Sie einen View oder eine benutzerdefinierte Funktion als Kern verwenden und dann Ihr dynamisches SQL drum herum bauen.
Weitere Literatur
Es gäbe noch viele Dinge über dynamisches SQL zu sagen, die ich aber hier auslassen will. Vielmehr möchte ich auf meinen Webartikel Dynamisches SQL: Fluch und Segen verweisen, der die Verwendung von dynamischem SQL im allgemeinen behandelt. In diesem Artikel behandle ich auch die Performance- und Sicherheitsaspekte im Detail und diskutiere mögliche Wege, um das SELECT Zugriffsproblem zu umgehen.
Eine Anmerkung zum Testverfahren
Aufgrund der Art und Weise, auf die Sie den Code generieren, ist es sehr wichtig, dass Sie alle Inputparameter testen, nach Möglichkeit sogar in Kombination miteinander. Sind Sie hier nicht umsichtig, kann es sein, dass Ihre Benutzer Syntaxfehler in Ihrem Code entdecken.
Ausserdem sollten Sie auch testen, ob Sie die Performance erhalten, welche Sie erwarten. Dafür sollte das Datenvolumen etwa dem entsprechend, welches Sie in Produktion erwarten.
Die Befehle
Es gibt zwei Arten, um dynamisches SQL auszuführen. EXEC() und sp_executesql. Das Letztere ist meiner Meinung nach vorzuziehen, aber teilweise ist dies auch eine Frage des Geschmacks und EXEC() wird auch vielfach verwendet. Ich zeige Ihnen Implementierungen von search_orders unter Verwendung beider Methoden. Die Beispiele sollen nicht nur die Verwendung der Befehle demonstrieren, sondern auch als Beispiel für guten Kodierstil dienen. T-SQL Code, der wiederum T-SQL generiert, wird ziemlich leicht verwirrend und schwer nachvollziehbar. Aus diesem Grund sollten Sie Kodierdisziplin an den Tag legen.
EXEC() ist seit SQL Server 6.0 verfügbar. sp_executesql wurde in SQL 7 hinzugefügt und ist daher nicht verfügbar in SQL 6.5
Verwendung von sp_executesql
sp_executesql ist eine Systemprozedur, die ein SQL Statement als ersten Parameter akzeptiert und eine Deklaration von Parametern als zweiten Parameter. Die verbleibenden Parameter sind durch diese Parameterliste definiert. Hier ist die Prozedur search_orders_1, unter Verwendung von sp_executesql:
CREATE PROCEDURE search_orders_1 -- 1
@orderid int = NULL, -- 2
@fromdate datetime = NULL, -- 3
@todate datetime = NULL, -- 4
@minprice money = NULL, -- 5
@maxprice money = NULL, -- 6
@custid nchar(5) = NULL, -- 7
@custname nvarchar(40) = NULL, -- 8
@city nvarchar(15) = NULL, -- 9
@region nvarchar(15) = NULL, -- 10
@country nvarchar(15) = NULL, -- 11
@prodid int = NULL, -- 12
@prodname nvarchar(40) = NULL, -- 13
@debug bit = 0 AS -- 14
-- 15
DECLARE @sql nvarchar(4000), -- 16
@paramlist nvarchar(4000) -- 17
-- 18
SELECT @sql = -- 19
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, -- 20
c.CustomerID, c.CompanyName, c.Address, c.City, -- 21
c.Region, c.PostalCode, c.Country, c.Phone, -- 22
p.ProductID, p.ProductName, p.UnitsInStock, -- 23
p.UnitsOnOrder -- 24
FROM dbo.Orders o -- 25
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID -- 26
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID -- 27
JOIN dbo.Products p ON p.ProductID = od.ProductID -- 28
WHERE 1 = 1' -- 29
-- 30
IF @orderid IS NOT NULL -- 31
SELECT @sql = @sql + ' AND o.OrderID = @xorderid' + -- 32
' AND od.OrderID = @xorderid' -- 33
-- 34
IF @fromdate IS NOT NULL -- 35
SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate' -- 36
-- 37
IF @todate IS NOT NULL -- 38
SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate' -- 39
-- 40
IF @minprice IS NOT NULL -- 41
SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice' -- 42
-- 43
IF @maxprice IS NOT NULL -- 44
SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice' -- 45
-- 46
IF @custid IS NOT NULL -- 47
SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' + -- 48
' AND c.CustomerID = @xcustid' -- 49
-- 50
IF @custname IS NOT NULL -- 51
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' -- 52
-- 53
IF @city IS NOT NULL -- 54
SELECT @sql = @sql + ' AND c.City = @xcity' -- 55
-- 56
IF @region IS NOT NULL -- 57
SELECT @sql = @sql + ' AND c.Region = @xregion' -- 58
-- 59
IF @country IS NOT NULL -- 60
SELECT @sql = @sql + ' AND c.Country = @xcountry' -- 61
-- 62
IF @prodid IS NOT NULL -- 63
SELECT @sql = @sql + ' AND od.ProductID = @xprodid' + -- 64
' AND p.ProductID = @xprodid' -- 65
-- 66
IF @prodname IS NOT NULL -- 67
SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' -- 68
-- 69
SELECT @sql = @sql + ' ORDER BY o.OrderID' -- 70
-- 71
IF @debug = 1 -- 72
PRINT @sql -- 73
-- 74
SELECT @paramlist = '@xorderid int, -- 75
@xfromdate datetime, -- 76
@xtodate datetime, -- 77
@xminprice money, -- 78
@xmaxprice money, -- 79
@xcustid nchar(5), -- 80
@xcustname nvarchar(40), -- 81
@xcity nvarchar(15), -- 82
@xregion nvarchar(15), -- 83
@xcountry nvarchar(15), -- 84
@xprodid int, -- 85
@xprodname nvarchar(40)' -- 86
-- 87
EXEC sp_executesql @sql, @paramlist, -- 88
@orderid, @fromdate, @todate, @minprice, -- 89
@maxprice, @custid, @custname, @city, @region, -- 90
@country, @prodid, @prodname -- 91
Für den Fall, dass Sie dies in einem schmalen Browser Fenster lesen: Ich habe Zeilennummern an der rechten Seite hinzugefügt, auf die ich mich im Folgenden beziehen werde.
Genereller Ablauf
In den Zeilen 19 - 28 erstelle ich den Basis SQL String. Die Bedingung
WHERE 1 = 1in Zeile 28 ermöglichst es dem Benutzer, die Prozedur aufzurufen, ohne auch nur einen Parameter anzugeben.
Anschliessend checke ich auf den Zeilen 30 - 67 alle Parameter (ausser @debug). Ist dieser nicht NULL, füge ich eine Bedingung für die entsprechende Spalte zum SQL String hinzu. Abschliessend auf Zeile 69, kommt die ORDER BY Klausel hinzu.
In Zeile 70 überprüfe ich den @debug Parameter. Enthält dieser den Wert 1, PRINTe ich den String. Dies ist nützlich, falls das dynamische SQL zu einem Kompilierungsfehler führt, den ich nicht sofort verstehe, der aber vielleicht offensichtlich wird, sobald ich den SQL Code sehe. Ein typischer Fehler zum Beispiel ist, ein Leerzeichen zu vergessen, so dass sich der SQL Code folgendermassen liest:
WHERE 1 = 1 AND o.OrderDate <= @xtodateAND p.ProductName LIKE @xprodname
Auf den Zeilen 74 - 85 deklariere ich die Parameterliste für mein dynamisches SQL Statement und auf den Zeilen 87 - 90 führe ich ihn schliesslich aus.
Weitere Details zu sp_executesql
sp_executesql ist eine Systemprozedur mit einer sehr speziellen Parameterliste. Der erste Parameter ist ein parametrisiertes SQL Statement. Der zweite Parameter ist die Deklaration einer Parameterliste, ähnlich der Parameterliste einer Gespeicherten Prozedur. Die restlichen Parameter sind einfach die Parameter aus der Parameterliste.
Die parametrisierte Abfrage wird im Cache platziert. Falls ein zweiter Benutzer dann die Suche unter Verwendung identischer Input Parameter (zum Beispiel @city und @prodname) auf denselben Kriterien ausführt, wird der Abfrageplan wiederverwendet, auch wenn er andere Werte in seiner Suche verwendet.
Der Parameterlisten-Parameter den ich an sp_executesql übergebe ist im Grunde identisch mit der Parameterliste der Prozedur selber. Hier habe ich die Parameter @xorderid usw benannt. Technisch gesehen gibt es keinen Grund dafür, und normalerweise würde ich auch die gleichen Namen verwenden wie in der Parameterliste der Gespeicherten Prozedur, aber ich wollte Sie darauf aufmerksam machen, dass die Parameter innerhalb des dynamischen SQL in keiner Beziehung zu den Parametern und Variablen der umgebenden Gespeicherten Prozedur stehen. Das dynamische SQL erstellt seinen eigenen Gültigkeitsbereich. Stellen Sie sich das so vor, als ob Sie eine Gespeicherte Prozedur on-the-fly erstellen.
Sie können auch OUTPUT Parameter mit sp_executesql verwenden. Ich zeige Ihnen hier dafür keine Beispiele, sondern möchte Sie auf KB Artikel 262499 verweisen (Der nicht in der onlinehilfe ist). OUTPUT Parameter sind nützlich, wenn Sie Daten zurückgeben möchten, zum Beispiel von einem SELECT COUNT(*).
Das SQL Statement und die Parameterliste haben den ntext Datentyp, falls Sie diese also als Literal übergeben, müssen Sie N (für National) als Kennzeichnung für ein Unicode Literal verwenden:
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @orderid',
N'@orderid int', @orderid = 10872
Doppelt gemoppelt
Vielleicht verwundert es Sie, warum es jeweils zwei Bedingungen zu order id, customer id und product id (Zeilen 31-32, 47-48, 63-64) gibt. Der Grund ist, dass ich einmal gelernt habe, dass man stets alle Bedingungen, die man kennt angeben soll, um den Optimierer zu unterstützen.
Andere Tabellen auswählen
Nehmen wir einmal an, es gäbe eine Tabelle Historicorders in der Northwind Datenbank, in der Aufträge gespeichert werden, die ausgeliefert, berechnet und bezahlt wurden, und das die Benutzer in der Lage sein sollen, auszuwählen, ob sie entweder mit den aktuellen Aufträgen oder den historischen arbeiten wollen. Aus Vereinfachungsgründen ignoriere ich Order Details hier. Wie würden Sie das lösen?
In Newsgroups sieht man oft, dass Leute versuchen, dies inkorrekterweise mit
SELECT * FROM @tablenamezu lösen. Bis SQL 7 war dies nichts weiter als ein Syntaxfehler. Im SQL Server 2000 ist dies eine gültige Syntax, Sie müssen @tablename allerdings als table Variable definiert haben, welche eine Art von temp Tabelle darstellt, nicht etwas eine String.
Tatsächlich sollten Sie Tabellenname nicht als Parameter an eine Gespeicherte Prozedur übergeben. Haben Sie einmal angefangen, Gespeicherte Prozeduren zu verwenden, sollten alle Verweise auf Tabellen- und Spaltennamen in der Prozedur selber sein. Ich würde einen weiteren Parameter zu search_orders hinzufügen:
@historic_data bit DEFAULT = 0
so dass Zeile 24 folgendermassen aussähe:
FROM ' + CASE @historic_data
WHEN 0 THEN 'Orders'
WHEN 1 THEN 'Historicorders'
END + ' o
EXEC() verwenden
Im Gegensatz zu sp_executesql akzeptiert EXEC() nur einen String als Inputparameter, so dass Sie die Abfrage nicht parametrisieren können. Stattdessen müssen Sie den Wert in den SQL String einsetzen. Auf den ersten Blick mag Ihnen dies vielleicht einfacher erscheinen, wie wir aber sehen werden, ist es tatsächlich wesentlich komplizierter. Hier ist die Prozedur search_orders_2:
CREATE PROCEDURE search_orders_2 -- 1
@orderid int = NULL, -- 2
@fromdate datetime = NULL, -- 3
@todate datetime = NULL, -- 4
@minprice money = NULL, -- 5
@maxprice money = NULL, -- 6
@custid nchar(5) = NULL, -- 7
@custname nvarchar(40) = NULL, -- 8
@city nvarchar(15) = NULL, -- 9
@region nvarchar(15) = NULL, -- 10
@country nvarchar(15) = NULL, -- 11
@prodid int = NULL, -- 12
@prodname nvarchar(40) = NULL, -- 13
@debug bit = 0 AS -- 14
-- 15
DECLARE @sql1 nvarchar(4000), -- 16
@sql2 nvarchar(4000), -- 17
@fromdatestr char(23), -- 18
@todatestr char(23), -- 19
@minpricestr varchar(25), -- 20
@maxpricestr varchar(25) -- 21
-- 22
SELECT @fromdatestr = convert(char(23), @fromdate, 126), -- 23
@todatestr = convert(char(23), @todate, 126), -- 24
@minpricestr = convert(varchar(25), @minprice), -- 25
@maxpricestr = convert(varchar(25), @maxprice) -- 26
-- 27
SELECT @sql1 = -- 28
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, -- 29
c.CustomerID, c.CompanyName, c.Address, c.City, -- 30
c.Region, c.PostalCode, c.Country, c.Phone, -- 31
p.ProductID, p.ProductName, p.UnitsInStock, -- 32
p.UnitsOnOrder -- 33
FROM dbo.Orders o -- 34
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID -- 35
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID -- 36
JOIN dbo.Products p ON p.ProductID = od.ProductID -- 37
WHERE 1 = 1' -- 38
-- 39
IF @orderid IS NOT NULL -- 40
SELECT @sql1 = @sql1 + ' AND o.OrderID = ' + str(@orderid) + -- 41
' AND od.OrderID = ' + str(@orderid) -- 42
-- 43
IF @fromdate IS NOT NULL -- 44
SELECT @sql1 = @sql1 + ' AND o.OrderDate >= ' + -- 45
quotename(@fromdatestr, '''') -- 46
-- 47
IF @todate IS NOT NULL -- 48
SELECT @sql1 = @sql1 + ' AND o.OrderDate <= ' + -- 49
quotename(@todatestr, '''') -- 50
-- 51
IF @minprice IS NOT NULL -- 52
SELECT @sql1 = @sql1 + ' AND od.UnitPrice >= ' + @minpricestr -- 53
-- 54
IF @maxprice IS NOT NULL -- 55
SELECT @sql1 = @sql1 + ' AND od.UnitPrice <= ' + @maxpricestr -- 56
-- 57
SELECT @sql2 = '' -- 58
-- 59
IF @custid IS NOT NULL -- 60
SELECT @sql2 = @sql2 + ' AND o.CustomerID = ' + -- 61
quotename(@custid, '''') + -- 62
' AND c.CustomerID = ' + -- 63
quotename(@custid, '''') -- 64
-- 65
IF @custname IS NOT NULL -- 66
SELECT @sql2 = @sql2 + ' AND c.CompanyName LIKE ' + -- 67
quotename(@custname + '%', '''') -- 68
-- 69
IF @city IS NOT NULL -- 70
SELECT @sql2 = @sql2 + ' AND c.City = ' + -- 71
quotename(@city, '''') -- 72
-- 73
IF @region IS NOT NULL -- 74
SELECT @sql2 = @sql2 + ' AND c.Region = ' + -- 75
quotename(@region, '''') -- 76
-- 77
IF @country IS NOT NULL -- 78
SELECT @sql2 = @sql2 + ' AND c.Country = ' + -- 79
quotename(@country, '''') -- 80
-- 81
IF @prodid IS NOT NULL -- 82
SELECT @sql2 = @sql2 + ' AND od.ProductID = ' + str(@prodid) + -- 83
' AND p.ProductID = ' + str(@prodid) -- 84
-- 85
IF @prodname IS NOT NULL -- 86
SELECT @sql2 = @sql2 + ' AND p.ProductName LIKE ' + -- 87
quotename(@prodname + '%', '''') -- 88
-- 89
IF @debug = 1 -- 90
PRINT @sql1 + @sql2 -- 91
-- 92
EXEC(@sql1 + @sql2 + ' ORDER BY o.OrderID') -- 93
Allgemeine Anmerkungen
Beim Erstellen einer nicht-parametrisierten Abfrage mit eingesetzten Werten, müssen Sie diszipliniert sein, wenn Sie Ihren Code schreiben. Sehr leicht kann man sich im Gewirr von verschachtelten Anführungszeichen verlieren. In Newsgroups sieht man oft, dass Leuten Code posten der wie folgt aussieht:
EXEC('SELECT col1, col2, ...
FROM ...
WHERE ' + CASE @par1 IS NULL THEN ' + col = ''' + @par + ''' + ...)
So etwas ist schwer zu lesen und zu warten, und falls etwas schief geht, haben Sie keine Idee davon, wie das SQL das Sie generiert haben tatsächlich aussieht. search_orders2 aggregiert den SQL Code in zwei Variablen. Ferner existiert wieder der @debug Parameter, so dass ich mir den Code ansehen kann, wenn ich verifizieren muss, dass ich ihn korrekt erzeuge. Und durch die Verwendung von quotename() habe ich versucht, den Einsatz von verschachtelten Anführungszeichen auf ein Minimum zu reduzieren (nähere Informationen zu dieser Funktion finden Sie weiter unten).
Berücksichtigen Sie, dass EXEC() einen neuen Gültigkeitsbereich aufmacht. Das SQL, das Sie generiert haben, kann nicht auf Variablen zugreifen, die in der umgebenden Prozedur deklariert wurden. Generiert das dynamische SQL eine temporäre Tabelle, verschwindet dies, wenn der Gültigkeitsbereich aufhört zu existieren.
Verwendung von quotename()
Wie Sie sehen können, mache ich ab Zeile 45 häufigen Gebrauch der eingebauten Funktion quotename(), auf Anregung von SQL Server MVP Steve Kass. quotename() akzeptiert zwei Parameter: einen String und ein Delimiter Zeichen. Ist der Delimiter (, [, < oder {, wird automatisch die jeweils korrespondierende Klammer als abschliessender Delimiter angenommen. Der Rückgabewert ist der String eingeschlossen in den Delimiter. Jeder abschliessender Delimiter innerhalb des Strings wird verdoppelt. Standard Delimiter sind die eckigen Klammern. Beispiel:
SELECT quotename('Order Details') -- => [Order Details]
SELECT quotename('Two o' + char(39) + 'clock', '''') -- => 'Two o''clock'
SELECT quotename('abc{}def', '{') -- => {abc{}}def}
Von diesen Beispielen ist in diesem Zusammenhang besonders das zweite interessant. In T-SQL verwenden Sie einfache Anführungszeichen, um einen String zu begrenzen. Ist die Einstellung QUOTED_IDENTIFIER OFF geschaltet, können auch doppelte Anführungszeichen verwendet werden, aber die Einstellung ist typischerweise on in SQL Server 2000 und einige Features verlangen diese on Einstellung sogar. Am besten Sie verwenden ausschliesslich einfache Anführungszeichen. Müssen Sie also den String Delimiter in einem Literal verwenden, müssen Sie diesen verdoppeln, so dass die vier einfachen Anführungszeichen hintereinander einen Einzeichen Charakter Literal repräsentieren, der den Wert eines einzelnen Anführungszeichens ( ' ) hat. Alternativ hierzu können Sie den Wert als char(39) angeben, so wie ich dies gemacht habe im Two o'clock Beispiel weiter oben.
Warum machen wir dies alles? Aus drei wichtigen Gründen:
- Es macht das Schreiben von Code viel leichter. Verwenden Sie quotename() nicht, müssen Sie verschachtelte Anführungszeichen verwenden, und Ihr Code wird unübersichtlich. Schauen Sie sich die Beispiele weiter unten im SQL 6.5 Abschnitt dazu an.
- Dem Benutzer wird dadurch erlaubt, Werte wie Let's Stop N Shop als Unternehmensnamen einzugeben ohne eine Syntaxfehler hervorzurufen (tatsächlich ist dies ein Kunde in der Northwind Datenbank).
- Sie erhöhen ausserdem Ihren Schutz gegen etwas, das als SQL injection bekannt ist. Dies ist eine Technik, bei der ein böswilliger Eindringling einen String Delimiter angibt und anschliessend ein SQL Kommando an einer Stelle, an der Sie als gutgläubiger Entwickler nur Daten erwarten und dadurch erreicht, dass SQL Server sein Statement ausführt. Ausführlicher diskutiere ich SQL Injection in meinem Artikel Dynamisches SQL: Fluch und Segen.
Anmerkung: Der Inputparameter für quotename() ist nvarchar(128), falls Sie in der Lage sein müssen, längere Inputparameter zu verarbeiten, können Sie quotename() nicht verwenden. Trotzdem müssen Sie auch in der Lage sein, einfache Anführungszeichen in einem String zu handhaben. Sie können die leicht durch die replace() Funktion erreichen . Ausserdem sind 128 Zeichen für einen Suchstring relativ lang, so dass Sie es vielleicht vorziehen, den User auf diese Anzahl zu beschränken. Darüber hinaus schauen Sie sich einmal die Funktion quotestring() an.
Quotename() und SQL 6.5
Sowohl die quotename() Funktion als auch replace() wurden in SQL 7 eingeführt. Falls Sie also noch mit SQL 6.5 arbeiten, haben Sie keine andere Möglichkeit als:
SELECT @sql2 = @sql2 + ' AND c.CompanyName LIKE ''' + @custname + '%'''
Wie gesagt, es ist wirklich leicht, sich im Gewirr von Anführungszeichzen zu verlieren. gibt zudem noch der Benutzer einen Wert ein, der ein einfaches Anführungszeichen beinhaltet, erhält er eine Syntaxfehler. In SQL 6.5 ist die Einstellung QUOTED_IDENTIFIER typischerweise OFF, so dass Sie auch doppelte Anführungszeichen verwenden können, um einen Literal zu umschliessen:
SELECT @sql2 = @sql2 + " AND c.CompanyName LIKE '" + @custname + "%'"
Da es weniger wahrscheinlich ist, dass doppelte Anführungszeichen in den Daten enthalten sind, sind die Benutzer auf diese Art und Weise relativ sicher. Vor SQL Injektion sind Sie dennoch nicht sicher.
Leider bietet SQL 6.5 keinen wirklich guten Weg an, um sämtliche String Delimiter zu ersetzen. Es ist natürlich möglich, aber der Umfang an Code, der dazu notwendig ist, steht in keinem Verhältnis. Aus diesem Grund, ist es das Beste, dies bereits im Client Code abzufangen.
Datetime Parameter
Sämtliche Nicht-String Parameter müssen in String Literal konvertiert werden. In den Zeilen 23-24 kümmere ich mich um den datetime Parameter. Den verwendeten Ausdruck:
convert(char(23), @fromdate, 126)
habe ich nicht zufällig gewählt. Das Ausgabeformat sieht folgendermassen aus: 2003-04-06T21:14:26.627, und entspricht exakt dem Format das der ISO 8601 Standard verlangt und welches gebräuchlich im XML ist. Noch wichtiger aber ist, dass dies eins von zwei Formaten für Datetime Literalen in SQL Server ist, bei dem die Interpretation nicht von den Einstellungen für Datumsformat oder Sprache abhängig ist. Falls Ihnen nicht ganz klar ist, wovon ich spreche, probieren Sie einmal folgende Statements aus:
SET DATEFORMAT mdy
SELECT convert(datetime, '02/07/09')
SET DATEFORMAT dmy
SELECT convert(datetime, '02/07/09')
go
SELECT convert(datetime, '2002-12-19') -- Fehler!
go
SET LANGUAGE Swedish
SELECT convert(datetime, 'Oct 12 2003') -- Fehler! "Okt" in Swedish.
Abgesehen vom SET Kommando kann die Spracheinstellung (die auch die Datumseinstellung kontrolliert) auch auf Userlevel mit der Systemprozedur sp_defaultlanguage gesetzt werden.
Der Stilparameter 126 sowie die Formatierung mit dem T zwischen Datum und Zeit, steht nur in SQL Server 2000 zur Verfügung. In SQL 7 und SQL 6.5 sollten Sie stattdessen 112 als Stilparameter verwenden, der das Format 20030406 zurückgibt. Dies ist das andere Format, dass von Datumsformat und Spracheinstellungen unabhängig ist. Nachteil bei Verwendung von 112 ist, dass Sie keine Stunden und Minuten erhalten. Für die Prozedur search_orders_2 ist das kein Problem, da die Benutzer sowieso nur das Datum vorgeben; müssen Sie aber auch den Zeitanteil in SQL 7 und SQL 6.5 berücksichtigen, müssen Sie verschiedene Stilparameter miteinander kombinieren
SELECT convert(char(8), getdate(), 112) + ' ' +
convert(char(8), getdate(), 108)
Für weitere Informationen über die verschiedenen Stilparameter, die Sie mit convert() verwenden können, schauen Sie unter CAST and CONVERT in der T-SQL Referenz in BOL.
Numerische Parameter
In den Zeilen 25-26 behandle ich die beiden money Parameter und auf den Zeilen 40-41 und 82-83 kümmere ich mich um die int Parameter @orderid und @prodid. Wie Sie sehen, verwende ich unterschiedliche Strategien. Natürlich könnte ich convert() für beide benutzen. Es ist einfach nur so, dass ich str() für geeigneter für den Umgang mit Integer Werten halte. str() funktioniert auch mit money, aber ohne Angabe weiterer Parameter wird der Wert auf die nächste Ganzahl gerundet, während convert() per Standard 2 Nachkommastellen zurückgibt. Dies ist der Grund, weswegen ich convert() für die @min/maxprice Parameter bevorzuge.
Ich möchte die float und decimal Datentypen hier nicht behandeln. Schauen Sie in die onlinehilfe zu CAST and CONVERT und STR.
@sql1 und @sql2
Wie Sie sehen, verwende ich zwei Variablen, um meinen String zusammenzusetzen @sql1 und @sql2. Es gibt zwar in diesem Beispiel keinen wirklichen Grund hierfür; falls Sie aber eine umfangreiche Abfrage mit zahlreichen Parametern haben, besteht ein Risiko, dass Ihr SQL String das Maximum von 4.000 Zeichen für einen nvarchar Wert (oder 8.000 für einen varchar) überschreitet. Aus diesem Grund verwende ich zwei Variablen, um die Technik im Umgang hiermit zu demonstrieren. Wie Sie sehen, erlaubt EXEC() es Ihnen, einen Ausdruck als Parameter zu übergeben. Beachten Sie, dass dies kein willkürlicher Ausdruck ist, vielmehr ist der einzig zulässige Operator die Verknüpfung ist.
In SQL 6.5 erlaubt der varchar Datentyp nicht mehr als 255 Zeichen, unter Umständen benötigen Sie eine ganze Menge von @sql Variablen
varchar und nvarchar
Die Northwind Datenbank verwendet ausschliesslich den nvarchar Datentypen.Die Daten in Northwind scheinen aber auf Zeichen in Windows Latin-1 beschränkt zu sein. Dadurch werden Sprachen wie Englisch, Spanisch, Französisch und Deutsch abgedeckt. Haben Sie also eine Systemcollation, die auf diesem Zeichensatz basiert, werden Sie niemals inkorrekte Ergebnisse aus search_orders_2 erhalten. Führt hingegen ein Russe folgendes aus:
EXEC search_orders_2 @city = N'Bräcke'
wird er nicht die Aufträge von Folk och Fä HB in Bräcke erhalten, da Bräcke bei der Konvertierung als varchar zu Bracke wird. So sollte meine Prozedur eigentlich aussehen (Zeilen 70-71):
SELECT @sql2 = @sql2 + N' AND c.City = N' +
quotename(@city, '''')
Das N, mit dem ein nvarchar Literal eingeleitet wird, sollte bei sämtlichen nvarchar Parametern erscheinen (Das N steht für National und stammt aus dem ANSI Standard SQL-92.)
Warum nun zeige ich Ihnen nicht den korrekten Code? Nun, ich habe herausgefunden, dass viele Leser den Code als Template für ihren eigenen Code verwenden und ich möchte Ihnen nicht quasi vorschreiben, das N standardmässig einzusetzen. Gebrauchen Sie ein nvarchar Literal zusammen mit einer indizierten varchar Spalte, wird die varchar Spalte in nvarchar konvertiert und dies schliesst den Einsatz des Index aus. Obwohl Sie die korrekten Ergebnisse erhalten werden, wird die Performance schlecht sein.
Caching
Auch mit EXEC() werden Ihre Abfragen gecached, wenn also zwei Benutzer nach einem Kunden suchen, kann der zweite Benutzer den gecacheden Abfrageplan wiederverwenden. Unter Umständen kann er ihn sogar verwenden, wenn er einen anderen Kunden abfragt, da SQL Server in der Lage ist, Abfrage zu autoparametrisieren. Sie werden verstehen, dass dies aber nicht so vorhersehbar ist wie bei sp_executesql.
Beachten Sie, dass in SQL 6.5 nur Abfragepläne für Gespeicherte Prozeduren gecached werden können. EXEC() führt jedesmal zu einer Rekompilierung der Abfrage.
Zusammenfassung: EXEC vs. sp_executesql
Wenn Sie beide Beispiel genau analysiert haben, werden Sie mir zustimmen, dass sp_executesql folgende Vorteil hat:
- Minimierter Bedarf verschachtelte Anführungszeichen einzusetzen.
- Minimiertes Risiko für SQL Injektion.
- Sie müssen sich keine Gedanken über die Formatierung von datetime und nummerischen Parametern machen.
- Sie müssen nicht nachdenken, ob Sie das N vor einem Stringliteral verwenden müssen oder nicht. (Obwohl Sie natürlich in der Parameterliste von sp_executesql die richtigen Angaben zu den Datentypen machen müssen.)
- Abfragepläne für die gleichen Inputparameter können aus dem Cache wiederverwendet werden.
- Da Werte nicht im generierten SQL einbezogen sind, ist das Risiko die maximalen 4.000 Zeichen zu überschreiten, geringer.
Fairerweise muss aber allerdings auch folgende Vorteile von EXEC() erwähnen:
- Während Sie EXEC(@sql1 + @sql2) schreiben können, können Sie hingegen nicht EXEC sp_executesql @sql1 + @sql2 schreiben. Überschreiten Sie also 4.000 Zeichen, müssen Sie EXEC() verwenden. (Sie können allerdings eingebettet sp_executesql aufrufen, wie dieses Beispiel zeigt.)
- Zu guter Letzt nimmt EXEC() einen varchar Wert entgegen, so dass Sie 8.000 Zeichen in einem String verwenden können. Vorausgesetzt Sie arbeiten nicht mit Unicode Daten.
Static SQL verwenden
Einführung
Sind Sie gezwungen, statisches SQL zu verwenden, müssen Sie sich entscheiden zwischen Performance und Wartbarkeit. Um stets die beste Performance zu erhalten, müssen Sie IF Statements verwenden, so können Sie nur die Bedingungen einbauen, die Sie auch brauchen:
IF @orderid IS NOT NULL
SELECT ...
WHERE o.orderd IS NOT NULL
ELSE IF @fromdate IS NOT NULL AND @todate IS NULL AND @custid IS NULL ...
SELECT ...
WHERE o.OrderDate <= @fromdate
ELSE IF @fromdate IS NOT NULL AND @todate IS NOT NULL AND @custid IS NULL ...
Aber dieser Code wäre unmöglich wartbar (und sehr langweilig zu schreiben). In den folgenden Abschnitten, beleuchte ich verschiedene Wege dieses Problem in Angriff zu nehmen. Ich sollte betonen, dass die einzelnen Methoden sich nicht gegenseitig ausschliessen und dass Sie vielleicht herausfinden, das Sie das beste Ergebnis durch Kombination zweier oder mehrerer dieser Methoden erreichen.
Auch hier gilt das, was bereits für dynamisches SQL gesagt wurde: Testen Sie stets alle Input Parameter, sowohl auf korrektes Ergebnis wie auch auf Performance. Um die Performance zu testen, benötigen Sie Tabellen die grössenmässig vergelichbar sind mit denen, die Sie in Produktion erwarten können.
Ein einfacher, aber langsamer Ansatz
Hier ist search_orders_3 die sehr kurz und kompakt ist, aber keine gute Performance bei grossen Datenmengen bietet:
CREATE PROCEDURE search_orders_3
@orderid int = NULL,
@fromdate datetime = NULL,
@todate datetime = NULL,
@minprice money = NULL,
@maxprice money = NULL,
@custid nchar(5) = NULL,
@custname nvarchar(40) = NULL,
@city nvarchar(15) = NULL,
@region nvarchar(15) = NULL,
@country nvarchar(15) = NULL,
@prodid int = NULL,
@prodname nvarchar(40) = NULL AS
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE (o.OrderID = @orderid OR @orderid IS NULL)
AND (o.OrderDate >= @fromdate OR @fromdate IS NULL)
AND (o.OrderDate <= @todate OR @todate IS NULL)
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
AND (o.CustomerID = @custid OR @custid IS NULL)
AND (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
AND (c.City = @city OR @city IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @country OR @country IS NULL)
AND (od.ProductID = @prodid OR @prodid IS NULL)
AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY o.OrderID
Der Effekt aller @x IS NULL Klauseln ist, dass falls der Input Parameter NULL ist, ist die AND Bedingung immer wahr.
Es ist sehr unwahrscheinlich, dass diese Prozedur jemals einen Index verwenden wird. Das mag vielleicht denjenigen Leser überraschen, der weiss, dass SQL Server bei Erstellung des Abfrageplans für eine Gespeicherte Prozedur, auf die Werte für die Input Parameter achtet. Für folgenden Aufruf:
EXEC search_orders_3 @orderid = 10654
würde Sie vielleicht erwarten, dass der Optimierer den Primarschlüssel Index der Orders und Order Details Tabellen verwendet. Aber der Optimierer nimmt keine Ablaufanalyse der Prozedur vor. Deshalb kann er nicht verifizieren, dass @orderid nicht auf NULL gesetzt wurde, wenn bei Ausführung die SELECT Statements erreicht werden. Der Optimierer kann annehmen, dass @orderid sich nicht ändert, falls die einzige Auswirkung dieser inkorrekten Annahme eine suboptimaler Plan ist. Aber er kann natürlich keinen Plan erstellen, der inkorrekte Ergebnisse liefern würde.
So, wenn diese Methode stets zu einem Table Scan führt, ist sie umakzeptabel und sollte nicht verwendet werden? Nun, nicht wirklich. Manchmal kann man vernüftigerweise annehmen, dass die Menge an Daten, die Sie durchsuchen müssen, nicht ca. 10.000 Zeilen übersteigt. Solange die Antwortzeiten akzeptabel für die Benutzer sind und keine Locking Problem bestehen, gibt es keinen Grund Ihre Programmierung komplexer als notwendig zu machen
(Ausserdem ist es nicht ganz wahr, dass die Prozedur keinen Index verwendet. Den Plan, den ich erhalten habe, enthielt einen Clustered Index Scan - also tatsächlich einen Table Scan - auf Order details, die die grösste Tabelle ist. Auf allen anderen Tabellen wurde durch ihren clustered indexes zugegriffen.)
Variationen zu diesem Thema
Anstelle OR zu verwenden wie oben, schreiben manche Leute:
o.orderID = coalesce(@orderid, o.OrderID)
coalesce() ist eine Funktion, die eine Liste von Werten akzeptiert und gibt den ersten nicht-Null Wert aus der Liste zurück oder NULL, falls es keinen nicht-NULL Wert in der Liste gibt. Ist also @orderid NULL, erhalten Sie o.OrderID = o.OrderID.
Obwol dies wie eine no-op Bedingung aussieht, und für OrderID auch ist, da diese Spalte kein NULL erlaubt. Schreiben Sie aber die Prozedur um, so dass sie coalesce stattdessen verwendet, erhalten Sie keine Zeilen für @orderid = 10654, da für diesen Kunden die Spalte Region Null ist. Das kommt daher, dass in SQL NULL nicht gleich NULL ist. NULL steht für einen "unbekannten Wert" und ob zwei unbekannte Werte gleich oder unterschiedlich ist natürlich ebenfalls unbekannt (Beachten Sie, dass falls die Einstellung ANSI_NULLS aus ist, NULL tatsächlich gleich NULL ist. Obwohl diese Einstellung standardmässig OFF ist, sollten Sie sich niemals darauf verlassen.). Aus diesem Grund rate ich Ihnen davon ab, dieses Konstrukt routinemässig zu verwenden. Performancemässig existiert das gleiche Problem wie mit OR. SQL Server wird keinen Index auf OrderID verwenden. Sie bemerken vielleicht eine unterschiedlichen Abfrageplan und für einige Input Parameter erhalten Sie bessere oder schlechtere Performance, es wird aber nie ein guter Plan sein.
IF Statements verwenden
Da die Verwendung von IF für jede Kombination ist durchführbar ist, können Sie herausfinden, welches die wichtigsten Suchparameter sind und welche diejenigen mit brauchbaren Indizes sind. Alles in allem, ist eine Bedingung wie c.Country = @country OR @country IS NULL ziemlich harmlos, da es sowieso keinen Index auf Customers.Country (Aber wir werden im letzten Beispiel sehen, dass dies nicht vollkommen harmlos ist. Es kann den Optimierer dennoch verwirren).
Zur Demonstration habe ich search_orders_4 geschrieben, die ich hier in einer gekürzten Form wiedergebe. In dieser Prozedur habe ich drei verschiedene Code Pfade: Einen, falls @orderid nicht gegeben ist, einen für @custid und einen für den Rest:
IF @orderid IS NOT NULL
BEGIN
SELECT ...
WHERE o.OrderID = @orderid
AND od.OrderID = @orderid
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
AND (od.ProductID = @prodid OR @prodid IS NULL)
AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY o.OrderID
END
ELSE IF @custid IS NOT NULL
BEGIN
SELECT ...
WHERE (o.OrderDate >= @fromdate OR @fromdate IS NULL)
AND (o.OrderDate <= @todate OR @todate IS NULL)
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
AND c.CustomerID = @custid
AND o.CustomerID = @custid
AND (od.ProductID = @prodid OR @prodid IS NULL)
AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY o.OrderID
END
ELSE
BEGIN
SELECT ...
WHERE (o.OrderDate >= @fromdate OR @fromdate IS NULL)
AND (o.OrderDate <= @todate OR @todate IS NULL)
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
AND (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
AND (c.City = @city OR @city IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @country OR @country IS NULL)
AND (od.ProductID = @prodid OR @prodid IS NULL)
AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER BY o.OrderID
END
Wie Sie sehen, habe ich einige der Bedingungen entfernt, die redundant sind, falls eine order id oder eine customer ist gegeben ist. Sie werden auch den Gebrauch des "doppelt gemoppelt" Features für die Bedingungen @orderid und @custid, in diesem Fall dem Optimierer hilft.
Ich habe @orderid herausgenommen, da auf dieser Spalte der Primarschlüssel liegt und wenn der Benutzer nach einer einzelnen order id sucht, erwartet er eine prompte Antwort (wohingegen er vielleicht eine Verzögerung akzeptiert bei einer Suche nach, z.B. Preis). Ausserdem habe ich @custid, da die customer_id ein mnemonischer String ist, mit dem der Benutzer vielleicht arbeitet. Zusätzlich existiert ein Index auf dieser Spalte sowohl in Orders als auch Customers.
Dies sind nicht die einzigen indizierten Spalten. Tatsächlich, in unserem Fallbeispiel, sind die einzigen Suchspalten, die nicht indiziert sind, Customers.Country und Order Details.UnitPrice.
Ich hätte weitermachen können mit mehr IF Statements, aber wie Sie erkennen, nimmt die Komplexität der IF Statements rapide zu, wenn ich mehr Bedingungen hinzufüge. Möchte ich beispielweise verschiedene Zweige im OrderDate haben, müsste ich folgendes schreiben:
ELSE IF @custid IS NOT NULL AND (@fromdate IS NOT NULL OR @todate IS NOT NULL)
...
ELSE IF (@fromdate IS NOT NULL OR @todate IS NOT NULL)
...
ELSE IF @custid IS NOT NULL
Würde ich nicht die erste Bedingung einschliessen, würde der Benutzer, der einen Kunden mit einer kleinen Anzahl an Aufträgen eingibt und ein bedeutungsloses zukünftiges @todate im falschen Zweig landen. Durch den ersten Zweig kann der Optimierer eine Entscheidung treffen.
Performance Beobachtungen
Selbst wenn Sie die Logik so aufsplitten, wählt der Optimierer vielleicht nicht den Index, den Sie erwarten würden. Ehrlich gesagt, als ich search_orders_4 schrieb, habe ich vergessen die ORDER BY Klausel einzufügen. Als ich folgende Statements ausführte:
EXEC search_orders_4 @orderid = 10654
EXEC search_orders_4 @custid = 'ALFKI'
stellte ich fest, dass SQL Server die Indizes verwendet, die ich erwartet hatte. Da ich nicht WITH RECOMPILE verwendete, konnte der zweite Aufruf den abgespeicherten Plan der ersten Ausführung verwenden. Wenn ich aber sage:
EXEC search_orders_4 @custid = 'ALFKI' WITH RECOMPILE
um einen neuen Plan zu erzwingen, scannte der Optimierer über Orders.OrderDate, was nicht besonders effektiv zu sein scheint (Nachdem ich die ORDER BY Klausel hinzugefügt habe, verwendete der Optimierer den Index über Orders.CustomerID wie ich es erwartet habe).
Da Northwind eine sehr kleine Datenbank ist, sollten Sie keine generellen Rückschlüsse hieraus ziehen. Fazit ist, dass Sie Tests durchführen müssen mit Daten in der Grössenordnung wie Sie sie in Produktion erwarten müssen, um zu sehen, ob Sie die Performance erhalten, die Sie erwarten.
Variationen des Themas
Simon Sabin hat mir eine Alternative, um alle Suchbedingungen in der Prozedur zu haben, vorgeschlagen, eine Subprozedur für jede Alternative zu verwenden. Nachteil dieses Ansatzes ist, dass Sie die Logik über verschiedene Prozeduren verteilen. Die Wartbarkeit wird dadurch erschwert. Es gibt aber auch die andere Seite der Performance: Jede Prozedur verwendet stets den optimalen für ihre Aufgabe und wird gecached. Ausserdem, und das sollte nicht vernachlässigt werden, gibt es keine Rekompilierungen mehr, und diese Rekompilierungen können bei langen Prozeduren schnell mal einige Sekunden beanspruchen.
Umachandar's Trickkiste
Hier sind einige Tricks, die ich von SQL Server MVP Umachandar Jaychandran gelernt habe.
Verwendung von BETWEEN
Er schlug vor, anstelle von
o.OrderID = @orderID OR @orderID IS NULL
folgendes zu verwenden:
You should use:
o.OrderID BETWEEN coalesce(@orderID, @MinInt) AND
coalesce(@orderID, @MaxInt)
Hier sind @MinInt und @MaxInt der kleinste und grösste mögliche Integer Wert. Dadurch erreichen Sie, dass der Optimierer den Index über OrderID, da, falls @orderid NULL ist, der Plan dennoch das korrekte Ergebnis erzeugt.
Nullable Spalten
Beachten Sie, dass dieser Trich nicht mit nullable Spalten funktioniert. Wollen Sie also folgendes in einer Abfrage verwenden:
c.Region BETWEEN coalesce(@region, @MinInt) AND
coalesce(@region, @MaxInt)
gibt die Abfrage:
EXEC search_orders @orderid = 10654
keine Zeilen zurück, da der Kunden dieses Auftrag keiner Region zugeordnet werden kann.
Wie erhalten Sie Min und Max Werte
Wie erhalten Sie die @MinInt und @MaxInt Werte? Das Einfachste ist die Grenzen für den Datentypen zu verwenden. Dies funktioniert für integer, binary und datetime Spalten und mit einiger Vorsicht auch für float/real, numeric/decimal und money Spalten.
Für Zeichen Daten können Sie kaum hart-kodierte Werte verwalten, da diese Collationsabhängig sind. Ein Weg diese aus einer Tabelle zu erhalten, ist die Verwendung von SELECT MAX() und SELECT MIN(). Machen Sie dies aber nur auf indizierten Spalten! Entscheiden Sie sich, die Variablen zu überspringen und setzen die MIN/MAX in die Hauptabfrage, gibt es eine Sache, auf die Sie aufpassen müssen:
o.CustomerID BETWEEN
isnull(@custid, (SELECT MIN(CustomerID) FROM Customers)) AND
isnull(@custid, (SELECT MAX(CustomerID) FROM Customers))
Warum hier isnull und nicht coalesce? isnull ist eine ältere Funktion, ähnlich zu coalesce, die nur zwei Parameter akzeptiert. Der Grund, warum Sie hier isnull verwenden sollten ist, dass Umachandar herausgefunden hat, dass er bei Verwendung von coalesce, üble Table Scans erhielt und schlechte Performance erzielte.
LIKE verwenden
Als Alternative schlug Umachandar für Zeichendaten vor:
c.City LIKE coalesce(@city, '%')
Auch hier gilt, dies ist ein Trick, der nur für Nicht-Nullable Spalten funktioniert (Customers.City ist tatsächlich nullable, es gibt aber keine NULL Werte für diese Spalte in Northwind.) Ausserdem funktioniert es nur mit varchar/nvarchar. Wende ich dies auf die CustomerID, nchar(10) ist, erhalte ich keine Zeilen zurück. Grund hierfür ist, dass das erste Argument für coalesce den Typen des Wertes festlegt, den coalesce zurückgibt. Da @custid nchar(10) ist, wird der Literal '%' um 9 Leerzeichen erweitert, was auf keine customer id in Northwind zutrifft. Ich könnte dies umgehen, indem ich @custid in nvarchar(10) ändere.
Ein weiteres potentielles Problem ist, das Sie nun aber den Benutzern erlauben müssen etwas wie %York einzugeben, was vielleicht nicht das Beste ist. Der Index auf City ist auf keinen Fall geeignet für diesen Suchstring
search_orders_5
Unter Verwendung von Umachandar's Tricks habe ich search_orders_5 geschrieben, die sich folgendermassen (abgekürzt) liest.
SELECT @minint = convert(int, 0x80000000),
@maxint = convert(int, 0x7FFFFFFF)
IF @custid IS NULL
SELECT @mincustid = MIN(CustomerID), @maxcustid = MAX(CustomerID)
FROM Customers
SELECT ...
WHERE o.OrderID BETWEEN coalesce(@orderid, @minint) AND
coalesce(@orderid, @maxint)
AND od.OrderID BETWEEN coalesce(@orderid, @minint) AND
coalesce(@orderid, @maxint)
AND o.OrderDate >= coalesce(@fromdate, '17530101')
AND o.OrderDate <= coalesce(@todate, '99991231')
AND od.UnitPrice >= coalesce(@minprice, 0)
AND od.UnitPrice <= coalesce(@maxprice, 1E18)
AND o.CustomerID BETWEEN coalesce(@custid, @mincustid) AND
coalesce(@custid, @maxcustid)
AND c.CustomerID BETWEEN coalesce(@custid, @mincustid) AND
coalesce(@custid, @maxcustid)
AND c.CompanyName LIKE coalesce(@custname + '%', '%')
AND c.City LIKE coalesce(@city, '%')
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @country OR @country IS NULL)
AND od.ProductID BETWEEN coalesce(@prodid, @minint) AND
coalesce(@prodid, @maxint)
AND p.ProductID BETWEEN coalesce(@prodid, @minint) AND
coalesce(@prodid, @maxint)
AND p.ProductName LIKE coalesce(@prodname + '%', '%')
ORDER BY o.OrderID
Um den kleinsten und den grössten möglichen Integer Wert zu erhalten, arbeite ich mit zwei Bitmustern, die einfacher zu merken sind als die aktuellen Nummern. Eine kleine Optimierung liegt darin, dass ich nur die min und max Werte für die customer id lese, wenn @custid NULL ist.
Sie sehen, ich verwende weder BETWEEN noch LIKE für alle Spalten. Wir haben bereits festgestellt, dass wir diesen Trick nicht für Customers.Region verwenden können, da diese Spalte NULL Werte enthält. Ausserdem existiert für Customers.Country sowieso kein Index (Und genau wie City, sind NULL Werte zulässig, auch wenn es keine NULL Werte in Northwind gibt).
Für die Filter auf Orders.Orderdate und Order Details.UnitPrice, brauche ich kein BETWEEN zu verwenden, da wir sowieso einen Bereich filtern wollen. Die Werte, die ich die ich für den zweiten Parameter von coalesce() verwende, sind das erste und letzte mögliche Datum, das vom datetime Datentypen unterstützt wird. Für die money Spalte habe ich einfach plausible Werte für max und min angenommen
Um dem Optimierer etwas mehr zum Arbeiten zu geben, habe ich wieder für beide Tabelle den Test auf den gejointen Spalte eingeschlossen.
Was für einen Plan habe ich erhalten?
Beim Testen dieser Prozedur, habe ich herausgefunden, dass sie tatsächlich Indizes verwendete. Nur, egal welche Werte ich eingab und egal, wie oft ich WITH RECOMPILE angab, ich habe stets den gleichen Abfrageplan erhalten, der mit einem Clustered Index Seek auf Order Details anfing. Und wenn der einzige Parameter ein company name oder ein product name ist, ist dieser Clustered Index Seek nicht weiter als ein verkleideter Table Scan. Versuchen Sie einmal:
EXEC search_orders_1 @prodname = 'Lakka'
EXEC search_orders_5 @prodname = 'Lakka'
Sie werden sehen, dass die Abfrageplänen radikal voneinander abweichen.
Nochmals, Northwind ist eine kleine Datenbank, Sie können diese Beoabchtungen nicht einfach übertragen auf grosse Datenbanken mit dem tausendfachen an Datenbankobjekten. Sie müssen es für Ihren speziellen Fall austesten und schauen welche Abfragepläne Sie erhalten.
Temporäre Tabellen verwenden
Manchmal können Sie Ihren Code vereinfachen, indem Sie ein oder zwei temporäre Tabellen verwenden. Schauen wir uns einmal zwei Möglichkeiten an.
Die unpassenden löschen
Von dieser Methode habe ich während der letzten Jahre heftigen Gebrauch gemacht. Sie ist nicht der Performance Sieger, im Gegenteil, Sie müssen sogar extrem vorsichtig beim Einsatz sein. Aber der daraus resultierende Code ist leicht zu verstehen und zu warten. Die Idee ist es, dass Sie die Basisselektion in einer temporären Tabelle speichern und anschliessend die Zeilen löschen, die nicht den Suchbedingungen des Benutzers entsprechen. Hier ist ein Auszug aus search_orders_6 wo ich diese Technik verwende:
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
INTO #temp
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
IF @orderid IS NOT NULL
DELETE #temp WHERE OrderID <> @orderid
IF @fromdate IS NOT NULL
DELETE #temp WHERE OrderDate < @fromdate
...
IF @region IS NOT NULL
DELETE #temp WHERE Region <> @region OR Region IS NULL
...
SELECT * FROM #temp ORDER BY OrderID
Sie sehen, dieser Code ist sehr einfach. Das Einzige, auf das Sie achten müssen, sind nullable Spalten wie Region.
Da aber diese Prozedur wesentlicher länger als search_orders_3 ist, hat man keine offensichtlichen Vorteile durch diese Methode. Sie ist vielmehr nützlich bei koplexeren Suchbedingungen. So zum Beispiel, falls Sie eine grosse Tabellen haben, auf die Sie nur dann zugreifen müssen, wenn ein bestimmter Inputparameter gegeben ist.
Diese Prozedur wird auch niemals von Indizes Gebrauch machen können, was in der kleinen Northwind Datenbank nicht viel ausmacht. Im Alltagseinsatz würden Sie wahrscheinlich bereits nach den wichtigsten Input Parametern filtern, bevor Sie die DELETE Operation starten, um gemäss den Suchbedingungen des Benutzers zu filtern und das Ergebnis zu verfeinern.
Die temporären Tabellen brauchen auch nicht alle Spalten der endgültigen Ergebnismenge zu enthalten. So wie in search_orders_6. Daher kann ich faul sein und SELECT * am Ende verwenden (Dies ist eine der wenigen Situationen, in denen ich es durchaus zulässig finde, SELECT * in Produktionscode zu verwenden). Enthält die Ergebnismenge viele Spalten, haben Sie alle Gründe, die temporäre Tabelle so klein wie möglich im Hinblick auf Anzahl der Spalten und damit Grösse zu halten. Beispielsweise hätte ich es gut sein lassen können mit OrderID, OrderDate und UnitPrice, CustomerID und ProductID. Das sind die Primärschlüssel für die Tabellen, die ich durchsuche und die Suchspalten in den "grossen" Tabellen Orders and Order Details.
Von unten nach oben filtern
Dieses letzte Beispiel zeigt einen Mix der Strategien. Es ist eher eine Demonstration dafür, dass es keine eindeutige Lösung gibt, diese Art von Suchen in statischem SQL zu implementieren. Sie müssen vielmehr Ihre eigene Phantasie kreisen lassen und, was noch wichtiger ist, Ihr Wissen um die Daten mit denen Sie umgehen.
Die nächste Prozedur ist search_orders_7. Sie startet folgendermassen:
IF @orderid IS NULL
BEGIN
INSERT #custids (custid)
SELECT CustomerID
FROM Customers c
WHERE (c.CustomerID = @custid OR @custid IS NULL)
AND (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
AND (c.City = @city OR @city IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @country OR @country IS NULL)
END
INSERT #products(ProductID, ProductName, UnitsInStock, UnitsOnOrder)
SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder
FROM Products
WHERE (ProductID = @prodid OR @prodid IS NULL)
AND ProductName LIKE coalesce(@prodname + '%', '%')
Hier fülle ich zwei temporäre Tabellen; eine für customers und ein für products. Um verschiedene Strategien zu zeigen, speichere ich nur die customer id in der temporären Tabellen, während ich bei products wesentlich mehr einfüge. Indem ich nur die CustomerID kann ich die Tabelle sehr klein halten, allerdings muss ich einen join gegen Customers ausführen in der endgültigen Abfrage, wohingegen ich für Products alles in der temporären Tabelle habe, was ich brauche.
Aus Vereinfachungsgründen versuche ich hier nicht, einen Index zu verwenden. Zwar hängt es von Ihren Gegebenheiten ab, aber typischerweise sind die Tabellen für Kunden und Produkte deutlich kleiner als die Auftragstabellen.
Wie Sie sehen, durchsuche ich nicht Customers wenn der Benutzer eine order id vorgibt, da in diesem Fall der Kunde durch den Auftrag vorgegeben ist (Dies gilt nicht für Produkte!).
Das nächste Statement in der Prozedur liest sich folgendermassen:
EXEC search_orders_7_sub @orderid, @fromdate, @todate, @minprice, @maxprice
In diese Prozedur (verfügbar in der gleichen Datei wie search_orders_7), habe ich die WITH RECOMPILE Option angegeben. In abgekürzter Form sieht sich wie folgt aus
IF @orderid IS NULL
BEGIN
SELECT ...
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN #custids t ON o.CustomerID = t.custid
JOIN Customers c ON o.CustomerID = c.CustomerID
AND t.custid = c.CustomerID
JOIN #products p ON p.ProductID = od.ProductID
WHERE o.OrderDate >= coalesce(@fromdate, '17530101')
AND o.OrderDate <= coalesce(@todate, '99991231')
AND od.UnitPrice >= coalesce(@minprice, 0)
AND od.UnitPrice <= coalesce(@maxprice, 1E18)
ORDER BY o.OrderID
END
ELSE
BEGIN
SELECT ...
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN #products p ON p.ProductID = od.ProductID
WHERE o.OrderID = @orderid
AND od.OrderID = @orderid
AND od.UnitPrice >= coalesce(@minprice, 0)
AND od.UnitPrice <= coalesce(@maxprice, 1E18)
END
Ähnlich wie in search_orders_4 verwende ich verschiedene Zweige, je nachdem, ob eine order id vorgegeben ist oder nicht. Sie sehen dass ich nur dann, wenn keine order id vorliegt sowohl #custids als auch Customers im Join verwende. Andererseits benutze ich immer #products, aber nie Products, da ich alle Daten, die ich brauche, bereits in der temp Tabelle habe.
Warum zwei Prozeduren? Als ich mit der anderen statischen search_orders Prozedur arbeitete, konnte ich feststellen, dass der Optimierer scharf darauf ist, Order Details (die grösste er vier Tabellen) zu scannen. Und wenn Sie sich einmal alle Orders anschauen, ist dies auch die richtige Entscheidung. Allerdings nicht, wenn Sie nur die Orders des Kunden ALFKI sehen wollen oder alle Kunden in Buenos Aires. Aus diesem Grund wollte ich, das SQL Server jedesmal die Grösse der temporären Tabelle berücksichtigt. SQL Server entscheidet vielfach, eine Prozedur zu rekompilieren, wenn sich die Menge an Daten in einer temporären Tabelle ändert. Möchte ich aber jedesmal eine Rekompilierung erzwingen, Muss ich WITH RECOMPILE angeben ( Oder ich verwende dynamische SQL, aber seit SQL Server 7 und höher werden auch ad-hoc Abfragen gecached, so das dies nicht immer zu einer Rekompilierung führt. Und in jedem Fall würden Sie diesen Abschnitt erst gar nicht lesen, wenn Sie dynamisches SQL verwenden könnten).
Bei Ausführung von search_orders_7 mit unterschiedlichen Input Parametern, erhielt ich Abfragepläne, die gut auf diese abgestimmt zu sein schienen (Ich sage schienen, da selbst wenn ein Plan gut aussehen mag, es sich vielleicht zeigt, dass seine Performance schlecht ist. Letztendlich müssen Sie ein Benchmarking durchführen, in dem Sie Ihre Suchen zeitlich messen). Allerdings ist dies auch situationsabhängig. Ursprünglich hatte ich die Bedingungen für UnitPrice wie in search_orders_3 geschrieben:
AND (od.UnitPrice >= @minprice OR @minprice IS NULL)
AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
In diesem Fall scheint der Optimierer jedesmal einen Table Scan durchzuführen in Order Details.
Dies beweist zwar nicht, dass ein Weg besser als der andere ist, aber der Optimierer scheint sensibel auf diese Art von "weissem Störfaktor" in der Abfrage zu reagieren. Kodieren Sie dies in dynamischem SQL wird der Optimierer niemals den unit price sehen, es sei denn, der Benutzer gibt ihn als Suchbedingung vor.
Tricks für komplexe Bedingungen
In dem Beispiel, dass ich in diesem Artikel benutzt habe, müssen wir stets auf sämtliche Tabellen zugreifen. In vielen Situationen aber muss nur dann auf Tabellen zugegriffen werden, wenn ein bestimmter Input Parameter eingegeben wurde, oder Sie müssen in Abhängigkeit von mehreren Parametern, auf unterschiedliche Tabellen zugreifen.
Verwenden sie dynamisches SQL, sind diese Bedingungen ziemlich einfach zu handhaben. Bei Erstellung der Abfrage schliessen Sie einfach nur die Tabellen ein, die Sie benötigen, um den aktuellen Werte der Input Parameter zu genügen.
Einige der Methoden für statisches SQL können leicht dahingehend erweitert werden, um mit solchen Fällen umzugehen. Ich denke dabei hauptsächlich an IF statements und temp tables. Es mag aber auch, je nach Situation, noch weitere Tricks geben. An dieser Stelle möchte ich nur einen Fall behandeln, für den Phillipp Sumi eine gute Lösung erarbeitet hat.
Selektierung anhand des Vorhandenseins in einer anderen Tabelle
Um diesen Fall abzudecken, lassen Sie uns einen weitere Parameter unserer Prozedur hinzufügen:
@suppl_country. Wird dieser Parameter angegeben, soll die Prozedur nur Informationen zurückgeben zu Produkten, bei denen der Hersteller aus dem angegebenen Land stammt. Dies würde von Ihnen erfordern, dass Sie eine EXISTS Bedingung zur Überprüfung hinzufügen. Phillipp Sumi's Vorschlag war, die Bedingungen folgendermassen zu schreiben:
@suppl_country IS NULL OR EXISTS (SELECT *
FROM Suppliers s
WHERE s.SupplierID = p.SupplierID
AND s.Country = @suppl_country)
Der springende Punkt hierbei ist, dass, wenn @suppl_country NULL ist, SQL Server nicht auf die Suppliers Tabelle zugreifen muss. Um dies zu verdeutlichen, habe ich search_orders_9 geschrieben, die aus Vereinfachungsgründen eine Erweiterung von search_orders_3 ist. Um zu verfolgen, was passiert, führen Sie folgendes aus:
SET STATISTICS IO ON
go
EXEC search_orders_9 @country = 'Sweden' WITH RECOMPILE
EXEC search_orders_9 @suppl_country = 'Sweden' WITH RECOMPILE
Wenn Sie die Abfrageplänen vergleichen, werden Sie feststellen, dass diese identisch sind. Betrachten Sie aber den Output von SET STATISTICS IO on werden Sie etwas Interessantes bemerken:
Table 'Suppliers'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Products'. ...
...
Table 'Suppliers'. Scan count 2075, logical reads 4150, physical reads 0, read-ahead reads 0.
Table 'Products'. ...
...
Beachten Sie bitte die erst fettgedruckte Zeile. SQL Server beachtet diese Tabelle überhaupt nicht. Grund hierfür ist, das der vom Optimierer verwendete Operator, der Nested loops/Left Semi Join es SQL Server erlaubt, erst die Variable zu evaluieren und daher festzustellen, dass es keinen Grund gibt, auf diese Tabelle zuzugreifen.
Dies mag Sie an shortcutting wie in C++ erinnern, aber T-SQL verfügt über keine Shortcut Mechanismen. Tatsächlich könnten Sie die Reihenfolge der Bedingungen im OR Ausdruck verändern und SQL Server würde immer noch nicht auf Suppliers zugreifen.
Aus Gewohnheit auch hier wieder der Hinweis: Sie müssen testen, ob Sie auch tatsächlich den Abfrageplan erhaltet, den Sie erwarten. SQL Server kann eventuell sich für eine unterschiedliche Behandlung der EXISTS Klausel entscheiden und immer auf die Tabelle zugreifen.
Eine Hybridlösung – Verwendung von statischem und dynamischem SQL
Das Folgende basiert auf einer Idee von .Net MVP Eric Garza. Können Sie Ihren Benutzern nicht direkte SELECT Berechtigungen auf die in der Suche involvierten Tabellen geben, können Sie unter Umständen einen View oder eine benutzerdefinierte Funktion erstellen, die dem Benutzer diejenigen Daten zeigt, auf die der Zugriff erlaubt ist und ihm stattdessen SELECT Zugriff auf den View oder die Funktion zu gewähren.
Auf Basis dieser Idee, habe ich den view search_order_view definiert und die Funktion search_order_fun:
CREATE VIEW search_order_view AS
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
go
CREATE FUNCTION search_order_fun () RETURNS TABLE AS
RETURN(SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID)
go
Danach habe ich search_orders_8 geschrieben, die sehr ähnlich zu search_order_1 ist, bei der ich aber die Zeilen 18-28 verändert habe, die nun so aussehen:
SELECT @sql = 'SELECT * FROM search_order_view WHERE 1 = 1 '
oder bei Verwendung der UDF:
SELECT @sql = 'SELECT * FROM search_order_fun() WHERE 1 = 1 '
Dies verändert nicht die Performance, da SQL Server keinen Abfrageplan für einen View oder eine Inline Tabellenfunktion speichert. Vielmehr behandelt SQL Server Views und Inline Funktionen wie Makros, so dass Sie im Grunde die Abfrage aus search_orders_1 verwenden (Beachten Sie bitte, dass dies nicht für Multi-Step Tabellen Funktionen gilt. In diesem Fall wird die Funktion separat behandelt und die zurückgegebene Tabelle ist eigenständig).
In unserem Szenario sind search_orders_view und search_orders_fun gegenstandslos, da selbst wenn Sie den Benutzern den SELECT Zugriff auf Orders, Order Details, Customers und Products vorenthalten, diese dennoch Zugriff auf alle Daten hätten, da die Benutzer nur Daten zu bestimmten Kunden sehen können. Dies wird in einer Tabelle namens UserAcceessCustomers definiert:
CREATE TABLE UserAccessCustomers
(UserName sysname NOT NULL,
CustomerID nchar(10) NOT NULL,
CONSTRAINT pk_uca PRIMARY KEY (UserName, CustomerID))
UserName ist hier einfachheitshalber der gleiche wie der Username in sysusers in der Datenbank. In diesem Fall könnten Sie den View so schreiben:
CREATE VIEW search_order_view AS
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN UsersAccessCustomers u ON u.CustomerID = c.CustomerID
AND u.CustomerID = o.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE u.UserName = USER
USER in der letzten Zeile ist eine eingebaute Funktion, die den Namen des aktuellen Benutzers in der aktuellen Datenbank zurückgibt.
Achtung: So wie im obigen Beispiel würden Sie normalerweise row-level Sicherheit im SQL Server implementieren. SQL Server MVP Steve Kass hat jedoch herausgefunden, dass es einem Benutzer möglich ist, Information über Daten herauszubekommen, die er eigentlich nicht sehen dürfte. Wie ich weiter oben erwähnte, ist ein View oder eine benutzerdefinierte Funktion nur eine Art von Makro und der Abfrageoptimierer ist unabhängig in der Wahl der Reihenfolge, in der die Tabellen evaluiert werden. Dies kann ein nicht-priviligierter Benutzer zu seinem Vorteil nutzen. Die Attacke erfordert zwar gewisse Fähigkeiten, falls es aber zwingend ist, dass Benutzer auch nicht nur einen Hauch der Daten zu sehen bekommen, die sie nicht sehen sollen. sollten Sie ihnen keine SELECT Berechtigungen auf Views oder Inline Funktionen wie die obige gewähren (Da eine Multi-Step Inline Funktion vollkommen separat ausgewertet wird, wäre eine solche Funktion sicher). Aber dann müsste SQL Server die komplette Menge an Daten materialisieren, die der Benutzer betrachten darf, auch wenn es nur um eine table Variable geht. Dies würde wahrscheinlich zu Lasten der Performance gehen.
Eric Garza kommt aus einer anderen Richtung zu diesem Ergebnis: Er wollte Benutzern Zugriff auf grosse Tabellen verweigern, so dass diese keine Abfragen abschicken konnten, welche zu Table Scans führen und damit andere Benutzer blockieren könnten. Er stellte eine Reihe von benutzer-definierten Funktionen zur Verfügung, bei denen von den Benutzer verlangt wurde, gewisse Input Parameter anzugeben. Auf diese Art war es egal, welche Abfrage abgeschickt wurde, er stellte so sicher, dass SQL Server Indexes verwenden konnte.
Schlussfolgerung
Sie haben nun verschiedene Wege kennengelernt, diese Art von Suchen zu implementieren. Sowohl in dynamischem SQL als auch mit statischem SQL. Sie haben gesehen, dass Sie mit dynamischem SQL die beste Performance erzielen und gleichzeitig Ihren Code wartbar halten können. Mit statischem SQL müssen Sie Kompromisse eingehen, um eine ansprechende Performance zu erhalten. Sie haben auch gesehen, dass Sie bei statischem SQL kreativ sein müssen und schauen müssen, welche Tricks die geeignetsten für die Art von Suche sind, die Sie implementieren sollen.
Lassen Sie mich noch einmal betonen dass, egal, ob Sie sich für dynamisches oder statisches SQL entscheiden, Sie Ihre Prozeduren auf sämtliche Input Parameter hin testen müssen. Am besten auch noch die Kombination mehrere. Sie müssen im Hinblick auf Korrektheit und Performance testen. Und um die Performance zu testen, brauchen Sie Daten die den Produktionsdaten ähneln. Wenn Sie zehn Millionen Order und 50.000 Kundendatensätze erwarten, können Sie nicht mit einer Spielzeugdatenbank von der Grösse wie Northwind herumexperimentieren.
Feedback und Danksagung
Mein Dank gilt den SQL Server MVPs Steve Kass and Umachandar Jaychandran, Phillipp Sumi, Simon Sabin, Thomas Joseph Olaes, Simon Hayes, Sloan Holliday and .Net MVP Eric Garza für ihre wertvolle Unterstützung.
Haben Sie Fragen oder Kommentare zum Inhalt dieses Artikel, können Sie mir gerne eine Mail schicken an esquel@sommarskog.se. Arbeiten Sie an einem spezifischen Problem, können Sie sich ebenfalls an mich wenden. Gleichwohl möchte ich Sie ermuntern, sich in einem solchen Fall an ein öffentliches Forum für SQL Server zu wenden, da es dort viele Leute gibt, die Ihre Fragen beantworten können. Und Sie die Antwort vielleicht sogar schneller erhalten werden!).
Revisions Geschichte
26.12.2004 – Verweis auf meinen Arrays Artikel in der Vorstellung der Fallstudie hinzugefügt.
28.04.2004 – Anmerkung über die Sortierung des Outputs am Ende des Fallstudien Abschnitts.
19.04.2004 – Auf Anregung von Simon Sabin einen weiteren Vorschlag im Abschnitt über die Verwendung von IF statements hinzugefügt.
02.12.2003 – Methode hinzugefügt, wie Zugriff auf Tabellen mit der EXISTS Klausel in statischen Suchen vermieden werden kann.
Den Originalartikel finden Sie hier
Dieser Eintrag wurde eingetragen von admin und ist abgelegt unter SQL Server. Tags: coding, dynamisches sql, erland sommarskog, query, t-sql