Query tuning

I think that I've found the best blog post on SQL tuning I've ever read.

It's by Jeff Moden, and it's entitled More RBAR and "Tuning" UPDATEs.

I had a similar issue the other day, where I had a query that was taking almost 7 minutes to retrieve 6,000 records. For some reason it was doing a UNION of two SELECT statements, yet the second SELECT statement was the same as the first - so I thought that this was what was causing the issue.

And sure, when we took out the second SELECT statement the query reduced to about 3 and a half minutes.

Yet that was absolutely still too long. When I looked at the statement a bit further, I noticed that it was doing a LEFT OUTER join against a view that used a count function, a correlated subquery and a CASE statement, and not only that but when the actual SELECT statement found a NULL yet another CASE statement was used to convert the NULL into a value. The query plan was hideous - for 6,000 records in the final query output the LEFT OUTER join to the view was bringing back 1.6 million rows.

Hello RBAR!

So I did exactly what Jeff did - read the view and the query to work out what it was trying to do, then I rewrote the view to be more sensible. All of a sudden I went from about 3.5 minutes to run the query to 7 seconds.

So lesson learned - RBAR bad, set-based good.