为什么使用参数化查询将数据插入表中比将值附加到查询字符串更快？ [英] Why is using a parameterized query to insert data into a table faster than appending the values to the query string?

问题描述

为什么使用参数化查询将数据插入到表中：

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 + ")";

？

在一个循环中插入10K行，参数化查询比另一个更快一个数量级。

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?

推荐答案

，执行SQL查询的最昂贵的部分是构建执行计划 - 标识需要哪些表，确定要使用的最佳索引（如果有）等。您可以将其视为编译查询if

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.

当你追加这些值时，你是硬编码他们进入查询，所以它必须每次重新准备，并且您承担为每次迭代构建新的执行计划的成本。再次使用编译隐喻，这就像一个C程序，其所有的配置硬编码 - 更改一个设置，你必须重新编译整个事情。

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

这篇关于为什么使用参数化查询将数据插入表中比将值附加到查询字符串更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！