Sql-Server-2008-R2

如何加快循環的 INSERT INTO 語句?

  • November 12, 2018

我目前使用以下語句,對於 10,000 行,大約需要 150 秒。我嘗試刪除目標表上的索引,但這沒有幫助。在沒有 50 毫秒的情況下執行循環INSERT INTO。我需要它來更新大約 3 億行,我真的不能等待 52 天(!)才能完成。

以下更新查詢的底線是我需要遍歷每一行,對 a 執行計算VARBINARY並從中提取適當的值(我們需要擺脫打包的VARBINARY欄位),並將其儲存在一個新表中。

FETCH NEXT FROM LocCities INTO @LocCity 
WHILE (@@FETCH_STATUS = 0)
BEGIN
   -- several sets, removed calculations for clarity
   SET @LocationId = Calculation1()
   SET @CityId = Calculation2()

   IF(@LocCity <> 0)
   BEGIN
       -- left out an inner loop here on the VARBINARY based on its length
       INSERT INTO LocationCities (LocationId, CityId)
       VALUES (@LocationId, @CityId)
   END
   FETCH NEXT FROM RespCursor INTO @TuningRow
END

我知道我可以將WITH關鍵字與table hints一起使用,但我不確定該使用什麼。我預計最終更新查詢將在幾個小時內執行,並希望有辦法做到這一點。我真的等不及將近兩個月了;)。

沒有類似的東西BULKINSERT我可以使用嗎?

我真的不認為表提示或 BULKINSERT 會幫助你 - 你的方法仍然是一次處理每個 varbinary 值,無論如何這將是你的失敗 - 特別是當你放棄基於集合的查詢的想法時因為你“不認為這是可能的”。

這是一種基於集合的方法,沒有糟糕的循環或游標。這假設模式始終相同(LocationID 是第一個字節,CityID 是接下來的兩個)。

DECLARE @x TABLE(x VARBINARY(32));

INSERT @x VALUES(0x010734),(0x030735040736),(0x030742050743060712);

;WITH n(n) AS 
(
 SELECT TOP (300) (number*3)+1 
 FROM [master].dbo.spt_values -- your own Numbers table is better
 WHERE [type] = N'P' ORDER BY number
)
-- INSERT dbo.LocationCities(LocationId, CityId)
SELECT 
 x.x,      -- comment this out before insert 
 LocationID = CONVERT(INT, SUBSTRING(x.x, n, 1)),
 CityID     = CONVERT(INT, SUBSTRING(x.x, n+1, 2))
FROM @x AS x INNER JOIN n ON LEN(x) > n.n;

結果:

x                        LocationID    CityID
---------------------    ----------    ------
0x010734                 1             1844
0x030735040736           3             1845
0x030735040736           4             1846
0x030742050743060712     3             1858
0x030742050743060712     5             1859
0x030742050743060712     6             1810

一些文章將幫助您理解數字表以及為什麼 SQL Server 中的生成集遠優於您可以派生的最有效的循環。

引用自:https://dba.stackexchange.com/questions/64553