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)

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 CustomerFROM 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 CustomerOPTION(RECOMPILE) Read More

Cart

Cart