Is it possable to improve on this?
This query still seems to be our worst. Currently down to 6.5 mins for 6071239 Rows. Is it possable to improve on this?
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).