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.

up vote 5 down vote accepted

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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