asked
inner join take time to execute
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. SELECT CTE1.[PROPERTY CODE], CTE1.[UNIT TYPE], CTE1.[AVERAGE CPR EFFECTIVE RENT], CAST(ROUND(CTE2.[AVERAGE LEASE AMENITY AMOUNT],0) AS INT) AS [AVERAGE LEASE AMENITY AMOUNT], CTE1.[NUMBER OF LEASE IN CALCULATION], CTE1.[VALID LEASES CASE#] FROM CTE1 INNER JOIN CTE2 ON (CTE2.[LRO PROPERTY ID]=CTE1.[PROPERTY CODE] AND CTE2.[LRO UNIT TYPE]=CTE1.[UNIT TYPE]) 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
14 People are following this question.
