How to Speed up SQL query for Date

Question

I have a query, I'm using an inner join from 2 tables that have about a million rows. I'm trying to run the query so it only gets data from last month. However, it takes a really long time when using the getDate() function. But when I enter the date in this format '2016-12-01' and '2017-01-01' - it's really quick. How can I modify the query so it runs faster? I read that I might have to create a non-clustered index but I'm not really good with those yet.

select 
    custKey,
    sum(salesAmt) as Sales, 
    sum(returnAmt) as Credit, 
    (sum(salesAmt) - sum(returnAmt)) as CONNET
from
    [SpotFireStaging].[dbo].[tsoSalesAnalysis] 
inner join 
    [SpotFireStaging].[dbo].OOGPLensDesc as o on tsoSalesAnalysis.ItemKey = O.ItemKey
where 
    PostDate between --DATEADD(MONTH, DATEDIFF(MONTH,0, GETDATE())-1,0 ) 
--AND DATEADD(MS, -3,DATEADD(MM, DATEDIFF(M,-1, GETDATE()) -1, 0)) 
    '2016-12-01' and '2017-01-01'
group by 
    custkey

what's the datatype of `PostDate`?, are there any indexes in the table? — Lamak, Jan 03 '17 at 18:16
Is it possible to calculate those two bracket dates and put them in two variables before? What happen if you do? I mean `PostDate between @firstDate AND @secondDate`. — DVT, Jan 03 '17 at 18:18
Hi, Yes I tried this and it worked perfectly. I can't believe I didn't think of this. Thanks for the help! :) — Cesar, Jan 03 '17 at 18:25

score 5 · Accepted Answer · edited May 23 '17 at 12:16

declare @startDate DateTime = DATEADD(MONTH, DATEDIFF(MONTH,0, GETDATE())-1,0 )
declare @endDate DateTime = DATEADD(MS, -3,DATEADD(MM, DATEDIFF(M,-1, GETDATE()) -1, 0)) 

select 
    custKey,
    sum(salesAmt) as Sales, 
    sum(returnAmt) as Credit, 
    (sum(salesAmt) - sum(returnAmt)) as CONNET
from
    [SpotFireStaging].[dbo].[tsoSalesAnalysis] 
inner join 
    [SpotFireStaging].[dbo].OOGPLensDesc as o on tsoSalesAnalysis.ItemKey = O.ItemKey
where 
    PostDate between @startDate AND @endDate
group by 
    custkey

another alternative, check out the selected answer here:

When using GETDATE() in many places, is it better to use a variable?

GetDate() is calculated separately for each row, so we gotta belive so is DateDiff() and DateAdd(). So we are better off moving it into a local variable.

How to Speed up SQL query for Date

1 Answers1