为什么第一次执行查询需要两倍的时间？
我正在运行几个具有相对较大结果集（300K 行）的查询，并以链接方式将它们插入到表中：
我将数据批量插入到 Table0 中，然后从 Table0 中读取数据strong> 并批量插入到 Table1，从 Table1 读取插入 Table2 等。为了读取数据，我使用 ADO.NET 命令，该命令在后台触发 exec_sql 存储过程。
我经历过一个有趣的现象。当我重新创建数据库并在几个表上运行整个过程时，每个表大约需要 20 秒。但是，在该流程的每次后续执行中，查询只需要 10 秒即可执行。在插入数据之前，表总是被截断，因此数据量不会增长。
起初我认为这与缓存的执行计划有关，所以我在第二次运行后使用 DBCC FREEPROCCACHE 清除了缓存，第三次运行仍然是 10 秒。
第一次执行查询时是否会发生任何其他事情，并且可以更快地找出它们（即无需实际执行查询）？
I am running a couple of queries with relatively huge result sets (300K rows) and inserting them into tables in a chained fashion :
I bulk insert data into Table0, then read data from Table0 and bulk insert into Table1, read from Table1 insert into Table2 etc. For reading the data, I am using ADO.NET command which fires an exec_sql stored procedure in the background.
I have experienced an interesting phenomenon. When I recreate the database and run the whole process on a couple of tables, it takes approximatelly 20 seconds for each. However, on every subsequent execution of the process the queries only take 10 seconds to execute. Before inserting the data the tables are always truncated so the data amount doesn't grow.
At first I thought this has something to do with cached execution plans, so I cleared the cache using DBCC FREEPROCCACHE after the 2nd run and the 3rd run was still 10 seconds.
Are there any other things that happen when the query is executed first time and can they be figured out faster (i.e. without actually executing the query) ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我假设 SQL Server 对包括表和索引在内的各种内容都有缓存，因此在第二次执行时，它可能已经在内存中拥有大量所需的数据。
I'd assume that SQL Server has a caches for all kinds of stuff including tables and indexes, so on the second execution it probably has lots of the data needed already in memory.
第一次执行查询时，它会被解析、编译和优化。下次执行查询时，DBMS 将采用内部编译和优化的版本并执行它。这就是导致执行时间差异的原因。
有关此过程的更多信息，请阅读 这篇文章。
The first time you execute a query, it is being parsed, compiled and optimized. The next time the query is executed, the DBMS takes the internal compiled and optimized version and executes it. That is what causes the difference in execution times.
For more information about this process, please read this article.