A way to make this query faster
The sort takes 44 procent of the cost, but leaving out the SORT only saves 0.5 seconds.
The total query takes about 8 seconds and returns +/- 20.000 rows
Thanks for your assistance.
There are a lot of scans occuring in the plans, these may be able to be turned into seeks. There is a missing index warning being shown too.
Perhaps a bit more relevantly how are you measuring the 8 seconds ? Management studio is slow at drawing the data, and in my experience this leads to a lot of wild goose chases.
Run the statement in management studio but turn on the "Ignore results after execution" option in the query options, that gives a more accurate measure.
The result set itself is fairly large, query analyzer reports it as 291MB, quite sizeable ? How fast can your network support a file transfer of that size ?
I've learned a lot yesterday and the developers will take a good look how to change the application.
Thanks a lot again!
The missing index is an index with a lot of included columns in there. DTA also suggested this and it saves about 9 percent. I've created it, but it did not save a lot of time or I/O.
Will talk to the developers to tune this query so we do not have to send 291 Mb over the network to the clients.
Many thanks for your help.
Dick