Is it possable to improve on this?

RichardBrianSmith 2015-09-29 15:19:04

This query still seems to be our worst. Currently down to 6.5 mins for 6071239 Rows. Is it possable to improve on this?

Aaron Bertrand 2015-10-04 13:47:19
Have you looked at implementing the index recommended for [tblArchiveDataObjectDetail]? If the Value IS NOT NULL clause is common and there are a lot of rows in the table where Value is NULL, a filtered index might be more beneficial for this particular query pattern. After that, you may consider partitioning the archive table by date so that your filter might get some benefit from partition elimination (assuming you are typically only looking at recent data).
RichardBrianSmith 2015-10-05 13:43:41
Thanks Aaron. The recommended index is used. Once augmented with 'where [value] is not null' reduced from 9 mins to 5 mins. Thanks again 🙂