I Created CTE on tables to perform the Calculations..
CTE 1 and CTE 2 Perform some arithmetic Calculation individually.
CTE 1 Returns 219 Rows and CTE 2 Returns 200 Rows,
Now when i try to Join CTE1 and CTE2 using inner join execution time reaches to 3 mins I Checked Columns which i used in joins, They have non Clustered index with included columns present on respective tables.
Following is the SQL Code for the same.
When i fetched data from CTE 1 and CTE 2 independently it takes 3-4 seconds to execute.
Please assist me so that i can reduce the execution time for the same
Thanks & Regards Gourav
The optimizer is expanding out the query, as if you had written it without the CTE definitions (not that that's how it gets executed anyway). It might be that the re-organisation of the query together with the statistics for your objects means that the execution plan is less than optimal.
You need to look at the execution plan to see what the optimizer has decided to do. Sometimes you can 'force' the optimizer to follow the CTE design by materializing the CTEs - using temp tables or ranking functions
answered Jun 12, 2014 at 07:03 AM
Kev Riley ♦♦