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.   ...
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.
  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 08, 2024

    Waiting your test result.

    You'd better post xml of execute plan, we can dig it deeper.

Resources