CODEWITHSUNDEEP
sqlsql-server
Cesar
Cesar

Reputation: 617

How to Speed up SQL query for Date

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

Upvotes: 1

Views: 1876

Answers (1)

Joe
Joe

Reputation: 1139

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.

Upvotes: 5

Related Questions