Consider your alternatives. The vast majority of the time you do not need a nested recordset.
The scenario: A colleague of mine at a company in SA that will go unnamed messaged me about how to speed up a nested stored procedure inside standard query recordset.
His code at it’s core was something like this (Classic ASP and fictional db class used for illustration):
After doing this he was surprised that he had a bit of a speed problem…
I know it’s tempting when the boss or client comes back with a last minute addition to perform that in a nested loop. Just don’t do it…
Nested loops have their place but that place is almost never to retrieve a one column answer. Remember that if you are paging through lots of data, each internal RS is another query to your server. That overhead isn’t huge but on a fairly large recordset it will add up quickly.
Solution A: (Quick Implementation)
In the original recordset add a function (“Select OrderID, OrderTotal, dbo.fn_CustomerName(OrderID) from Orders . . .”Solution B: (Maybe you need data transformation)
Replace original recordset with your proc (return all columns needed from one proc)Solution C: (Far more likely what you need)
Just do a join in your select statement… If you’re bothering with a function or SP to retrieve a single column name from an indexed table, what’s the point?All three solutions (A,B,C) required no nested recordset. In actuality my friend was just hacking together a solution and all he needed was a join.
Instant performance gain.