Forum Discussion

JohnTM's avatar
JohnTM
Brass Contributor
Sep 26, 2024

Unused Join affects performance on View select

The query below runs in less than 1 second when dbo.CustomerTeam, which contains 6 rows, is a table but almost 4 minutes when it is created as a view.
 
ProjectManagement contains 737615 rows.
 
The query plan shows 221663 rows read on ProjectManagement to Project/ProjectTask join when CustomerTeam is a table but 4195075 when it is a view - despite CustomerTeam not being on the join path!
 
Rows read on the Project Management to Customer Team join is 149790 in both cases - which is the number of rows in team 'Sales Team'.
 
Why is the performance on the ProjectManagement to Project/ProjectTask join being negatively influenced by CustomerTeam, which is on a different join, being a view?   

SELECT Customer
FROM ProjectManagement pm
  LEFT OUTER JOIN dbo.CustomerTeam ct ON ct.[Customer Team DynID] = pm.[Customer Team DynID]
  LEFT OUTER JOIN dbo.Project p
     INNER JOIN dbo.ProjectTask pt
        ON p.[Project DynID] = pt.[Project DynID]
        ON pt.[Project DynID] = pm.[Project DynID] AND pt.[Task DynID] = pm.[Task DynID]
  LEFT OUTER JOIN dbo.Customer c ON p.[Customer DynID] = c.[Customer DynID]
WHERE ct.[Customer Team] IN ('Sales Team')
GROUP BY Customer
OPTION(RECOMPILE)

4 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Sep 27, 2024
    When you create the view from CustomerTeam, try create it as indexed view, and set its clustered index with same columns from base table. See if it works.
    • JohnTM's avatar
      JohnTM
      Brass Contributor
      Sep 27, 2024

      Hi rodgerkong,

       

      , Thanks for your reply.

       

      Using an Indexed view isn't an option - there are a lot of restrictions on creating indexed views and the CustomerTeam view falls foul of one of those - the underlying table is in a different schema.

       

      But it did lead me to a solution - if not an explanation...

       

      The underlying table has a number of uniqueidentifier data type columns and the view uses CAST to convert these to varchar.  The execution plan referenced a "PlanAffectingConvert" on the dbo.CustomerTeam.[Customer Team DynID] column. I changed the data type on the column on the underlying table, recreated the view without the CAST, and the problem is gone...

       

      So, changing the underlying data type (luckily an option in my scenario), works around the problem.

       

      But it still doesn't explain why the PlanAffectingConvert on one join 'branch' of the query had such a considerable impact on an entirely unrelated 'branch'.

       

      • JohnTM's avatar
        JohnTM
        Brass Contributor
        Oct 08, 2024
        ...and have now found that this is not a general solution... I have now found another example, without uniqueidentifier data types, where specifying a join has had an enormous impact on a query's performance for no apparent reason - and there's no "PlanAffectingConvert" issues that I can see.

        I presume this qualifies as a bug in the Query engine. The issue is found in an Azure SQL database - I'll do some testing to see if this is a general SQL thing or just Azure SQL.

Resources