关联查询效率 为什么SQL Server在填充表格后索引表的速度更快?




关联查询效率 (9)

在执行大数据操作操作之后,您经常需要更新基础索引。 你可以通过使用UPDATE STATISTICS [table]语句来做到这一点。

另一个选择是删除并重新创建索引,如果您正在执行大量数据插入,可能会更快地执行插入操作。 你甚至可以将它合并到你的存储过程中。

https://code.i-harness.com

我有一个存储过程,通过查询将750K记录放入临时表中,作为其第一个操作之一。 如果我在填充表之前在临时表上创建索引,那么与填充表之后索引相比,该项目的运行时间大约是运行时间的两倍。 (索引是单列中的一个整数,被索引的表只是两列,每一列都是一个整数)。

这对我来说似乎有点不对劲,但是我对引擎盖下面发生的事情并没有最深入的了解。 有没有人有这个答案?


如果你在表上有一个索引,当你将数据添加到表中时,SQL Server将不得不重新排序表,以便在新记录的适当位置腾出空间。 如果你添加了大量的数据,它将不得不一遍又一遍地重新排序。 只有在数据加载后才创建索引,重新排序只需要一次。

当然,如果你按照索引顺序导入记录,那应该不是那么重要。


如果创建聚簇索引,则会影响数据在磁盘上物理排序的方式。 最好在事实之后添加索引,并让数据库引擎在知道数据如何分发时重新排序行。

例如,假设您需要建造一个带有编号砖的砖墙,以便编号最高的砖墙位于墙的底部。 如果你只是随机地把砖头交给一个砖块,那么这将是一件困难的事情 - 你不知道哪块砖头会变成最高的编号,而且你不得不把墙壁撕下来并反复重建。 如果你把所有的砖排在你面前,并且可以组织你的工作,处理这个任务将会容易得多。

这就是数据库引擎的方式 - 如果让它知道整个工作,那么它可以比一次只填充一行更有效率。


如果您打算在之后大量加载,则永远不要在空表上创建索引。 当表上的数据发生变化时,索引必须保持不变,所以想象一下,表中的每一个插入索引都被重新计算(这是一个昂贵的操作)。 先加载表格,并在加载完成后创建索引。 这就是性能差异。


想想这样。

特定
unorderedList = {5,1,3}
orderedList = {1,3,5}

将2添加到这两个列表。
unorderedList = {5,1,3,2}
orderedList = {1,2,3,5}

你认为哪个列表更容易添加?

顺便说一下你的输入在加载之前会给你一个提升。


这是因为如果插入的数据不是按照索引的顺序,那么SQL将不得不拆分页面以腾出更多的行来将它们保持在一起


这是因为数据库服务器在每次插入新行时都必须进行计算。 基本上,你最终每次都重新绑定表格。 这看起来不是一个非常昂贵的操作,也不是,但是当你把它们合在一起的时候,你会开始看到影响。 这就是为什么你通常想要在你填充行之后进行索引的原因,因为这只是一次性成本。


这是由于SQL Server使用数据索引表时,能够生成索引列中值的精确统计。 在某些时刻,SQL Server将重新计算统计信息,但是当您执行大量插入操作时,上次计算统计信息后,值的分配可能会发生变化。

查询分析器可以发现统计信息已经过时的事实。 当你看到某个表的扫描行数与预期的实际行数不同时,

插入所有数据后,应该使用UPDATE STATISTICS重新计算值的分布。 之后,不应该观察到性能差异。


除了索引开销之外,出于同样的原因,将每个查询作为事务运行也是一个坏主意。 如果你在1个显式事务中运行插入块(比如说100),你也应该看到性能增加。







indexing