Best way to speed up requests using date columns Our database is receiving the following requests:

select * from [schema].[table] (nolock) where (Date_A &gt; 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