SQL Server 2008 R2 – Query Plan Estimated Row Count Very High

execution-planperformancequery-performancesql-server-2008-r2

I have a query that populates a temp table with 1692 rows. I had used a table variable but performance was terrible. Now it is improved but still not ideal.

However, I then need to split the TaskName values into separate columns. For this I use APPLY (maybe there is a better way to do this?)

The Execution Plan returns 'Actual Number of Rows' value of over 100,000. Any idea why this is? There are only 1692 rows in the table!

Query plan is below.

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

enter image description here

UPDATE: Tried below CASE query from @Biju jose but does not give desired output.

Output Required:

enter image description here

Output Returned:

enter image description here

Best Answer

Here is an alternative using CASE statement, Please test the code before run

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

Also, the estimated number is high because you are joining to the same table for 9 times which bloats the number of rows.

Related Question