better? lots of rs.open or one big rs.open?

[DMcManus]

I'm using ADO. I have a few different tables that I join together in a query. I then use the data on an Active Server Page.
I'm not sure what is better to do...
I have a page that needs to open certain information each time. Then I need info from other fields only during certain situations. Right now my query gets all the fields that I might possibly need.
So now...should I just do one select statement and return all the fields (about 25). Or should I just get the 15 or so I need, then when I need other fields do another select statement and get the info that I need again? I might have to change select statements about 6 times...or only 2 sometimes...just wondering what's best? Or maybe should I even have the 6 different queries for each and then open those?
Thanks

[bashfirst]

I don't think you'll get a fixed answer on this question. It probably comes down to the performance you need from the application, which is dependent on the db engine, the size of the tables, if they're indexed well. Why not try it the way that makes the most sense from a programmers standpoint (with a view toward maintenance) just to get it to work. Then if the performance is ok, let it go. They say that most programs spend 90% of the time in 10% of the code... so don't bother optimizing code that the application rarely uses or that operates adequately to start with...

Hope this bit of philosophy helps.
Bash

Another thing to consider is whether or not you are pulling information over the network. If your page is on one machine and your database on another -- how much of a load are you putting on your network. Are other slowed down when somebody online pulls that information. If so... balancing program efficiency with network efficiency will be required.

[DaveAMS]

Two thoughts for you:

1. Take a look at using disconnected recordsets. This is usually a big performance booster, especially on high-traffic websites. This hyperlink is to an article that describes their use in a web environment:

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

2. There's nothing quite as good for optimizing performance as live testing. Microsoft has created what they call a "web application stress tool" that gives you the ability to test your website under different loads. You might get yourself a copy of it at the following link:

http://www.microsoft.com/technet/treeview/default.asp?url=/TechNet/itsolutions/intranet/downloads/webstres.asp?frame=true

You can use this tool to see how much of a performance issue things like extra fields in your recordsets are. Monitor the performance of your machine (processor % utilized, ram pages/sec, hard disk reads/writes, etc) and see if you're seeing big increases or decreases with a particular change.

Web optimization is a lot harder than optimizing a vb program to run on a standalone machine, and there are a lot more factors to consider. If you're using ASP/IIS, look at some of the stuff at:

http://www.4guysfromrolla.com

If you're using SQL Server as your database, try:

http://www.sqlteam.com

Hope this helps