Warum ist die Verwendung einer parameterisierten Abfrage zum Einfügen von Daten in eine Tabelle schneller als das Anhängen der Werte an den Abfragestring?

Question

Warum ist es schneller, einen parametrisierten Query zu verwenden, um Daten in eine Tabelle einzufügen:

string queryString = "insert into product(id, name) values (@id, @name)";

als die Werte an den Abfragestring anzuhängen:

string queryString = "insert into product(id, name) values (" + _id + ", " + _name + ")";

?

Wenn ich den Befehl in einer Schleife verwende, um 10K Zeilen einzufügen, ist der parametrisierte Query um eine Größenordnung schneller als der andere.

Ich weiß, dass ein parametrisierter Query Sicherheits- und Wartungsvorteile hat und der empfohlene Weg ist, aber jetzt interessiert mich eine Erklärung, warum er so viel schneller ist?

Answer 1

Im Allgemeinen ist der teuerste Teil bei der Ausführung einer SQL-Abfrage das Erstellen des Ausführungsplans - das Identifizieren der Tabellen, die benötigt werden, das Bestimmen der besten Indizes (falls vorhanden), usw. Man kann dies als "Kompilieren" der Abfrage bezeichnen, wenn man so möchte.

Bei Verwendung einer parametrisierten Abfrage können Sie sie einmal vorbereiten und dann einfach verschiedene Zielwerte einsetzen. Da es sich um dieselbe Operation mit unterschiedlichen Daten handelt, ist es nicht erforderlich, den Ausführungsplan jedes Mal neu zu erstellen. Um das metaphorisch auszudrücken, ist dies wie das erneute Ausführen eines Programms mit einer anderen Konfigurationsdatei.

Wenn Sie die Werte jedoch anhängen, werden sie in die Abfrage fest codiert, sodass sie jedes Mal neu vorbereitet werden muss und Sie die Kosten für das Erstellen eines neuen Ausführungsplans für jede Iteration tragen. Wiederum mit der "Kompilieren"-Metapher ist das wie ein C-Programm mit all seinen fest codierten Konfigurationen - ändern Sie eine Einstellung und Sie müssen das Ganze neu kompilieren.

(Die andere Hauptkostenquelle, auf die Sie bei Masseneinfügungen stoßen können, ist das Aktualisieren der Indizes. Wenn Ihre Tabelle indiziert ist, sollten Sie versuchen, sie auszuschalten, Ihre Einfügungen durchzuführen und sie dann wieder einzuschalten, sodass sie nur einmal neu indiziert werden muss, anstatt nach jeder hinzugefügten Zeile.)

Answer 2

Einfach. Das Parsen und Vorbereiten eines Ausführungsplans für eine Abfrage dauert lange, noch bevor die Abfrage ausgeführt wird.

Wenn Sie Parameter als Text an die Abfrage anhängen, ist jede Abfrage unterschiedlich, sodass die Datenbank sie parsen und den Ausführungsplan vorbereiten muss.

Wenn Sie Parameter verwenden, senden Sie dieselbe Abfrage viele Male (mit unterschiedlichen Daten) und die Datenbank kann einfach den Ausführungsplan aus dem früheren Aufruf wiederverwenden.

In den meisten Situationen handelt es sich einfach um einen Textvergleich zwischen den Abfragen. Zum Beispiel reicht es in MS SQL Server aus, die Großschreibung eines Buchstabens zu ändern oder am Ende der Abfrage ein Leerzeichen hinzuzufügen, um die Datenbank dazu zu zwingen, den Ausführungsplan neu zu erstellen.

Answer 3

Je nachdem, welche Datenbank Sie verwenden, ist der übliche Grund dafür, dass die parametrisierte Abfrage nur einmal kompiliert werden muss, während die dynamische Abfrage-Version bei jedem Gebrauch neu kompiliert wird.

Answer 4

Dies liegt daran, dass die Datenbank die Abfragepläne zwischenspeichert, was sie schneller macht.

Für den SQL Server siehe diese Erklärung

Ausführungsplan-Caching und -Wiederverwendung

Answer 5

Ich wette, es wäre nicht schneller, wenn Sie mehrere Werte verwenden würden
Sie können bis zu 1000 machen

string queryString = "insert into product(id, name) values " + 
"   (" + _id  + ", " + _name  + ")" + 
" , (" + _id1 + ", " + _name1 + ")" + 
" , (" + _id2 + ", " + _name2 + ")";