>I've spoken to one of the others involved in this test and it transpires that there was an index on DELETED() on these tables (actually, on every table in the building!). This slows things down. Off the top of our heads we recalled that this was slowing things down. Select was taking 16 seconds to return a data set, seek and scan was taking about 1.5 seconds to do same. Set deleted off returned the set in 0.2 seconds, moving clauses between WHERE and HAVING improved slightly again.
>
>I will try to get the figures for you. It seems like, on large tables, some indexes are transferred to your local drive BEFORE the select is done - I think this is an optimization feature.

This is true. The FP Advisor article of (was it May) explained this in detail...