問題描述

我嘗試使用 WHILE 在我的表中插入一些虛擬數(shù)據(jù)，但它運行起來真的很慢.

I try to insert some dummy data inside my table using a WHILE, but it run really really slow.

我在想可能是我寫的代碼不正確，請您看一下并確認一下嗎?

I was thinking maybe I am writing not properly the code, could yo please have a look and confirm it?

-- Insert dummy data

DECLARE
    @i          int,
    @Content    int;
SET @i = 5001;

WHILE @i > 5000 AND @i < 10000
BEGIN
    SET @Content = ROUND(((10000-5000)*RAND()+5000),0)
    INSERT INTO dbo.CmsImagesContents
    (ContentId, Title, AltTag, Caption)
    VALUES
    (@Content,'Test Title', 'Test AltTag', 'Test Caption');
    SET @i = @i + 1;
END

推薦答案

與在循環(huán)中執(zhí)行 4999 條單獨的插入語句相比，如果對所有 4999 行執(zhí)行一次插入，您將獲得更好的性能.因此，如果您有一個包含 4999 行的表 #T，您只需調(diào)用以下代碼:

Rather than doing 4999 separate insert statements in a loop, you'll get much better performance if you do a single insert of all 4999 rows. So, if you have a table #T containing 4999 rows you would simply call the following:

INSERT INTO DBO.CmsImagesContents(ContentId, Title, AltTag, Caption)    
SELECT (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5000) + 5000 AS ContentID, 
        'Test Title' AS Title, 'Test AltTag' AS AltTag, 'Test Caption'  AS Caption
FROM #T1

如果您首先需要創(chuàng)建這樣一個包含 4999 行的表，那么以下 SQL 將適合您:

If you need to create such a table of 4999 rows in the first place then the following SQL would work for you:

CREATE TABLE #T1
(
    N INT NOT NULL PRIMARY key
);

WITH L0 AS (SELECT 1 AS N UNION ALL SELECT 1), 
    L1 AS (SELECT A.N FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT A.N FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT A.N FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT A.N FROM L3 AS A CROSS JOIN L3 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L4)

INSERT INTO #T1( N )
SELECT N
FROM Nums
WHERE n < 10000 AND n>5000;

這篇關(guān)于使用 WHILE 創(chuàng)建虛擬數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！