CODEWITHSUNDEEP
sqlsql-serverperformancet-sqlsql-execution-plan
Proviste
Proviste

Reputation: 177

SQL Server query behavior

vw_project is a view which involves 20 CTEs, join them multiple times and return 56 columns

many of these CTEs are self-joins (the classic "last row per group", in our case we get the last related object product / customer / manager per Project)

most of the tables (maybe 40 ?) involved don't exceed 1000 rows, the view itself returns 634 rows.

We are trying to improve the very bad performances of this view. We denormalized (went from TPT to TPH), and reduce by half the number of joins with almost no impact.

But i don't understand the following results i am obtaining :

select * from  vw_Project (TPT)
2 sec 

select * from  vw_Project (TPH)
2 sec 

select Id from vw_Project (TPH , TPT is instant)
6 sec

select 1 from vw_Project  (TPH , TPT is instant)
6 sec

select count(1) from vw_Project (TPH , TPT is instant)
6 sec

Execution plan for the last one (6 sec) : https://www.brentozar.com/pastetheplan/?id=r1DqRciBW

execution plan after sp_updatestats https://www.brentozar.com/pastetheplan/?id=H1Cuwsor-

To me, that seems absurd, I don't understand what's happening and it's hard to know whether my optimization strategies are relevant since I have no idea what justifies the apparently irrationnal behaviors I'm observing...

Any clue ?

Upvotes: 0

Views: 85

Answers (1)

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

CTE has no guarantee order to run the statements and 20 CTEs are far too many in my opinion. You can use OPTION (FORCE ORDER) to force execution from top to bottom.

For selecting few thousand rows however anything more than 1 sec is not acceptable regardless of complexity. I would choose an approach of a table function so i would have the luxury to create hash tables or table variables inside to have full control of each step. This way you limit the optimizer scope within each step alone.

Upvotes: 1

Related Questions