使用指数 INSERT 生成非常快速的测试数据

引言

本文（我的第一篇）将描述一种算法，该算法使用 SQL 查询能够非常快速地生成大量数据。测试数据可以是静态的，也可以是增量的，例如“项目名称”和“项目 ID”，如下所示

背景

我在一个项目中执行的任务之一是生成一个包含 103,680,000 条记录的测试表。使用传统的数据生成方法需要一个月的时间；因此，需要一种快速的数据插入方法。新的方法仅用了 5 个小时。

使用代码

传统方法 – 顺序 INSERT

使用循环和以下 INSERT 语句生成从 0…100000 的数字列表的传统方法是

CREATE TABLE #tempTable([Item ID] [bigint], [Item Name] nvarchar(30))
DECLARE @counter int
SET @counter = 1
WHILE (@counter < 100000)
BEGIN
        INSERT INTO #tempTable VALUES (@counter, 'Hammer')
        SET @counter = @counter + 1
END
SELECT * FROM #tempTable
DROP TABLE #tempTable

我们称这种数据生成方法为“顺序 INSERT”。

新方法 – 指数 INSERT

新方法有效地复制现有数据并将其附加为新数据，并重复此操作，直到生成所需数量的数据。

以下是指数 INSERT 的代码

CREATE TABLE #tempTable([Item ID] [bigint], [Item Name] nvarchar(30))
INSERT INTO #tempTable VALUES (1, 'Hammer')
WHILE((SELECT COUNT(*) FROM #tempTable) < 100000)
BEGIN
    INSERT INTO #tempTable ([Item ID], [Item Name])
        (SELECT [Item ID] + (SELECT COUNT(*) FROM #tempTable), 
                                 'Hammer' FROM #tempTable)
END
SELECT * FROM #tempTable
DROP TABLE #tempTable

关注点

WHILE..LOOP 的条件是 (SELECT COUNT(*))。评估此条件语句需要很长时间。更快速的方法是计算生成所需记录数所需的迭代次数，例如，在本例中为 100,000 条记录，即 2^17=131,072，因此我们可以重写代码以在第 17 次迭代后停止。

从 1 到 100,0000 的数字计数需要 4 秒；使用以下代码，指数方法需要 2 秒

CREATE TABLE #tempTable([Item ID] [bigint], [Item Name] nvarchar(30))
INSERT INTO #tempTable VALUES (1, 'Hammer')
DECLARE @counter int
SET @counter = 1
WHILE(@counter <= 17)
BEGIN
    INSERT INTO #tempTable ([Item ID], [Item Name])
        (SELECT [Item ID] + (SELECT COUNT(*) FROM #tempTable), 
                           'Hammer' FROM #tempTable)
    SET @counter = @counter + 1
END
SELECT * FROM #tempTable
DROP TABLE #tempTable

此外，您不仅可以使用它来递增数字字段，还可以将其应用于 datetime 字段。

历史

• 这是 v1.0。