Clever Geek Handbook

Query on a large table using DateTime WHERE clause

I am trying to query data from a fairly large table, about 98 million rows, using datetime columns in a WHERE clause. It takes about 12 minutes to complete, which is obviously not acceptable. The request 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
+3


source to share


2 answers


In SQL Server:

Your index does not cover Value1

, so it must retrieve that column from the table for every row if it uses your existing index.

You can create a coverage index (including all columns needed for the query):



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

or modify the existing index to include Value1

.

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

+1


source


You only have DateTime1, so make an index on that column only. A complex index is like concatenating a string from all its parts, such as DateTime1 + DateTime2 + ID + Value2. Oh yes, it must be slow.



0


source






More articles:


All Articles