Execution Time Question

[snitin78]

Hey Everyone,

I have a SQL Query that I am executing on MS SQL Server 2008 (with compatibility mode). This query uses 3 CTEs and returns data anywhere in the range of 5000 to 40K rows.

The query roughly looks like this:

WITH CTE_1 (col1, col2...) as (
  select something, something, ...
  from table1
  --some inner joins
  where something_in_table1 between @lowerBound and @upperBound
),
CTE_2 (col1, col2...) as (
  Select something, something, ...
  from table2
  -- some inner joins
  where something_in_table2_1 = @something1
  and something_in_table2_2  between @lowerBound and @upperBound
),
CTE_3 (col1, col2...) as (
  Select something, something, ...
  from table2
  -- some inner joins
  where something_in_table2_1 = @something2
  and something_in_table2_2  between @lowerBound and @upperBound
)
select CTE_1.*, CTE_2.col2, CTE_3.col2
from CTE_1 
LEFT OUTER JOIN
CTE_2 ON CTE_1.col2 = CTE_2.col2
LEFT OUTER JOIN
CTE_3 ON CTE_1.col2 = CTE_3.col2

Here @lowerBound and @upperBound hold the same value for all the CTEs and CTE_2 & CTE_3 are pretty much the same except that the where clause checks for different values in a column of table2 (I did not use just one CTE with an in clause cause I believe that it would have made the CTE a little slower in execution).

The query suffices my purpose, however, I am seeing different execution time for the same query, with same params and same result, every time I run it. For example, certain parameter values result in say 35K rows...then for the first run it takes anywhere between 15-20 secs where as for second it could be 10-15 secs, then for third it could be 5-10 secs.

Any ideas as to why this difference in execution time! Again, this is on MS SQL Server 2008 (with compatibility mode).

Any suggestions for query optimization are also welcome (although, I do hope that the rough query provides enough info for such suggestions] :-D

 

[bborissov]

The only optimization you could make is in the CTEs themselves. Check the execution plan for each one.
The last query IS NOT optimizable. That is why I don't like much CTEs

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Do you use RECOMPILE option with your SP?

Also I would combine CTE_2 and CTE_3 as one CTE

where SomeField IN (SomeParameter1, SomeParameter2) and add one extra field in it.

So, you would not need an extra LEFT JOIN - I think (not 100% sure) that you would be able to only use 1 join for your case.

 

[snitin78]

Thanks for you replies guys.

Borissov, I looked into the execution plans and provided some index hints to the queries. That made it a little faster but still the execution time is not constant. Also, AFAIK CTEs are supposed to be faster than traditional joins..but that's just from Microsoft

Markos, I am not using the Recompile option with my SP? Are you suggesting that I should use it? My guess is not..but lemme know. I'll try your idea about having one CTE.

 

[bborissov]

CTEs are faster that the usual methods to get hierarchical data (w/o using cursors, temp tables, WHILE loop etc.), but when you JOIN them then that is another question
Then they behave like usual table variables w/o any indexes, and what is worse you CAN'T set one

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.