Search code examples
sqlsql-serverwhere-clausedateadd

SQL Server 2014 Using DateAdd in where clause to filter huge table

I have two tables Meter and MeterSpot. Meter table has about 9000 records and MeterSpot has about 70 million records. MeterID column creates the relationship between two tables. This is part of the table design:

  • Meter : MeterID int, ContractHour int, etc......

  • MeterSpot : MeterSpotID int, MeterID int, RecordStamp datetime, VolumeToday float etc......

I have a clustered index on Meter.MeterID and MeterSpot.MeterSpotID and non-clustered index on MeterSpot.RecordStamp and MeterSpot.MeterID.

Now I need to query data from these two tables and get MeterID, ContractDate and VolumeToday columns for which ContractDate falls between a given date range. ContractDate is calculated by subtracting ContractHour of Meter table from RecordStamp of MeterSpot table.

This is what I have tried:

SELECT  
    M.MeterID, MS.VolumeToday,  
    DATEADD(hour, -(M.ContractHour), MS.RecordStamp) AS 'ContractDate'      
FROM 
    Meter AS M
INNER JOIN  
    MeterSpot AS MS ON M.MeterID = MS.MeterID   
WHERE 
    (DATEADD(hour, -(M.ContractHour), MS.RecordStamp)  
       BETWEEN @StartDate and @EndDate)

This query is taking hours to execute. I have searched little and found out that when I add functions on where clause the index is not considered.

I tried below and it was quite fast for smaller date ranges.

WHERE MS.RecordStamp BETWEEN @StartDate AND @EndDate)

I tried to add ContractHour to @StartDate and @EndDate without subtracting it from RecordStamp. Still performance looks the same.

Questions:

  1. How can I filter data from calculated column?
  2. Is there any better way for me to achieve my task?
  3. What are the good practices which I should follow when working with this type of huge tables?
Solution

  • You could give your date criteria twice, once in a way that informs SQL how it may leverage its indexes to accelerate your query, and again to filter out precisely the rows you need to. Is there some maximum value for ContractHour? If so, then you could do something like this:

    select ... 
from Meter m 
join MeterSpot ms on m.MeterID = ms.MeterID
where 
     (ms.RecordStamp between @StartDateMinusMaxContractHour and @EndDate)
     and (dateadd(hour, -@m.ContractHour, ms.RecordStamp) between @StartDate and @EndDate)