查詢計劃 - 估計的行數非常高

我有一個用 1692 行填充臨時表的查詢。我使用了一個表變數，但性能很糟糕。現在有所改進，但仍不理想。

但是，然後我需要將TaskName值拆分為單獨的列。為此，我使用 APPLY （也許有更好的方法來做到這一點？）

執行計劃返回超過 100,000的“實際行數”值。知道這是為什麼嗎？表中只有1692行！

查詢計劃如下。

https://www.brentozar.com/pastetheplan/?id=rJ6_JkKfQ

**更新：**在@Biju jose 的 CASE 查詢下進行了嘗試，但沒有提供所需的輸出。

輸出要求：

返回輸出：

這是另一種 usingCASE語句，請在執行前測試程式碼

INSERT INTO #TmpClientOnboardingTable
  SELECT 
   f.FundCode
  ,f.FundName
  ,tr.Period
  ,td.TaskName
  ,td.TaskDetailID
  --,tr.LatestComment
  ,[Tasks].[OverallTaskStatus](tr.TaskDetailID,tr.TagID,f.FundID,tr.Period) as 'OverallStatus'
  ,[Tasks].[LatestComment](tr.TaskDetailID,f.FundID,tr.Period) as 'LatestComment'

  --,[TaskDetailID]
  --,[TagID]
  --,[StatusFlagID]
  --,[ApprovalLevelID]
  --,[ApprovedByID]
  --,[ApprovedDateTime]
  --,[LatestComment]
  --,[CommentDateTime]
  --,[CommentPostedByID]
  FROM [Tasks].[TaskRecord] tr
  INNER JOIN dbo.Fund f
  ON f.FundID = tr.FundID
  INNER JOIN [Tasks].[Tag] t
  ON t.TagID = tr.TagID
  INNER JOIN [Tasks].[TaskDetail] td
  ON td.TaskDetailID = tr.TaskDetailID
  INNER JOIN [dbo].[StatusFlag] s
  ON s.StatusFlagID = tr.StatusFlagID
  LEFT JOIN [dbo].[UserDetails] u
  ON u.UserDetailID = tr.ApprovedByID
  WHERE tr.Period = @Period 
  AND t.TagID = 4; -- Client Onboarding View -- ('Cash Reconciliation', 'Non Trade Related')
  --AND tr.ApprovalLevelID = 1 -- analyst
  --ORDER BY f.FundCode  

 --optional if its unique
 --CREATE CLUSTERED INDEX CL_idx ON  #TmpClientOnboardingTable (Fundcode) 

   SELECT DISTINCT
 c.FundCode
,c.FundName
,c.Period
,MAX(CASE WHEN TaskName= 'Client Risk Assessment' THEN TaskDetailID ELSE NULL END) AS 'ClientRiskAssessmentParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'Client Risk Assessment' THEN OverallStatus ELSE NULL END,'RED')) as 'ClientRiskAssessment'     
,MAX(CASE WHEN TaskName= 'AML/KYC' THEN TaskDetailID ELSE NULL END) AS 'AMLKYCParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'AML/KYC' THEN OverallStatus ELSE NULL END,'RED')) as 'AMLKYC'     
,MAX(CASE WHEN TaskName= 'Broker' THEN TaskDetailID ELSE NULL END) AS 'BrokerParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'Broker' THEN OverallStatus ELSE NULL END,'RED')) as 'Broker'     
,MAX(CASE WHEN TaskName= 'Checklist' THEN TaskDetailID ELSE NULL END) AS 'ChecklistParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'Checklist' THEN OverallStatus ELSE NULL END,'RED')) as 'Checklist'     
,MAX(CASE WHEN TaskName= 'Register of Assets' THEN TaskDetailID ELSE NULL END) AS 'RegisterOfAssetsParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'Register of Assets' THEN OverallStatus ELSE NULL END,'RED')) as 'RegisterOfAssets'          
,MAX(CASE WHEN TaskName= 'Expense Budget' THEN TaskDetailID ELSE NULL END) AS 'ExpenseBudgetParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'Expense Budget' THEN OverallStatus ELSE NULL END,'RED')) as 'ExpenseBudget'     
,MAX(CASE WHEN TaskName= 'Decisions' THEN TaskDetailID ELSE NULL END)  AS 'DecisionsParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'Decisions' THEN OverallStatus ELSE NULL END,'RED')) as 'Decisions'
,MAX(CASE WHEN TaskName= 'Onboarding Review' THEN TaskDetailID ELSE NULL END) AS 'OnboardingReviewParentID'
,MAX(COALESCE(CASE WHEN TaskName= 'Onboarding Review' THEN OverallStatus ELSE NULL END,'RED')) as 'OnboardingReview'     
FROM #TmpClientOnboardingTable c
GROUP BY c.FundCode
       ,c.FundName
       ,c.Period

此外，估計數字很高，因為您要加入同一個表 9 次，這會使行數膨脹。

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