I recently had the chance to look over a friends shoulder at a query that needed some tuning work. In the previous iteration of tuning they had found that running a portion of the query inside a cursor actually yielded better performance than a one query takes all set based approach. If you've read my SSC article about cursors you'll know that I'm not anti-cursor, more a pragmatic-cursor guy, so the question is - without benefit of a couple hours with the code and data - why would the cursor solution be more efficient?

Don't have a provable answer, but my guess is that the sheer size of the set based approach was driving one or more table scans. Not that big queries can't be done without table scans of course. If you think of one of the tables as having the where clause values, by looping through those and executing the rest of the query you would potentially get better query plans in because the optimizer would see that the value was distinct enough to use an index. So essentially by running more specific queries the statistics might allow you to get a faster plan than if you run it all at once.

Note that I'm not suggesting that everyone start using cursors to boost performance, just that there have been cases where it worked and it's interesting to consider in which scenarios we might look to the technique.