Why is using a parameterized query to insert data into a table faster than appending the values to the query string?

Question

Why is using a parameterized query to insert data into a table:

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

faster than appending the values to the query string:

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

?

When I use the command in a loop to insert 10K rows, the parameterized query is an order of magnitude faster than the other one.

I know a parametrized query has security and maintainability benefits, and it's the recommended way to use, but now I'm interested in an explanation on why is it that much faster?

Answer 1

In general, the most expensive part of performing an SQL query is building the execution plan - identifying which tables are going to be needed, determining the best indexes (if any) to use, etc. You can think of this as "compiling" the query if you like.

When you use a parametrized query, you can prepare it once and then just plug in different target values. Since it's the same operation with different data, there's no need to rebuild the execution plan each time. To extend the "compiling" metaphor, this is like re-running the same program with a different configuration file.

When you append the values, though, you're hardcoding them into the query, so it has to be re-prepared each time and you incur the cost of building a new execution plan for each iteration. Again with the "compiling" metaphor, this is like a C program with all of its configuration hardcoded - change one setting, and you have to recompile the whole thing.

(The other major cost you can run into when doing mass inserts is updating the indexes. If your table is indexed, you might want to try turning them off, doing your inserts, and turning them back on so it only has to be reindexed once instead of after each row is added.)