Database Connection Question

[Christian Cooper]

I have a question for you, it involves IIS and database connections.

When I got hired, we were using an asp include file with database functions
and would open and close the database connection multiple times in 1 script.

Common sense dictated we should only open the connection once, get all
necessary information, and close it once per script load. We ran some of
your stress testing software and obviously saw performance increases.

Then I stumbled upon the ability to open a database connection when the user
first creates a session and logs into our authentication, and close it when
that session expires or the window is closed. This would mean each user
using the system would connect once and disconnect when they are done.
Otherwise, each user using the system would be making let's say 5 database
connections and disconnections per minute, each time they load an asp page.
So if each user uses the system for 1 hour, and there are 500 of them, there
are 150 000 open and closes.

My Question:

Wouldn't 500 open and closes be LESS stress on the system, by using a
persistent connection?

Also, then I can run queries on the system tables and tell how many users
are logged into the application....

I don't know, the persistent connection seems much better to me, easier to
use and less network traffic, but I've read the opposite, am I missing
something?

[Ken Schaefer]

You should read up on connection pooling. When you open a connection you
aren't physically opening a connection. You get one from the OLEDB/ODBC
pool, and when you close it in your code, it is returned to your pool.

You should *not* use a session object to hold a connection open. You will
kill application scalability (it might work a bit faster, but will support
less users). The session connection will be held open even if it's not being
used by the current user.

You can find information about connection pooling here:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp[/url]
There is an explicit warning about using session/application objects to
store connections

The above link doesn't work at the moment, but you can get the article out
of the google.com cache:

[url]http://216.239.57.104/search?q=cache:VhwjVZOsntEJ:msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp+site:microsoft.com+Pooling+in+the+Mic rosoft+Data+Access+Components&hl=en[/url]

Cheers
Ken

"Christian Cooper" <christiancooper77@hotmail.com> wrote in message
news:ac65fea.0404290420.2156b61f@posting.google.co m...
: I have a question for you, it involves IIS and database connections.
:
: When I got hired, we were using an asp include file with database
functions
: and would open and close the database connection multiple times in 1
script.
:
: Common sense dictated we should only open the connection once, get all
: necessary information, and close it once per script load. We ran some of
: your stress testing software and obviously saw performance increases.
:
: Then I stumbled upon the ability to open a database connection when the
user
: first creates a session and logs into our authentication, and close it
when
: that session expires or the window is closed. This would mean each user
: using the system would connect once and disconnect when they are done.
: Otherwise, each user using the system would be making let's say 5 database
: connections and disconnections per minute, each time they load an asp
page.
: So if each user uses the system for 1 hour, and there are 500 of them,
there
: are 150 000 open and closes.
:
: My Question:
:
: Wouldn't 500 open and closes be LESS stress on the system, by using a
: persistent connection?
:
: Also, then I can run queries on the system tables and tell how many users
: are logged into the application....
:
: I don't know, the persistent connection seems much better to me, easier to
: use and less network traffic, but I've read the opposite, am I missing
: something?

[Bob Barrows [MVP]]

Christian Cooper wrote:

> I have a question for you, it involves IIS and database connections.
>
> When I got hired, we were using an asp include file with database
> functions and would open and close the database connection multiple
> times in 1 script.
>
> Common sense dictated we should only open the connection once, get all
> necessary information, and close it once per script load. We ran
> some of your stress testing software and obviously saw performance
> increases.
>
> Then I stumbled upon the ability to open a database connection when
> the user first creates a session and logs into our authentication,
> and close it when that session expires or the window is closed. This
> would mean each user using the system would connect once and
> disconnect when they are done. Otherwise, each user using the system
> would be making let's say 5 database connections and disconnections
> per minute, each time they load an asp page. So if each user uses the
> system for 1 hour, and there are 500 of them, there are 150 000 open
> and closes.

Not really. Connection pooling (actually called Session pooling in OLEDB)
will minimize the number of actual opens and closes.

>
> My Question:
>
> Wouldn't 500 open and closes be LESS stress on the system, by using a
> persistent connection?

No. ADO is not free-threaded. By using a single connection, stored in
Application or Session, you will serialize all contact with your database.
Plus you will no longer be taking advantage of session pooling to help
minimize the number of open connections on your server.

If you have a desktop application, then the global connection makes more
sense. In a server however, "open late-close early" is the best policy.

Here are some links:
[url]http://www.aspfaq.com/show.asp?id=2053[/url]
[url]http://www.learnasp.com/learn/nodbsession.asp[/url]
[url]http://www.learnasp.com/learn/nosessionobjects.asp[/url]
[url]http://www.learnasp.com/learn/sessionoverview.asp[/url]

HTH,
Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand [MVP]]

> Then I stumbled upon the ability to open a database connection when the

> user
> first creates a session

No, no, no, no, no.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bob Barrows [MVP]]

Christian Cooper wrote:

>> Then I stumbled upon the ability to open a database connection when

> the user first creates a session and logs into our authentication,
> and close it when that session expires or the window is closed.

I meant to comment on this as well. Only two occurences will cause a session
to expire:
1. The session times out
2. A Session.Abandon command is executed

The server does not know when a user closes his browser window, or navigates
to another site, or whatever. This means that in your scheme, connections
will be open much longer than they need to be.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.