sql server程序优化 [英] sql server procedure optimization

问题描述

SQL Server 2005:

Option: 1

    CREATE TABLE #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)

    CREATE UNIQUE CLUSTERED INDEX Idx1 ON #test(customerid)
    CREATE INDEX Idx2 ON #test(field1 DESC)
    CREATE INDEX Idx3 ON #test(field2 DESC)
    CREATE INDEX Idx4 ON #test(field3 DESC)

    INSERT INTO #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)
    SELECT
      customerid, orderdate, field1, field2, field3 FROM 
    ATABLERETURNING4000000ROWS

对比

Option: 2

    CREATE TABLE #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)

    INSERT INTO #test
      (customerid, orderdate, field1 INT, field2 INT, field3 INT)
    SELECT
      customerid, orderdate, field1, field2, field3 FROM 
    ATABLERETURNING4000000ROWS

    CREATE UNIQUE CLUSTERED INDEX Idx1 ON #test(customerid)
    CREATE INDEX Idx2 ON #test(field1 DESC)
    CREATE INDEX Idx3 ON #test(field2 DESC)
    CREATE INDEX Idx4 ON #test(field3 DESC)

当我们使用第二个选项时，它的运行速度提高了近 50%.这是为什么?

When we use the second option it runs close to 50% faster. Why is this?

推荐答案

来自 SQL Server 查询处理团队:

为了构建索引的 b 树，我们必须首先对源中的数据进行排序.流程是扫描源，对其进行排序(如果可能 - 在内存中*)，然后根据排序构建 b 树.
为什么我们在构建b-tree之前需要先排序?理论上我们不必排序，我们可以使用常规 DML 并直接将数据插入内置索引(无排序)，但在这种情况下，我们将进行随机插入，随机插入 b-tree 需要首先在 b 树中搜索正确的叶节点，然后插入数据.虽然搜索 b 树相当快，但在每次插入之前这样做远非最佳.

In order to build the b-tree for the index we have to first sort the data from source. The flow is to scan the source, sort it (if possible - in memory*), then build the b-tree from the sort.
Why do we need to sort first before building the b-tree? In theory we don’t have to sort, we could use regular DML and directly insert data into the in-build index (no sort), but in this case we would be doing random inserts, random inserts in a b-tree require searching the b-tree for the correct leaf node first and then inserting the data. And while searching a b-tree is fairly fast, doing so before each insert is far from optimal.

您的索引是 B+ 树.

第一个查询需要在 B+ 树中查找每条记录，然后修改 B+ 树.

The first query requires lookups in B+ trees for each record and then modifying the B+ trees.

第二个查询将根据特定索引依次对每个索引所需的数据进行排序，B+树为构建非常高效.

The second query will sort the data reuired for each index in turn according to the particular index and the B+ trees are constructed very efficiently.

这篇关于sql server程序优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！