Worthless Nested Recordsets

by Jason on

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):

   1: SET rs = OpenQuery(“Select OrderID, OrderTotal from Orders where OrderID = “ & IDVar & “)
   2:  
   3: Dim CustomerName
   4:  
   5: Do Until rs.EOF
   6:     SET rs2 = OpenProc(“exec spCustomerFromOrderID “ & 
   7:     rs.Fields.Item(“OrderID“).Value)
   8:  
   9:     CustomerName = rs2.Fields.Item(“CustomerName“).Value
  10:  
  11:     Response.Write rs.Fields.Item(“OrderID“).Value & 
  12:     rs.Fields.Item(“OrderTotal”) & 
  13:     CustomerName
  14:     
  15:     rs.MoveNext()
  16: Loop

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.