如何通過擺脫游標來減少儲存過程的執行時間？

我的任務是減少執行數小時的儲存過程的執行時間。

任務描述：游標從查詢中獲取一組值@Primary 和@Secondary（5000+ 行），通過@Primary 的值找到@isNewProgram 和@NewProgram，然後操作另一個內置游標，其中的值將 5000 的每一行與某些條件進行比較並最終插入到 @tempTable 中。但是使用游標一次對每一行進行單獨的非並行處理會消耗大量時間。

我已經優化了從索引到使用公用表表達式的所有內容，但是我嘗試了多少，我什至無法開始刪除游標並用基於集合的方法替換它。

SET @CUR_PSD = CURSOR FOR
SELECT DISTINCT B.Primary,Secondary 
FROM DetailsTable A 
Inner join GroupTable B on B.Primary = A.Primary
WHERE CurrentMonth  = @CurrentMonth AND (LEN(Primary) <> 6)

SET @Cur_Billable = CURSOR FOR
SELECT DISTINCT Liability FROM @Liability

OPEN @CUR_PSD
   
FETCH NEXT FROM @CUR_PSD INTO @Primary,@Secondary
               
IF @@FETCH_STATUS <> 0 
   Print 'Error in the Cursor Fetch Statement'
           
WHILE @@FETCH_STATUS = 0    

BEGIN
   SELECT @IsNewProgram = (SELECT Distinct IsNewProgram 
                   FROM GroupTable WHERE Primary = @Primary)
   SELECT @NewProgram = (SELECT Distinct NewProgram
                   FROM GroupTable WHERE Primary = @Primary)
   
       
   OPEN @Cur_Billable
   
   FETCH NEXT FROM @Cur_Billable INTO @VarLiability
                   
   IF @@FETCH_STATUS <> 0 
       Print 'Error in the Cursor Fetch Statement'
               
   WHILE @@FETCH_STATUS = 0    
   
   BEGIN
   
   INSERT INTO @TempTable
   SELECT top 10 Secondary, Primary, GroupName,Liability, DefectCount, DefectCode
       FROM (
               SELECT  Secondary
                       ,@Primary AS Primary
                       ,GroupName
                       ,Liability
                       ,SUM(DefectCount) AS DefectCount,   
                       DefectCode
               FROM @PDTopTable
               WHERE Liability = @VarLiability                         
               
               and TPPrimary IN (SELECT Primary FROM GroupTable C 
                           WHERE C.Primary = @Primary)
               AND  TPNewProgram = CASE  WHEN @IsNewProgram = 0 
                                               Then  @NewProgram           
                                   WHEN @IsNewProgram = 2 
                                               Then TPNewProgram
                               ELSE  @NewProgram  END  
               AND TPSecondary = @Secondary            
               GROUP BY Secondary, GroupName, DefectCode,Liability     
       UNION
               SELECT  Secondary
                       ,@Primary AS Primary
                       ,GroupName
                       ,Liability
                       ,SUM(DefectCount) AS DefectCount,   
                       DefectCode
               FROM @PDBotTable
               WHERE Liability = @VarLiability                         
               
               and TPPrimary IN (SELECT Primary FROM GroupTable C 
                           WHERE C.Primary = @Primary)
               AND  TPNewProgram = CASE  WHEN @IsNewProgram = 0 
                                           Then  @NewProgram           
                                   WHEN @IsNewProgram = 2 
                                           Then TPNewProgram
                               ELSE  @NewProgram  END  
               AND TPSecondary = @Secondary            
               GROUP BY Secondary, GroupName, DefectCode,Liability
       ) TDG
       ORDER BY  TDG.Primary, TDG.Secondary,TDG.DefectCount DESC
   
       FETCH NEXT FROM @Cur_Billable INTO @VarLiability
   
       END 

       CLOSE @Cur_Billable
   
   FETCH NEXT FROM @CUR_PSD INTO @Primary,@Secondary
   
END 

CLOSE @CUR_PSD

我不是在找人為我進行轉換，我只需要知道如何開始。我找到了很多教程，但我需要正確的指導才能開始，因為我以前從未這樣做過。

只是為了讓人們知道外部游標在 5000 行或更多行上執行，並且外部游標的條件檢查是導致程式碼執行如此緩慢的原因。我是否應該在其上使用內部連接以便獲得相同的所需輸出……？

我的意思是我想知道我應該怎麼做

With TQ_PlantSupplierDefects(PlantCode,SupplierCode) AS
(
SELECT DISTINCT B.PrimaryCode as PlantCode,SupplierCode 
   FROM [SMS].[T_SMS_PlantDetailDefects] A 
   Inner join SMS.T_SMS_PlantGroup B on B.PlantCode = A.PlantCode
   WHERE RollingYearMonth  = @RollingYearMonth AND (LEN(PrimaryCode) <> 6)
)

如果我要對上述 CTE 和 @PDTopTable 進行操作，我應該使用什麼。我的直覺說我應該使用函式和連接的組合。我對嗎？

您想提高游標的速度嗎？將它們包裝在事務中。如果您正在處理數百萬條記錄並且不希望/不需要它們全部在一個事務中，您可以偶爾送出它以減少資源。

我用一個需要一個小時才能執行的游標（這是一個極端情況）來完成這個，然後它在 1 1/2 分鐘內執行。

我知道這不能回答您的問題，但它可能會幫助您避免進行轉換，直到您更熟悉 SQL。你的問題的答案是……經驗。沒有可以閱讀的神奇網站或書籍，只是需要時間學習在一個語句中做更多的事情。

如果我遵循正確，程式碼的一般目的是：

對於每個有效的主要、次要和責任，將最多 10 條記錄插入@TempTable（基於 IsNewProgram 和 NewProgram 的值）。

GroupTable第一：如果每個主節點中可以有多個記錄，那麼您的@IsNewProgram和@NewProgram值可以是該主節點行的任何可能值。如果這是意圖，您可以將它們設置在外部游標（GROUP BY Primary, Secondary，並將 IsNewProgram 和 NewProgram 包括為 MAX 或 MIN，以確保有效值）。這樣就無需為循環中的每個 Primary 和 Secondary 進行設置。

GroupTable第二：除非在執行查詢期間可以刪除主節點，否則可能不需要檢查以下內容：

         and TPMPrimary IN (SELECT Primary FROM GroupTable C 
                       WHERE C.Primary = @Primary)

如果我們可以通過一個查詢成功地提取所有內容，那麼這絕對不需要。但是，如果我們使用多個查詢，它可能仍然值得確認。如果其他更改無法提供足夠的性能提升，請嘗試檢查一下，看看它是否真的有所作為。

最後：您也許可以使用ROW_NUMBER(). 您將保持 TDG 子查詢基本上保持原樣（以上述步驟為模，並擴展為一次包括所有主要、次要和負債）。對於外部查詢，TOP 10您將添加一個新列，而不是 ：

   ,ROW_NUMBER() OVER (PARTITION BY Primary, Secondary, Liability ORDER BY DefectCount DESC) as row_no

接下來，將此 SQL 語句變成另一個 SQL 語句的子查詢。在新的外部 SELECT 中，獲取除 之外的所有列row_no，並使用以下WHERE子句：

WHERE row_no <= 10

從根本上說，您需要確定是什麼迫使您使用游標（向上拉IsNewProgram並NewProgram在循環內；並且，需要每個主要、次要和責任的前十個結果），並尋找讓您在其中完成相同事情的選項一種基於集合的方式。

注意：未經過嚴格測試，因為您要求的指針多於特定解決方案。

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