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

This is due to the database caching the query plans, which makes it faster.

For Sql server see this explanation

Execution Plan Caching and Reuse

Answer 2

Depending on the DB you're using, the usual reason is because the parameterized query only has to be compiled once, and the dynamic query version is recompiled on each use.

Answer 3

Simple. Parsing and preparing execution plan for a query takes a lot of time even before query execution starts.

When you append parameters as text to query, each query is different, so DB needs to parse it and prepare execution plan.

When you use parameters, you are sending the same query many times (with different data) and DB can simply reuse execution plan from earlier call.

In most situation that's simply text comparison between queries. For example in MS SQL Server it's enough to change case of a letter or add a space at end of query to force DB to recreate execution plan.

Answer 4

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.)