Betrachten wir einmal das folgende SQL Skript:
dbcc freeproccache -- Cache und Buffer leeren
dbcc dropcleanbuffers
declare @i int -- Schleifenvariable
,@m int -- Obergrenze der Schleifenvariablen
,@cmd nvarchar(500) -- Generiertes SQL Statement
set @i = 1
select @m = MAX(BusinessEntityID) from Person.Person
while (@i < @m)
begin
-- SQL Statement wird bei jedem Durchlauf neu generiert
set @cmd = 'declare @Lname nvarchar(50); ' +
' select @Lname = LastName ' +
'from Person.Person where ' +
'BusinessEntityID = ' + cast(@i as nvarchar(10))
exec (@cmd)
set @i = @i + 1
end
go
Mein Laptop benötigt zur Ausführung dieser Schleife etwa 18 Sekunden. In jedem Schleifendurchlauf wird das SQL Statement neu generiert und die WHERE-Klausel sieht ein klein wenig anders aus. Für den SQL Server bedeutet das: Er muss für jedes Statement einen Ausführungsplan erzeugen. Und so kommt es, dass im Plan Cache so viele Varianten dieses Statements stehen, wie es Schleifendurchläufe gibt, nämlich 20.779. Über die folgende Abfrage lässt sich das ermitteln:
select entries_count
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP'
Das wirklich Dumme an der Sache ist also nicht nur, dass für das Erzeugen des (immer gleichen) Ausführungsplans eine Menge CPU-Leistung verbraten wird. Außerdem läuft auch noch der Plancache voll, in dem SQL Server einmal erzeugte Ausführungspläne aufbewahrt in der Hoffnung, dass sie bald wieder verwendet werden können. Das können Sie sehr gut in dem Bericht "Memory Consumption" sehen, den Sie erhalten, wenn Sie im Management Studio mit der rechten Maustaste auf die Instanz (Oberster Knoten im Object Explorer) klicken und dann "Reports - Standard Reports - Memory Consumption" auswählen.
 |
| Cache mit immer wieder neu generierter Abfrage |
Und wie kann man das besser machen?
Schauen Sie sich das folgende modifizierte Skript an:
dbcc freeproccache -- Cache und Buffer leeren
dbcc dropcleanbuffers
declare @i int -- Schleifenvariable
,@m int -- Obergrenze der Schleifenvariablen
,@cmd nvarchar(500) -- SQL Statement
,@Lname nvarchar(50)
set @i = 1
select @m = MAX(BusinessEntityID) from Person.Person
-- Hier ist der Unterschied: Das SQL Statement bleibt
-- durch die Verwendung eines Parameters konstant.
set @cmd = 'select @Lname = LastName ' +
'from Person.Person where ' +
'BusinessEntityID = @Param1'
while (@i < @m)
begin
exec sp_executesql @cmd
,N'@Param1 int, @Lname nvarchar(50) OUTPUT'
,@Param1 = @i
,@Lname = @Lname OUTPUT;
set @i = @i + 1
end
Das SQL Statement wird hier nur einmal vor Beginn der Schleife erzeugt und dann immer wieder unverändert ausgeführt. Der Parameter @Param1 erhält bei jedem Schleifendurchlauf den aktuellen Wert der Zählervariablen zugewiesen. Dieses SQL Skript braucht auf meinem Rechner keine 2 Sekunden, ist also ungefähr 10-mal schneller als die erste Variante.
Wie sieht die Veränderung im Cache aus? Wieder hilft uns die bereits oben verwendete Abfrage weiter:
select entries_count
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP'
Sie zeigt, dass jetzt gerade einmal 2 Ausführungspläne im Cache liegen. Und hier liegt der wichtigste Vorteil, denn der Cache wurde nun nicht mit unbrauchbaren Ausführungsplänen überflutet. Die Grafik zeigt das sehr schön:
 |
| Cache nach Parametrisierung der Abfrage |
Bleibt nur noch eine Frage zu klären:
Wie erzeugt man parametrisierte SQL Abfragen mit Hochsprachen?
Am Beispiel eines ADO.NET Fragments sehen Sie, wie einfach das geht:
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select LastName from Person.Person where BusinessEntityID = @Id";
cmd.Parameters.Add("@Id", SqlDbType.Int);
Wie Sie sehen, ist der Aufwand gar nicht groß. Ich finde sogar eher, dass der Code dadurch übersichtlicher wird. Und die Performance-Vorteile können enorm sein.
