Does having more than one Recordset slow down a page in any way?

[MustangManny]

I have a page that has 5 recordsets, each with a particular SQL query to filter out five different tables of info one page.
Would having more than one recordset slow things down? If so, is there an easier and/or efficient way around it?

Thanks In Advance.

 

[Ovatvvon]

One table it's not gonna matter until you get into the 10's of thousands range for access. (more for sql server). The thing that slows them down is the more it has to interact between tables. The more tables you have to work with, the slower it'll go. The more stuff you can figure out how to do with less tables, the faster it'll work.
-Ovatvvon :-Q

 

[vasah20]

multiple recordsets can slow down your page, because of the memory involved and the possiblity of keeping your connection open too long.

What Ovatvvon posted is correct, in terms of database access and manipulation, but to expand on his idea some more, the slow down you'll recieve is not only from the DBMS, but also from the network and the webserver.

The real question here is whether or not those 5 recordsets are open for the entire page, or if you close them immediately after you are done. If you open connections late and close connections early, then that will help your speed somewhat. (the only connections you have open are the ones that you are processing)

A better way to do this would be to get all 5 recordsets at once, then disconnect the recordset. You would do this by separating your SQL query with ; between each recordset result, then using the .NextRecordset method to get each recordset. See

http://www.devguru.com/Technologies/ado/quickref/recordset_nextrecordset.html

and

http://www.4guysfromrolla.com/webtech/080101-1.shtml

for info about .NextRecordset and Disconnected Recordsets, respectively

IMHO, The best way to do this would be to have the SQL code as a Stored Proc, have it spit back 5 different recordsets, then disconnect the recordset and use .NextRecordset to traverse through the tables. It's possible that you can use .GetRows to get the Recordset information, but I've never been able to get .GetRows to work with a disconnected recordset.

sidenote: I've never used .NextRecordset with a severed connection, so I don't know if this will work. but in theory, this would be better than opening up 5 different recordsets to connect to the DB 5 different times.

anyways - hth
leo

 

[MustangManny]

Thank you kindly. Is there a way to make my own Stored Procedures via something like Ultradev instaed of having to pin down the DBA?

Thanks so much for taking the time to respond. It is greatly appreciated.