x

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

more ▼

asked Jun 12, 2014 at 06:53 AM in Default

avatar image

gourav
31 2 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jun 12, 2014 at 07:03 AM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x162
x45

asked: Jun 12, 2014 at 06:53 AM

Seen: 413 times

Last Updated: Jun 12, 2014 at 07:03 AM

Copyright 2017 Redgate Software. Privacy Policy