sql - 如果经过 60 秒，则在 While 循环中跳过批处理

Question

我正在尝试使用 WHILE 循环遍历批次以将客户引用与数据表匹配。我生成的 SQL 查询得到了预期的结果，所以我希望在更长的时间内运行它——然后是 while 循环和批处理。

这些批次大约需要 2 秒才能将另外 10,000 行插入表中，我遇到的问题意味着对于特定批次，该过程会继续运行，但不会发生插入。我已经确定这似乎始终归结为批次中没有插入的特定 Call_ID，但是当针对有问题的记录运行时，该记录将插入。

我正在寻找一种解决方案，该解决方案将允许该过程以 10,000 条记录的批次运行 INSERT INTO，但如果需要超过 10 秒，则会跳过一个批次。我试图设置 LOCK_TIMEOUT = 60000 但这似乎并没有改变任何暗示问题与锁定无关的东西。

然后，我计划为跳过的批次运行该过程，但增量要小得多，这样我就可以识别导致问题的记录。

仅供参考，我正在处理大约 400 万条记录。

提前致谢！

CREATE PROCEDURE dbo.USR_FN_CRM_AGREEMENTS_BATCHES
AS

DECLARE @BatchStart  INT;
DECLARE @ResultCount INT;
DECLARE @Increment   INT;
DECLARE @BatchStartTime DATETIME;

SET @Increment = 10000;
SET @BatchStart = 2858110;
SET @ResultCount = 1;
SET @BatchStartTime = GETDATE()

WHILE(@ResultCount > 0)
    BEGIN

    SET LOCK_TIMEOUT 60000;


        WITH AccountReferences
             AS (SELECT ra.ID AS AccountReference, 
                        ra.MainAsset, 
                        cgct.Contact_ID, 
                        cgct.Group_Contact_ID, 
                        ra.AccountStartDate, 
                        ra.AccountEndDate
                 FROM SQLActiveH_Reporting.dbo.Contact_Group_Contacts_T cgct
                      LEFT JOIN SQLActiveH_Reporting.dbo.Rent_Accounts ra ON cgct.Group_ID = ra.ContactDatabaseReference
                ),
             CRM_Calls
             AS (SELECT cct.Call_ID, 
                        cct.Call_Made_By_Group_Contact_ID, 
                        cct.Call_Relates_To_Group_Contact_ID, 
                        cct.Call_Relates_To_Asset_ID AS Asset_ID, 
                        cct.Entered_Actual_DT AS Call_Date_Time
                 FROM SQLActiveH_Reporting.dbo.CRM_Calls_T cct
                 WHERE cct.Call_ID > @BatchStart
                       AND cct.Call_ID <= @BatchStart + @Increment
                       AND cct.Call_ID NOT IN (2873237)
             ),
             DataPrep
             AS (
             SELECT CASE WHEN AR1.AccountReference IS NOT NULL THEN AR1.AccountReference
                             WHEN AR2.AccountReference IS NOT NULL THEN AR2.AccountReference
                             WHEN AR3.AccountReference IS NOT NULL THEN AR3.AccountReference
                             WHEN AR4.AccountReference IS NOT NULL THEN AR4.AccountReference
                             WHEN AR5.AccountReference IS NOT NULL THEN AR5.AccountReference
                             WHEN AR6.AccountReference IS NOT NULL THEN AR6.AccountReference
                             WHEN AR7.AccountReference IS NOT NULL THEN AR7.AccountReference
                             WHEN AR8.AccountReference IS NOT NULL THEN AR8.AccountReference
                             WHEN AR9.AccountReference IS NOT NULL THEN AR9.AccountReference
                             ELSE NULL
                        END AS AccountReference, 
                        CRM.Call_ID, 
                        CRM.Call_Made_By_Group_Contact_ID, 
                        CRM.Call_Relates_To_Group_Contact_ID, 
                        CRM.Asset_ID, 
                        CRM.Call_Date_Time, 
                        ROW_NUMBER() OVER(PARTITION BY CRM.Call_ID ORDER BY CRM.Call_Date_Time) AS SEQ
                 FROM CRM_Calls CRM
                      LEFT JOIN AccountReferences AR1 ON CRM.Call_Made_By_Group_Contact_ID = AR1.Group_Contact_ID AND CRM.Asset_ID = AR1.MainAsset
                                                         AND CRM.Call_Date_Time BETWEEN AR1.AccountStartDate AND AR1.AccountEndDate
                      LEFT JOIN AccountReferences AR2 ON CRM.Call_Made_By_Group_Contact_ID = AR2.Group_Contact_ID
                                                         AND CRM.Call_Date_Time BETWEEN AR2.AccountStartDate AND AR2.AccountEndDate
                      LEFT JOIN AccountReferences AR3 ON CRM.Call_Relates_To_Group_Contact_ID = AR3.Group_Contact_ID
                                                         AND CRM.Asset_ID = AR3.MainAsset
                                                         AND CRM.Call_Date_Time BETWEEN AR3.AccountStartDate AND AR3.AccountEndDate
                      LEFT JOIN AccountReferences AR4 ON CRM.Call_Relates_To_Group_Contact_ID = AR4.Group_Contact_ID
                                                         AND CRM.Call_Date_Time BETWEEN AR4.AccountStartDate AND AR4.AccountEndDate
                      LEFT JOIN AccountReferences AR5 ON CRM.Call_Made_By_Group_Contact_ID = AR5.Group_Contact_ID
                                                         AND CRM.Asset_ID = AR5.MainAsset
                      LEFT JOIN AccountReferences AR6 ON CRM.Call_Made_By_Group_Contact_ID = AR6.Group_Contact_ID
                      LEFT JOIN AccountReferences AR7 ON CRM.Call_Relates_To_Group_Contact_ID = AR7.Group_Contact_ID
                                                         AND CRM.Asset_ID = AR7.MainAsset
                      LEFT JOIN AccountReferences AR8 ON CRM.Call_Relates_To_Group_Contact_ID = AR8.Group_Contact_ID
                      LEFT JOIN AccountReferences AR9 ON CRM.Asset_ID = AR9.MainAsset
                                                         AND CRM.Call_Date_Time BETWEEN AR9.AccountStartDate AND AR9.AccountEndDate
                       )
             INSERT INTO Admin_DBA.dbo.CRM_Accounts
                    SELECT dp.AccountReference, 
                           dp.Call_ID, 
                           dp.Call_Made_By_Group_Contact_ID, 
                           dp.Call_Relates_To_Group_Contact_ID, 
                           dp.Asset_ID, 
                           dp.Call_Date_Time,
                           @BatchStart
                    FROM DataPrep dp
                    WHERE Seq = 1
                 ;


        SET @BatchStart = @BatchStart + @Increment;
        SET @ResultCount = @@ROWCOUNT;

    END;

USE [Admin_DBA]
GO

/****** Object:  Table [dbo].[CRM_Accounts]    Script Date: 18/07/2019 
09:21:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CRM_Accounts](
    [AccountReference] [varchar](50) NULL,
    [Call_ID] [int] NOT NULL,
    [Call_Made_By_Group_Contact_ID] [int] NULL,
    [Call_Relates_To_Group_Contact_ID] [int] NULL,
    [Asset_ID] [int] NULL,
    [Call_Date_Time] [datetime] NOT NULL,
    [SEQ] [bigint] NULL
) ON [PRIMARY]
GO

标签： sqlsql-servertsql