Forum Discussion
Re: Unused Join affects performance on View select
3 Replies
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'.
- ...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.Waiting your test result.
You'd better post xml of execute plan, we can dig it deeper.
