Sql-Server

如何以有效的方式在 sql server 表中插入/更新數百萬行？

  • October 28, 2015

通常在我的工作中，我必須在 SQL Server 中創建一個處理數百萬數據行的過程，將它們保存到臨時表（臨時表）中，最後將它們保存到數據庫中的表中。

我不是在尋找替代解決方案，例如 SSIS。

我無法禁用索引、約束、使數據庫離線、更改恢復模式等。

我們一直在尋找設置此過程以在系統不太繁忙時執行，但我們在 24/7/365 線上零售商環境中工作。

有一個非常相似的問題： Performance Inserting and Updating Millions of rows into a table

這個問題也很相關： 插入大量行的最快方法是什麼？

範例一：

CREATE PROCEDURE [dbo].[udpstaging_page_import_fromFilter]
     @sourceDesc nvarchar(50) -- e.g. 'Coremetrics'
     ,@feedDesc nvarchar(50) -- e.g. 'Daily Exports'
     ,@process_job_task_logID bigint
AS BEGIN


     SET NOCOUNT ON;
     BEGIN TRY


           --truncate table prior INSERT
           exec dbo.udpstaging_page_truncateTable;

           declare @source_feedID int;
           exec crm_admin.dbo.udpsource_feedID_select @sourceDesc
                 ,@feedDesc
                 ,@source_feedID = @source_feedID OUTPUT;


           -- drop temp tables
           if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#pageImport'))
                 drop table #pageImport;


           -- create temp tables
           create table #pageImport(
                 pageImportID [bigint] identity(1,1) NOT NULL
                 ,pageCode [varbinary](16) NOT NULL
           );


           insert into #pageImport(
                 pageCode
           )
           select pageCode
           from Coremetrics.PageView
           group by pageCode;


           -- add indexes to temp table
           CREATE CLUSTERED INDEX IDX_pageImport_pageImportID ON #pageImport(pageImportID);
           CREATE INDEX IDX_pageImport_pageCode ON #pageImport(pageCode);


           declare @recordCount bigint
                 ,@counter int
                 ,@maxCounter int
                 ,@updateRowCount int;

           select @counter = MIN(pageImportID)
                 ,@recordCount = MAX(pageImportID)
           from #pageImport;

           set @updateRowCount = 1000000;

           while @counter <= @recordCount
           begin

                 set @maxCounter = (@counter + @updateRowCount - 1);

                 with pageImport as (
                       select pv.pageCode
                             ,pv.websiteCode as 'pageCIV'
                             ,dbo.udfDerivePageName(pv.PAGE_ID, pv.CONTENT_CATEGORY_ID) as 'pageName'
                             ,dbo.udfDerivePageName(pv.PAGE_ID, pv.CONTENT_CATEGORY_ID) as 'pageDesc'
                             ,pv.[TIMESTAMP] as 'pageCreateDate'
                             ,pv.pageTypeCode
                             ,'' as 'pageTypeCIV'
                             ,pv.websiteCode
                             ,pv.marketID
                             ,@source_feedID as 'source_feedID'
                             ,@process_job_task_logID as 'process_job_task_logID'
                             ,GETDATE() as 'createdDate'
                             ,SUSER_NAME() as 'createdBy'
                             ,GETDATE() as 'modifiedDate'
                             ,SUSER_NAME() as 'modifiedBy'
                             ,ROW_NUMBER() over (
                                   PARTITION BY [pi].pageCode
                                   ORDER BY pv.[TIMESTAMP]
                             ) as 'is_masterPageImport'
                       from #pageImport [pi]
                       inner join Coremetrics.PageView pv on pv.pageCode = [pi].pageCode
                             and [pi].pageImportID between @counter and @maxCounter
                 )                 
                 insert into staging.[page](
                       pageCode
                       ,pageCIV
                       ,pageName
                       ,pageDesc
                       ,pageCreateDate
                       ,pageTypeCode
                       ,pageTypeCIV
                       ,websiteCode
                       ,marketID
                       ,source_feedID
                       ,process_job_task_logID
                       ,createdDate
                       ,createdBy
                       ,modifiedDate
                       ,modifiedBy
                 )
                 select pageCode
                       ,pageCIV
                       ,pageName
                       ,pageDesc
                       ,pageCreateDate
                       ,pageTypeCode
                       ,pageTypeCIV
                       ,websiteCode
                       ,marketID
                       ,source_feedID
                       ,process_job_task_logID
                       ,createdDate
                       ,createdBy
                       ,modifiedDate
                       ,modifiedBy 
                 from pageImport
                 where 1 = 1
                       and is_masterPageImport = 1;

                 set @counter = @counter + @updateRowCount;
           end;


           SET NOCOUNT OFF;
           RETURN 0;

     END TRY
     BEGIN CATCH
           print N'inner catch: ' + error_message();
           SET NOCOUNT OFF;
           RETURN -10;
     END CATCH

END;

範例二：

這只是儲存過程的一部分，太大而無法在此處發布。

IF OBJECT_ID('tempdb.dbo.#ztblOrgProductStockView', 'U') IS NOT NULL
   DROP TABLE #ztblOrgProductStockView;

CREATE TABLE #ztblOrgProductStockView (
   [lngID] [int] NOT NULL IDENTITY PRIMARY KEY,
   [sintMarketId] [smallint] NOT NULL,
   [sintChannelId] [smallint] NOT NULL,
   [strOrgVwName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
   [tintSequence] [tinyint] NOT NULL,
   [tintOrgGrpId] [tinyint] NOT NULL,
   [strTier1] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
   [strTier2] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
   [strTier3] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
   [strTier4] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
   [strTier5] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
   [strTier6] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
   [strItemNo] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
   [strStockTypeName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
   [tintStockTypeId] [tinyint] NOT NULL,
   [sintDueWithinDays] [tinyint] NOT NULL,
   [bitOverSellingAllowed] [bit] NOT NULL,
   [dtmStartDate] [datetime] NULL,
   [dtmEndDate] [datetime] NULL,
   [dtmExpected] [datetime] NULL,
   [blnIsLocalToMarket] [bit] NULL,
   [blnPremiunDelvAllowed] [bit] NULL,
   [strStdDeliveryDaysCode] [varchar](20)
)



INSERT into #ztblOrgProductStockView (
   sintMarketId
   ,sintChannelId
   ,strOrgVwName
   ,tintSequence
   ,tintOrgGrpId
   ,strTier1
   ,strTier2
   ,strTier3
   ,strTier4
   ,strTier5
   ,strTier6
   ,strItemNo
   ,strStockTypeName
   ,tintStockTypeId
   ,sintDueWithinDays
   ,bitOverSellingAllowed
   ,dtmStartDate
   ,dtmEndDate
   ,dtmExpected
   ,blnIsLocalToMarket
   ,blnPremiunDelvAllowed
   ,strStdDeliveryDaysCode
)
   select
       rv.sintMarketId
       ,rv.sintChannelId
       ,rv.strOrgVwName
       ,tintSequence
       ,tintOrgGrpId
       ,ISNULL(rv.pnTier1,'ÿ')
       ,ISNULL(rv.pnTier2,'ÿ')
       ,ISNULL(rv.pnTier3,'ÿ')
       ,ISNULL(rv.strTier4,'ÿ')
       ,ISNULL(rv.strTier5,'ÿ')
       ,ISNULL(rv.strTier6,'ÿ')
       ,rv.strItemNo
       ,strStockTypeName
       ,tintStockTypeId
       ,sintDueWithinDays
       ,bitOverSellingAllowed
       ,dtmStartDate
       ,dtmEndDate
       ,dtmExpected
       ,blnIsLocalToMarket
       ,blnPremiunDelvAllowed
       ,strStdDeliveryDaysCode
   from #ztblOrgProductRangeView_1 rv
   inner join #ztblOrgProductSeqView_1 sv on rv.strItemNo = sv.strItemNo
       and rv.lngOrgVwId = sv.lngOrgVwId
   --order by rv.sintMarketId, rv.sintChannelId, sv.tintOrgGrpId, rv.strItemNo, sv.tintStockTypeId

--set @DebugDate = convert(nvarchar(10),getdate(),108)
--raiserror('%s [%s]', 0, 1, N'Populated #ztblOrgProductStockView', @DebugDate) with nowait

--select [sintMarketId], [sintChannelId], [tintOrgGrpId], [strItemNo], [tintStockTypeId], count(*)
--from [#ztblOrgProductStockView]
--group by [sintMarketId], [sintChannelId], [tintOrgGrpId], [strItemNo], [tintStockTypeId]
--having count(*) > 1

   set @lngRowcount = @@ROWCOUNT
   set nocount on;

       While @lngRowcount > 0
       Begin
           Set @lngMinID = @lngMaxID
           Set @lngMaxID = @lngMaxID + 5000

           INSERT INTO [ztblOrgProductStockView]
                  ([sintActiveView]    
                  ,[sintMarketId]
                  ,[sintChannelId]
                  ,[strOrgVwName]  
                  ,[tintSequence]
                  ,[tintOrgGrpId]
                  ,[strTier1]
                  ,[strTier2]
                  ,[strTier3]
                  ,[strTier4]
                  ,[strTier5]
                  ,[strTier6]
                  ,[strItemNo]
                  ,[strStockTypeName]
                  ,[tintStockTypeId]
                  ,[sintDueWithinDays]
                  ,[bitOverSellingAllowed]
                  ,[dtmStartDate]
                  ,[dtmEndDate]
                  ,[dtmExpected]
                  ,[blnIsLocalToMarket]
                  ,[blnPremiunDelvAllowed]
                  ,[strStdDeliveryDaysCode])
           Select
                   @sintActiveView_new
                  ,[sintMarketId]
                  ,[sintChannelId]
                  ,[strOrgVwName]
                  ,[tintSequence]
                  ,[tintOrgGrpId]
                  ,[strTier1]
                  ,[strTier2]
                  ,[strTier3]
                  ,[strTier4]
                  ,[strTier5]
                  ,[strTier6]
                  ,[strItemNo]
                  ,[strStockTypeName]
                  ,[tintStockTypeId]
                  ,[sintDueWithinDays]
                  ,[bitOverSellingAllowed]
                  ,[dtmStartDate]
                   ,[dtmEndDate]
                  ,[dtmExpected]
                  ,[blnIsLocalToMarket]
                  ,[blnPremiunDelvAllowed]
                  ,[strStdDeliveryDaysCode]
           From #ztblOrgProductStockView
           Where lngID >= @lngMinID
           And   lngID <  @lngMaxID

           set @lngRowcount = @@ROWCOUNT

       End

問題 請注意，基於意見的答案在這裡不是最受歡迎的，盡可能提供證據。

**1）**如何確定組織批次大小的最佳方式？例如在範例 2 中為 5000。

**2)**如果BEGIN TRANSACTION我COMMIT TRANSACTION在while loop? 一批交易。

**3)**如果我想更改批次的大小，我可以監控什麼以確定是否可以增加批次的大小，或者我會導致 I/O 延遲？

我目前使用以下腳本找到 I/O 延遲：

-- How to identify I/O latency issues
-- Below SQL code helps in identifying the I/O latency issues in a SQL Server system on a per-file basis.
-- http://sqlserverdbknowledge.wordpress.com/2011/11/08/how-to-identify-io-latency-issues/
--http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

--MARCELO MIORELLI 26-JULY-2013

SELECT 
--- virtual file latency
ReadLatency = CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
WriteLatency = CASE WHEN num_of_writes = 0 
THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
Latency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
--– avg bytes per IOP
AvgBPerRead = CASE WHEN num_of_reads = 0 
THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
AvgBPerWrite = CASE WHEN io_stall_write_ms = 0 
THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
AvgBPerTransfer = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE ((num_of_bytes_read + num_of_bytes_written) / 
(num_of_reads + num_of_writes)) END, 
LEFT (mf.physical_name, 2) AS Drive,
DB_NAME (vfs.database_id) AS DB,
--- –vfs.*,
mf.physical_name
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
--WHERE vfs.file_id = 2 — log files
-- ORDER BY Latency DESC
-- ORDER BY ReadLatency DESC
ORDER BY WriteLatency DESC;
GO

基於意見的答案在這裡不是最受歡迎的，盡可能提供證據。

好吧，這並不完全公平，因為最終，最有效的“證據”將來自您的系統；-)。你的硬體、你的數據、你的系統負載等將決定什麼是最好的。如何處理事情以及系統如何工作都有很多變數，因此在一個系統中最有效的方法在另一個系統中可能不是那麼好。

**1）**如何確定組織批次大小的最佳方式？例如在範例 2 中為 5000。

這主要是一個反複試驗的問題，看看什麼最有效。但是，請務必記住，鎖升級通常發生在 5000 個鎖時。根據數據的組織方式，5000 個更改可能是 5000 個行鎖或幾個頁鎖。這是基於每個對象的，因為行的順序在不同的索引中可能不同。關鍵是，在這些操作期間對需要由其他程序使用的表所做的更改應盡量避免表鎖（即鎖升級的結果）。但是諸如臨時表和臨時表之類的表將從表鎖中受益，因為它是單個操作並且不應該存在爭用，因此TABLOCK在執行 “bulk” 時會有提示INSERT。

**2）**如果我在while循環中開始交易和送出交易，它通常會有更多的機會提高性能嗎？一批交易。

當這些操作是逐行的時，將多個 DML 操作包裝到一個顯式事務中可以極大地提高性能。從您在此處發布的程式碼中，您已經在進行基於集合的操作，因此將任何內容組合到事務中只會帶來很小的時間優勢。此外，在WHILE這兩個範例的循環中，您都在執行單個INSERT操作，這是它自己的事務，因此在WHILE循環內添加事務無論如何都不會獲得任何收益。而且，在WHILE循環周圍添加一個顯式事務會將整個集合放入一個事務中，這可能對時間安排有一點幫助，但是你也會有一個巨大的事務，這會增加阻塞的機會並有助於 LOG文件增長，因為此事務將處於活動狀態的時間更長。

**3)**如果我想更改批次的大小，我可以監控什麼以確定是否可以增加批次的大小，或者我會導致 I/O 延遲？

監控程序執行得更快或更慢。嘗試幾種不同的批次大小，並讓每個批次執行多次迭代。跟踪每個批次大小的過程執行多長時間，您會發現最有效的方法。

按照這些構想，我至少會嘗試減少範例 1 中的百萬行批量大小。

我還將標量 UDF轉換為內聯 TVF，並使用ordbo.udfDerivePageName將其合併到查詢（範例 1）中。而且，考慮到對 UDF 的兩次呼叫都傳入相同的兩個參數，您只需引用返回的欄位兩次（一次 as和一次 as ），而不是兩次呼叫 iTVF。CROSS APPLY``OUTER APPLY``pageName``pageDesc

減少目標表爭用的另一個選項（如果這只是插入新行而不更新現有行）是使用表分區。這將允許您像目前所做的那樣暫存數據，但是您不需要將新數據插入到活動表中，而是SWITCH使用新的分區，這是一個相當快速的操作。這對首先暫存數據所需的時間或 I/O 沒有幫助，但它可以消除將暫存數據“合併”到實時表中所花費的時間和爭用。這是需要調查的事情。

引用自：https://dba.stackexchange.com/questions/119447

相關問答

Sql-Server

將索引視圖上的計算欄位添加到 SQL Server 中的表後，如何測量 I/O 爭用？

  • October 15, 2015
檢視問答
Sql-Server

沒有數據更改的更新性能

  • February 23, 2022
檢視問答
Sql-Server

隱式轉換不影響性能

  • February 14, 2022
檢視問答
Sql-Server

重新排序表中的記錄

  • November 6, 2021
檢視問答
Sql-Server

插入性能不佳的表 Exec SP

  • August 30, 2021
檢視問答
Sql-Server

由於阻塞，無法檢索正在執行的查詢的查詢計劃

  • November 12, 2020
檢視問答