sql-server – 高效插入具有聚簇索引的表

我有一个SQL语句,它将行插入到TRACKING_NUMBER列上具有聚簇索引的表中.

譬喻.：

INSERT INTO TABL_NAME (TRACKING_NUMBER,COLB,COLC) 
SELECT TRACKING_NUMBER,COL_B,COL_C 
FROM STAGING_TABLE

我的题目是 – 在SELECT语句中为聚积索引列行使ORDER BY子句是否有辅佐,可能ORDER BY子句所需的特殊排序是否可以打消任何得到的增益？

办理要领

这取决于打算中的聚簇索引运算符是否具有DMLRequestSort属性集(这又取决于插入的预计行数).

假如您发明SQL Server由于某种缘故起因低估了这一点,您也许会从SELECT查询中添加显式ORDER BY以最大限度地镌汰页面拆分并从INSERT操纵中发生碎片中获益

例：

use tempdb;

GO

CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))

CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))

GO

DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)

INSERT INTO @T(N)
SELECT number 
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499

/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1,@T T2

/*Same operation using explicit sort*/    
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1,@T T2
ORDER BY T1.N*1000 + T2.N


SELECT avg_fragmentation_in_percent,fragment_count,page_count,avg_page_space_used_in_percent,record_count
FROM   sys.dm_db_index_physical_stats(2,OBJECT_ID('T'),NULL,'DETAILED')
;  


SELECT avg_fragmentation_in_percent,OBJECT_ID('T2'),'DETAILED')
;

表白T是大局限碎片

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536             92535                92535                67.1668272794663               250000
99.5                         200                  200                  74.2868173956017               92535
0                            1                    1                    32.0978502594514               200

但对付T2碎片很少

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376                        262                  62500                99.456387447492                250000
2.1551724137931              232                  232                  43.2438349394613               62500
0                            1                    1                    37.2374598468001               232

相反,偶然您也许但愿在知道数据已经预先排序并但愿停止不须要的排序时逼迫SQL Server低估行数.一个值得留意的例子是行使newsequentialid聚簇索引键将大量行插入到表中. In versions of SQL Server prior to Denali SQL Server adds an unnecessary and potentially expensive sort operation.这可以通过以下方法停止

DECLARE @var INT =2147483647

INSERT INTO Foo
SELECT TOP (@var) *
FROM Bar

然后,SQL Server将预计将插入100行,而不管Bar的巨细是否低于将排序添加到打算的阈值.可是,正如下面的评述中所指出的那样,这确实意味着插件将无法操作起码的日记记录.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!