在SQL Server中插入10万条记录的最快方法

Question

我正在使用以下脚本将100,000条记录插入表中。基本上插入了从500,001到600,000的整数。我将整数转换为字符串并插入，因为这是我想要在表中的方式（一个以字符串形式表示的整数）。我使用合并来检查记录是否已经存在。

DECLARE @first AS INT
SET @first = 500001
DECLARE @step AS INT
SET @step = 1
DECLARE @last AS INT
SET @last = 600000

BEGIN TRANSACTION
WHILE(@first <= @last)
BEGIN
MERGE dbo.Identifiers As target
USING (SELECT CAST(@first as varchar(10)) AS Identifier) AS source 
    ON (source.Identifier = target.Identifier)
WHEN NOT MATCHED THEN
INSERT (Identifier) 
VALUES (source.Identifier);
SET @first += @step
END
COMMIT TRANSACTION

加载需要超过2分钟的时间。我做错了什么，但是无法找出原因。 注：该表在标识列上有唯一的非聚集索引。

- Vinoth

---

"加载需要超过2分钟时间" - 你认为这太久了吗？我们不知道你的硬件设置。 - Mitch Wheat

这是我们应用程序加载过程的一部分。我了解到有关百万条记录的闪电般快速插入，并且这是10万条记录。我认为我做错了什么，所以想澄清一下。我的系统配置是i7处理器，8 GB内存和64位操作系统。 - Vinoth

Answer 1

尝试使用这个方法。它使用了一个计数表。参考：http://www.sqlservercentral.com/articles/T-SQL/62867/

create table #temp_table(
    N int
)

declare @first as int
set @first = 500001
declare @step as int
set @step = 1
declare @last as int
set @last = 600000

with 
    e1 as(select 1 as N union all select 1),  --2 rows
    e2 as(select 1 as N from e1 as a, e1 as b), --4 rows
    e3 as(select 1 as N from e2 as a, e2 as b), --16 rows
    e4 as(select 1 as N from e3 as a, e3 as b), --256 rows
    e5 as(select 1 as N from e4 as a, e4 as b), --65,356 rows
    e6 as(select 1 as N from e5 as a, e1 as b), -- 131,072 rows
    tally as (select 500000 + (row_number() over(order by N) * @step) as N from e6) -- change 500000 with desired start
insert into #temp_table
select  cast(N as varchar(10))
from tally t
where
    N >= @first
    and N <=@last
    and not exists(
        select 1 from #temp_table where N = t.N
    )

drop table #temp_table

- Felix Pamittan

---

太棒了！加载整个10万条记录只用了一秒钟！！！非常感谢。 - Vinoth

尽管该解决方案第一次运行得很好，但当我第二次运行脚本时，由于这是一个合并操作，它花费了超过4分钟的时间（我不知道需要多长时间，因为我在第4分钟停止了）。 - Vinoth

Answer 2

我在想你的循环和使用 MERGE （而不是简单的 INSERT ）对性能问题有多大影响。我会选择严格基于集合的解决方案，如下：

INSERT INTO dbo.Identifiers (Identifier)
SELECT n FROM dbo.GetNums(500001, 600000)
WHERE n NOT IN (SELECT Identifier FROM dbo.Identifiers);

现在，这依赖于一个用户定义的表值函数dbo.GetNums，它返回一个包含500,001到600,000之间所有数字的列n的表。你如何编写这个函数？你需要在其中实时生成一系列数字。

以下实现摘自Itzik Ben-Gak的书《Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions》。

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
    RETURN
    WITH L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
         L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), 
         L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
         L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
         L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
         L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
         Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;

（由于这是一本关于SQL Server 2012的书，可能无法在SQL Server 2008上直接使用，但应该可以进行适应。）

- stakx - no longer contributing

Answer 3

Vinoth，以下内容也可以帮助您。

Declare @tab table (id int identity(1,1),num int)
Insert  into @tab (num) Values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)

Declare @start as int
set @start = 500000 

Insert into dbo.Identifiers (Identifier) 
Select  @start + ((E.id-1)*10000) +((D.id-1)*1000) +((C.id-1)*100) + ((B.id-1) * 10) + A.id 
from    @tab A,@tab B,@tab C,@tab D,@tab E
Order by @start + ((E.id-1)*10000) +((D.id-1)*1000) +((C.id-1)*100) + ((B.id-1) * 10) + A.id 

在我的数据库中，dbo.Identifiers 是一张没有索引的表格。插入只花费了 230 毫秒。

- Jithin Shaji

Answer 4

在标识符列上创建索引，然后尝试以上插入操作。

- Yogesh86

---

那么创建索引的问题是什么呢？ - Yogesh86

我的意思是，该表已经创建并包含索引。 - Vinoth

由于标识符列上没有索引，您查询时会进行表扫描，从而被卡住。因此，如果这是一个常规的真实查询，您可以为该列创建索引。 - Yogesh86

请在评论之前阅读问题，我已经注明标识符列上有一个唯一的非聚集索引。 - Vinoth