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:
http://support.microsoft.com/default...b;en-us;235282

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 [email protected] 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

cn.Open

rs.Open “SELECT * FROM CUSTOMERS; SELECT * FROM ORDERS;SELECT * FROM EMPLOYEES”

‘ Use results from first select statement

rs.NextResultSet

‘ Use results from second select statement

rs.NextResultSet

‘ Use results from third select statement

cn.Close

This is better because it retrieves the results of the queries in one go instead of making 3 separate calls.

"

And

"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,

Paul

__________________
http://www.thewebsiteshop.co.uk

I guess the question that is begging to be asked is: Are you having performance issues with the way things are currently done?

I subscribe to the Mantra that If it isn't broke, don't fix it.

In so far as concurrent connections, in Enterprise manager you should be able to view how many connections you have to the server and who owns the connections. (I have not worked with 2005 but in 2000 you can find this under Management --> Current Activity)

In my experience, I would always open my connection, do my processing, and then close the connection after I had completed all of my processing and never really ran into a problem.

hth.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

Thanks dparsons,

No problems to date but the intention within the organisation is for the application to be used more widely and the concern is that it won't scale well in it's current state. I don't really want to get into a fix-on-fail situation which is why I'd like to determine the best way forward with respect to database connections for an application that is intended to scale.

Well if I can provide you with a real world example:

Years ago I was the webmaster for a very prominent Newspaper in my area and our website recieved about 15,000 unique visitors a day; all of the functionality was ASP 3.0 and all of the data elements were being pulled from an ACCESS backend. In my year and a half tenure there I never had an issue in regards to connection leaks or a preformance hit from the way I did things. (Much the same way you are doing them in your app currently)

While not trying to compare Apples to Oranges, in .NET I do something similiar. I create my connection objects, execute my queries, work with my data, and close the connections; I have no problems with database or application preformance and, one application particular, is used all day extensively by about 300 staff members.

How many people currently use the application? IMHO if its any sort of significant number 200+ and you have not experienced problems with the way things are currently done, I don't forsee it to be a problem.

hth.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

It would be beneficial to have the queries moved over to stored procedures. Where you had "SELECT * FROM CUSTOMERS; SELECT * FROM ORDERS;SELECT * FROM EMPLOYEES", I would create a single stored prodcure that returns all three recordsets.

My reasoning is that once a stored procedure has been run once, all subsequent times that it's run, will be faster (until the SQL server is rebooted).

So the hit times would be (made up numbers, for example sake) for 5 hits;

Your query: 4s; 4s; 4s; 4s; 4s = 20 seconds
Procedures: 4s; 1s; 1s; 1s; 1s = 8 seconds

Not much, I know, but if you get into a query that takes 20-30 seconds, then you could save quite some time.

Regards,

Sean Anderson