Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 6th, 2006, 04:15 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default Opening & Closing Database connections

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 ukdevscr@microsoft.com 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
Reply With Quote
  #2 (permalink)  
Old December 7th, 2006, 05:46 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
Reply With Quote
  #3 (permalink)  
Old December 8th, 2006, 11:43 AM
Authorized User
 
Join Date: Dec 2006
Location: , , .
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old December 8th, 2006, 02:17 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
Reply With Quote
  #5 (permalink)  
Old December 18th, 2006, 08:50 AM
Friend of Wrox
 
Join Date: Oct 2006
Location: Northampton, Northants, United Kingdom.
Posts: 115
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
closing connections - application pooling goingmad Classic ASP Databases 0 September 29th, 2005 08:20 AM
closing connections - application pooling goingmad SQL Server ASP 0 September 29th, 2005 07:19 AM
Closing db connections - when/how? SoC Classic ASP Basics 11 November 23rd, 2004 05:47 PM
Closing ADO connections in new page darrenw SQL Server 2000 0 September 15th, 2003 11:48 AM
Closing connections when redirecting alldis Classic ASP Basics 3 July 30th, 2003 08:33 AM



All times are GMT -4. The time now is 10:44 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.