Best way to speed up requests using date columns

Our database is receiving the following requests:

select * from [schema].[table] (nolock) where (Date_A > Date_B and Archive = 0)

This is a normal request that is made by our application. On average, the request is taking between 200ms – 400ms to complete, with about 100ms – 150ms in CPU. Reads normally come in at about 48k. This is at least one example of how this script performs.

What would be the best way (if there is a way) to speed up this type of request?

The table has 240,932 rows and the queries return 0 records.

If there is only a small subset of rows with Archive = 0, you could use a filtered index:

CREATE NONCLUSTERED INDEX IX_Archive_Filtered
    ON [schema].[table] ([Date_A], [Date_B])
    INCLUDE ([Archive])
    WHERE ([Archive] = 0)

Note: I’ve made the index on the two date fields only because I don’t know of any other fields in your table – it might make more sense for this to be on a different field

Additional Note: always take care to include the filtering columns in the filtered index keys or included columns, so here for one example of why: Incorrect row estimation given with a filtered index

This keeps the index small (which means it uses less storage, and is quicker to read from), because only the rows where Archive is 0 are included in it.

If a filtered index won’t work for you (and there are a number of scenarios where they don’t work, see Filtered Indexes and Dynamic SQL for one example), you could try a normal nonclustered index on the archive field OR the date fields. You would want to create the index on whichever field is most selective – meaning which one will narrow down the results the most.

For instance, again if only a small subset of your rows have an Archive value of 0, you could create this index:

CREATE NONCLUSTERED INDEX IX_Archive_Includes
    ON [schema].[table] ([Archive])
    INCLUDE ([Date_A], [Date_B])

This will let your query seek to the correct rows (where Archive is 0), and then filter easily on Date_A and Date_B.

Additional comments about your query:

• you really shouldn’t use select * – enumerate each of the fields you actually need in your results to be more efficient, and allow more effective indexing
• you shouldn’t use nolock if the accuracy of the results is important
  • see this post for a quick demo of how results can be wrong with nolock: Using NOLOCK? Here’s How You’ll Get the Wrong Query Results
  • see this post for a lot more detail about using nolock (which is the same as the read uncommitted isolation level): The Read Uncommitted Isolation Level