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:

    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:

    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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.