We have a reasonably sized web application written in ASP 3.0 with a SQL Server 2005 database back end. The company see the user base of this application growing considerably in the near future.
The application communicates with the database through an OLEDB provider.
The asp scripts range in size from 1kb to 74kb although most are in the 10-20kb range, so most scripts do a reasonable amount of processing.
In a typical asp script queries might be executed to determine the logical flow of the script and to provide values for subsequent queries or output, so batch processing is not practical.
The traditional practice has been to open a database connection at the top of each page, do the processing and close the database connection at the bottom of the page.
Since the calls are never directly in sequence there is a concern that each call spawns a new connection to the database:
Also as these are not explicitly opened or closed is it correct they are not managed by pooling and hence are being left open once the script has completed?
A method has been proposed which encapsulates the code required to execute database queries. The code makes use of connection pooling by explicitly opening and closing the connection after each call to the database as this does not actually close the connection but returns it to the pool where it is supposedly managed effectively according to the current load on the server.
Due to it's age a lot of the database queries are by means of inline SQL statements within the asp scripts. Ideally these will in time be replaced with stored procedures but for the time being our concern lies with the issue of whether we should open a database connection at the top of the page, make database calls throughout the script or open & close a connection explicitly after each query.
We sent a message to firstname.lastname@example.org
who replied with the following:
"In this particular case, an ASP page that is expected to execute rather quickly and as connection pooling would normally be in effect (which means that the physical connection would actually be held open for the lifetime of the application regardless of if you close it and open it again) then it would not really make a difference which approach you go for.
I would opt for the latter however, open the connection execute all three statements and then close the connection, as the intermediate closing and opening of the connection would be redundant in this case if there is no significant processing to be done in between the calls.
Better still however would be to open the connection, execute all three statements as a batch and then close the connection.
For instance you could have code like this:
cn.CusrorLoction = adUseClient
rs.Open “SELECT * FROM CUSTOMERS; SELECT * FROM ORDERS;SELECT * FROM EMPLOYEES”
‘ Use results from first select statement
‘ Use results from second select statement
‘ Use results from third select statement
This is better because it retrieves the results of the queries in one go instead of making 3 separate calls.
"Opening the connection at the top of the page and closing it at the bottom is fine.
Moving forward, if the page has a lot of queries that depend on the results on others then thought should be given to encapsulating all the decision making logic into a single stored procedure which is given as input parameters everything that it needs.
I hope this helps."
My personal opinion is that our scripts do have a reasonable amount of processing in between calls and aren't currently written in a way that makes it possible to execute all the queries in one go, for reasons mentioned earlier. Additionally the work involved in wrapping everything up in a stored procedure for every page requires more effort than we can throw at it right now, so we need an intermediate solution.
So our question is do we open & close at the top & bottom of each script or before and after each call and what kind of tests could we run to give us a definite insight into the consequences of either approach for this application, such as seeing how many connections are open after a certain amount of usage?
Many Thanks in advance,