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
nolockif 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