在SQL Server 2014 Developer中生成20亿行的有效方法 [英] Efficient way to generate 2 billion rows in SQL Server 2014 Developer

问题描述

长话短说；我正在测试一种通过网络连接从表中清除条目的系统，预计该功能最多可处理20亿个条目.

Long story short; I am testing a system to purge entries from a table over a network connection, and the functionality is predicted to handle over 2 billion entries at most.

我需要对此进行压力测试.

I need to stress test this to be certain.

这是我的测试脚本(最多可以在十分钟内生成980万.)

Here's my test script (At best it's able to generate 9.8 million in ten minutes.)

DECLARE @I INT=0

WHILE @I <2000000001
BEGIN
    INSERT INTO "Table here"
    VALUES(@I)  

    SET @I=@I+1
END

在这种情况下，任何人都可以提出任何建议或给我一个想法，我的测试环境的上限是多少?

Can anyone suggest anything, or give me an idea what the upper limits of my test environment might be in this situation?

推荐答案

下面是一种使用CROSS JOIN的方法，批量使用10M.这大约在6分钟内将20亿行加载到了我的台式机上.

Below is a method using CROSS JOIN in batches of 10M. This loaded 2 billion rows in about 6 minutes on my desktop machine.

SET NOCOUNT ON;
DECLARE
      @TargetRowCount int = 2000000000
    , @RowsInserted int = 0;

WHILE @RowsInserted < @TargetRowCount
BEGIN

    WITH 
         t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
        ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
        ,t10m AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a
        CROSS JOIN t1k AS b CROSS JOIN t10 AS c)
    INSERT INTO dbo."table here" WITH(TABLOCKX)
    SELECT num + @RowsInserted
    FROM t10m;

    SET @RowsInserted += @@ROWCOUNT;

    RAISERROR('%d of %d rows inserted', 0, 0, @RowsInserted, @TargetRowCount) WITH NOWAIT;

END;
GO

这篇关于在SQL Server 2014 Developer中生成20亿行的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！