Improve querying table using dates

Question

I have a table that contains a huge amount of data and I want to query it using a date range.

Here's the query

SELECT TOP (10000) 
    [PositionId] AS [PositionId], 
    [DeviceId] AS [DeviceId],     
    [Course] AS [Course], 
    [Latitude] AS [Latitude], 
    [Longitude] AS [Longitude], 
    [Speed] AS [Speed], 
    [MomentTiming] AS [MomentTiming],                             
    [Fuel] AS [Fuel], 
    [Ignition] AS [Ignition]        
 FROM
    [PositionInsights]
 WHERE
    deviceId = 352 
    AND [MomentTiming] >= '8/12/2015 7:38:00 AM' 
    AND [MomentTiming] <= '8/12/2015 3:38:00 PM'

The problem is that this simple query takes too much time, about 35 sec

What indices are on the table? If there is no index on the field `deviceId`, adding an index on this field will improve the performance. What is the clustered Index ? If this is the most common (frequent) query run against this table, I I would suggest a cliustered Index on the columns `deviceId`, `MomentTiming`, and whatever the Prtimary Key Column is. — Charles Bretana, Aug 12 '15 at 13:47
visit this link may help, http://stackoverflow.com/questions/1716798/sql-server-2008-ordering-by-datetime-is-too-slow — Nick Bullatovci, Aug 12 '15 at 13:48
@CharlesBretana the only index that exists is on the primary key positionId — Omar Salah El-din, Aug 12 '15 at 13:54
see my answer, you need to start with indexing the fields in your where clause — Mark Giaconia, Aug 12 '15 at 13:55
Change the clustered index (currently only on `positionId`) to be a clustered Index on (`deviceId`, `MomentTiming`, and `positionId`) in that order. — Charles Bretana, Aug 12 '15 at 16:53

Mark Giaconia · Accepted Answer · 2015-08-12T14:00:12.847

As a general rule, any fields in your where clause should be indexed. And you should make sure the indexes are not fragmented over time if you are doing a lot of CRUD on the table(s). So I would create two separate indexes, one on the deviceid field and another on the MomentTiming field. If your table is in the millions or billions of rows, you will probably want to set up table partitions on one of those fields. If the deviceid field is an autonumber field, then that might be a good enough range partitioning value, otherwise partition on your MomentTiming date field. If you partition, keep the indexes the same as I described.

UPDATE: since you only have millions, not billions, of rows, just start with the indexing and see what happens. However, if your data is growing fast, you may want to look at partitioning anyway. Also, if you go the partitioning route, you should try to put the different files on different mountpoints to get the best performance.

HTH

another thought is that you could store your dates as bigints, since there is a lot of opinion out there that number-based indexes often provide faster lookups — Mark Giaconia, Aug 12 '15 at 13:54
datetimes are already a numeric value presented as a datetime. I doubt this would boost performance at all — t-clausen.dk, Aug 12 '15 at 13:59

Improve querying table using dates

1 Answers1