0

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

  • can you case statement for this, also adding an index in the temporary table can benefit it. – Biju jose Jul 3 '18 at 12:15
  • Seems to me like actual number of rows and estimated number of rows are the same. While that is more rows than are in the table, if you self-join to a table multiple times, you multiply the number of rows accessed. This doesn't mean this is a performance problem, though. What is the actual problem you're trying to solve? – Aaron Bertrand Jul 3 '18 at 16:16
  • I am looking for a better more efficient way of querying this table to get the desired output. Tried the below CASE statement as suggested but output incorrect. But yes, I also did not know why it was getting so many rows. Now it makes sense. Thanks – Kevin Jul 4 '18 at 11:54
1

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.

  • This did not work unfortunately. Is there another solution? Thanks – Kevin Jul 10 '18 at 14:18
  • 1
    @Kevin, can you try max on those columns, I have updated the answer, also can you provide us with some sample data and required output based on that I can enhance my answer. – Biju jose Jul 11 '18 at 4:37
  • that worked. Thank you. However, while performance has improved it is still taking on average 15secs to run – Kevin Aug 2 '18 at 10:05
  • @Kevin, can you add the new plan which is generated based on my query. – Biju jose Aug 2 '18 at 10:40
  • works well now! Thank you! The scalar function in the SELECT query was causing issues. – Kevin Aug 2 '18 at 16:15
0

The reason why the table variable is giving horrible performance is because table variables have no statistics. Table variables can be every useful but use them with a small number of records to avoid the performance issue.

The estimated number of rows in the clustered index scan is correct; no problem with that statistic. Aaron is right (of course).

In my experience Outer apply also has a very handy usecase, but it's used far too often (very popular because it is a newer feature ...). Most of the cases I have seen are better off using (left) outer join (and optimal indexes of course). That usually gives a much better performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.