Query on large table using DateTime WHERE clause

Question

I'm attempting to query data from a rather large table, approximately 98 million rows, using datetime columns in the WHERE clause. It takes approximately 12 minutes to complete - which is obviously not acceptable. The query is simple:

SELECT ID, DateTime1, DateTime2, Value1, Value2
FROM dataTable
WHERE DateTime1 >= '2017-05-15 09:00' AND
      DateTime1 <= '2017-05-15 09:30'

The table has the following structure:

Column Name | DataType
-------------------------
ID          | float
DateTime1   | datetime
DateTime2   | datetime
Value1      | float
Value2      | varchar(20)

The table has the following index:

Nonclustered: DateTime1, DateTime2, ID, Value2

Tag your question with the database you are using. You should also put Value1 into the index. — Gordon Linoff

SqlZim SqlZim · Accepted Answer · 2017-05-15T12:32:55

In SQL Server:

Your index does not cover Value1, so it has to retrieve that column from the table for each row if it is using your existing index.

You could create an covering index (includes all columns required by the query) like so:

create nonclustered index ix_dataTable_DateTime1_cover 
  on dbo.dataTable (DateTime1)
    include (Id, DateTime2, Value1, Value2);

or modify your existing index to include Value1.

Also, check the execution plan. If still have performance issues, share your execution plans using Paste The Plan @ brentozar.com here are the instructions: How to Use Paste the Plan.

Query on large table using DateTime WHERE clause

2 Answers