Parametrierte Queries bei "... WHERE foo in (1, 2, 3)" sinnvoll?
-
Dienstag, 1. Mai 2012 15:28Hallo,
ich möchte beginnen, das bisher durchgehend "dynamische SQL" einer Web-App umzustellen, also auf parametrierte Queries (aka variable binding) -- vor allem der Performance zu Liebe. Beispielsweise mit sp_executesql.
Des öfteren verwende ich Konstrukte wie
... WHERE ... AND tabelle.feld in (1, 2, 3) AND ...
Wobei Die Liste nicht nur 3 Elemente beinhalten kann, sondern auch mal 4, 10 oder 100 und alles dazwischen.
Meine Frage: Eine solche LISTE ist ja KEIN eigener Datentyp. D. h. ich müsste sie bei der Parametrisierung durch einzelne INT abbilden:
in (@p1, @p2, @p3)
Das bedeutet aber, dass die Anzahl der Parameter je nach Länge der Liste variiert. Ich befürchte nun, dass der SQL-Server den Ausführungsplan für einen Query mit einer Liste mit 3 Elementen nicht für einen Query mit einer Liste mit 4 Elementen verwenden wird, weil er vom Aufbau her unterschiedlich ist.
Ist das richtig?
Ist es zielführend in so einem Fall überhaupt zu parametrisieren?
Angenommen, die Listen können von 2 bis 100 Elemente haben, dann gäbe es ja 99 gecachte Ausführungspläne. Das käme mir komisch vor, ist aber vielleicht gar kein Problem?
Ich hoffe die Frage ist verständlich. Bis vor kurzem kam mir gar nicht in den Sinn dass dynamisches SQL schlecht sein könnte. Habe natürlich schon gegoogelt was das Zeug hielt...
LG,
Paul- Bearbeitet eigelb Dienstag, 1. Mai 2012 15:30 fettdruck ergänzt
Alle Antworten
-
Dienstag, 1. Mai 2012 19:43
Hallo Paul,
für dieses Zweck bieten sich die "Table Value Parameter" (TVP) an.
http://msdn.microsoft.com/en-us/library/bb510489.aspx
Die Seite enthält auch ein Beispiel für den Einsatz innerhalb einer Stored Procedure.
USE AdventureWorks; GO /* Create a table type. */ CREATE TYPE ProductSubcategoryType AS TABLE ( ProductSubcategoryID INT ); GO /* Create a procedure to receive data with the help of the table-valued parameter. */ CREATE PROCEDURE dbo.usp_SelectProductsByTVP @TVP ProductSubcategoryType READONLY AS SET NOCOUNT ON; SELECT * FROM Production.Product p WHERE p.ProductSubcategoryID IN (SELECT tvp.ProductSubcategoryID FROM @TVP tvp) ; GO DECLARE @pSubCategory AS ProductSubcategoryType; INSERT INTO @pSubCategory (ProductSubcategoryID) VALUES ((2)), ((5)), ((10)) EXECUTE dbo.usp_SelectProductsByTVP @pSubCategory;Beispiel für die Nutzung von TVP's in Verbindung mit ADO.Net findest du hier: http://msdn.microsoft.com/en-us/library/bb675163.aspx
-
Dienstag, 1. Mai 2012 19:57
Hallo Christian,
danke! Leider war ich zu doof zu erwähnen dass wir noch mit dem 2005er arbeiten. Da funzt das wohl noch nicht. Das wäre dann schon mal ein Grund für ein Upgrade. Wenn ich stattdessen temporäre Tabellen nehme, müsste ich hald gründlich prüfen ob es von der Performance noch was bringt.
LG, Paul
-
Dienstag, 1. Mai 2012 20:07
Hallo Paul,
dann hilft dir vielleicht folgender Blog-Eintrag weiter.
- Als Antwort markiert Robert BreitenhoferMicrosoft Contingent Staff, Moderator Dienstag, 15. Mai 2012 09:42
-
Dienstag, 1. Mai 2012 20:24
Hallo Christian,
nochmals dankeschön! Es scheint mir dubios, da mit XML rumzumachen, aber ich werde mal die Performance ermitteln -- solange die stimmt, soll's mir recht sein...
LG, Paul
-
Dienstag, 1. Mai 2012 20:33
Hallo Paul,
aus Performancegründen würde ich die Werte in eine temporäre Tabelle überführen. Gerade wenn du die Werte mehrmals verwenden willst.
Gruß Christian
-
Dienstag, 15. Mai 2012 09:42Besitzer
Hallo eigelb,
Ich gehe davon aus, dass die Antwort Dir weitergeholfen hat.
Solltest Du noch "Rückfragen" dazu haben, so gib uns bitte Bescheid.Grüße,
RobertRobert Breitenhofer, MICROSOFT
Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „Entwickler helfen Entwickler“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.
.png)
.png)
