How to avoid re-execute the query on a MoveFirst

Question

I have a query in a ASP page. The record set that I obtein must be printed in 3 different tables, after some conditions. So, for avoid extecuting 3 times almost the same query, I decided to search the recordset for the results I need...so I need to make a RS.MoveFirst two times. But...when I analyzed with SQL Profiler, I saw that MoveFirst operation re-executes my query...exactly what I wanted to avoid. How can I do the cache the results and only move around the recordset?

Answer 1

Use a disconnected recordset

Const adOpenStatic = 3
Const adUseClient = 3
Const adLockOptimistic = 3

Dim conn: Set conn = Server.CreateObject("ADODB.Connection")
conn.Open sYourConnectionString

Dim rs : Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient

rs.Open sYourSQL, conn, adOpenStatic, adLockOptimistic

Set rs.ActiveConnection = Nothing
conn.close

'' // You can now roam around the recordset with .MoveFirst, .MoveNext etc without 
'' // incurring any further hits on the DB.

Note that if you have parameters to supply to your sql you will need to use an ADODB.Command object between the connection and recordset (don't be tempted to use string concatenation). Still the principle is the same use a client cursor location and static recordset then detach and close the connection.

Answer 2

personally i would simply use rs.getRows() and navigate into the array... not only are u sure u never hit the database again you should see a huge gain of performance everytime you use it