Using a Calculation within a JOIN or after using a CTE performance

118 Views Asked by At

I have tried Googling this to find out if there might be a clear answer on this but I am currently trying to evaluate some ways to optimize a query for work with a connection to MS SQL Server.

The current query has a join that looks something like:

..
SELECT
column1
, column2
, column3
INTO #temptable1
FROM table1 t1
LEFT JOIN table2 t2
ON t1.column1 = t2.column1

WITH cte1 as (
SELECT c1.*
CASE WHEN ((t1.CALENDAR_YEAR%4 = 0 and t1.CALENDAR_YEAR%100<>0) or t1.CALENDAR_YEAR%400=0) AND MONTH(t1.START_DATE)=2 AND DAY(t1.START_DATE)<=29 AND DAY(t1.END_DATE)>=29 THEN (DATEDIFF(day,t1.START_DATE,t1.END_DATE)+1-1)/365.00
ELSE (DATEDIFF(day,t1.START_DATE,t1.END_DATE)+1)/365.00
END AS datecalc
FROM temptable#1 as c1
)

SELECT 
column1
, column2
, datecalc
FROM cte1

My thinking is to simply move the datecalc up into the SELECT INTO query and do away with the CTE all together because the calculation can be added to the rows as they are joined, rather than executing the join AND THEN going back through every row to add the calculation as follows:

..
SELECT
column1
, column2
, column3
CASE WHEN ((t1.CALENDAR_YEAR%4 = 0 and t1.CALENDAR_YEAR%100<>0) or t1.CALENDAR_YEAR%400=0) AND MONTH(t1.START_DATE)=2 AND DAY(t1.START_DATE)<=29 AND DAY(t1.END_DATE)>=29 THEN (DATEDIFF(day,t1.START_DATE,t1.END_DATE)+1-1)/365.00
ELSE (DATEDIFF(day,t1.START_DATE,t1.END_DATE)+1)/365.00
END AS datecalc
INTO #temptable1
FROM table1 t1
LEFT JOIN table2 t2
ON t1.column1 = t2.column1

SELECT 
column1
, column2
, datecalc
FROM #temptable1

Any thoughts on performance here? Currently the script takes 5 hours to run (there is a lot more than just this but this seems to be the pain area of the script) and the main table has 174 Million records for reference.

0

There are 0 best solutions below