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

Why is parameterized query used to insert data in the table:

  String Query String = "Enter Product (id, name) value (@id, @name)";   string query string = "Enter in the product (id, name) values ​​(" + _id + "," + _name + ")";  

?

When I use the command in a loop to insert 10K rows, then the query with the parameter is a sequence of more intensity than the other.

I know that there is a security and maintenance benefit in a parametrized query, and this is the recommended way to use it, but now I am interested in clarifying why this is fast.

In general, the most expensive part of the SQL query is building the execution plan - to identify which tables are needed, to determine the best indexes (if Any) to use, etc. If you want you can think of the query as a "compilation."

When you use a parametric query, you can prepare it once and then simply plug it into different target values. Since this is the same operation with different data, there is no need to recreate the execution plan every time. To expand the "compilation" metaphor, it is similar to running the same program again with a separate program.

When you add value, however, you are working hard on them in the query, so this is the time to reproduce every time and you need to reproduce the cost of creating a new performance plan Spends. With the "compiling" metaphor, it's like a program in which all the configuration is hardcoded - change one setting, and you have to compile the whole thing again.

(The other major cost that you can run in when updating the index that is largely inserts. If your table is indexed, then you can try inserting them, your inclusion , And can turn them on again, so once each row has been added it will only have to be replaced once again.)