[thelist] (ASP) Connection time measurement

[Wade Armstrong]

Well, no less than Microsoft suggests you don't do what the DBA wants:
http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/dnasp/html/
ASPtips.asp
(look at Tip 5: Do Not Cache Database Connections in the Application or
Session Objects)
Basically, IIS is smart - it'll try to use connections you've opened and
closed in the most efficient way possible. By opening and closing
connections as you use them, you release the connections to IIS's connection
pooling, which then reallocates them optimally.

Storing the connection in the Application object is the most efficient from
the database server's perspective, because it only opens one connection.
It's also the least efficient from the Web server's perspective, both
because there's only one connection for all the queries to use and also
because of the Application object's threading model. So, if not stressing
the database is the most important thing, then sure, slow down your Web
server and make it less stable by storing the connection in the Application
object.

Storing the connection in the Session object is probably the least efficient
approach from the database server's perspective, because it creates one
connection for each user even if the user doesn't need it - connection
pooling should create a smaller number of connections and allocate them more
efficiently. It's also inefficient from the Web server's perspective, again
because of the threading model of the Session object. There's also problems
with closing of connections because of the unreliability of the
Session.OnEnd event.

Opening connections late and closing them early, as is recommended by
everyone everywhere, is the most efficient all-around, because it takes
advantage of connection pooling and stresses the Web and db server only as
much as is necessary.

Wade


on 5/16/02 11:53 AM, Paulo Guedes at ramone at amazoniacelular.com.br wrote:

> Yeah, that's what I've learned. But I don't know exactly why...
>
> The DBA has asked me "you mean every page will create a new connection
> to 'my' database? why can't you create just one connection for all users
> of your application?"
>
> I haven't found a good answer...
>
> /Paulo Guedes
>
> -----Original Message-----
> From: Tab Alleman [mailto:Tab.Alleman at realmetros.com]

> Everything I've ever read on the subject has said DON'T put connection
> objects in Global.asa or in Sessions!
>
> -----Original Message-----
> From: Amazon Paulo [mailto:Amazon.Paulo at amazoniacelular.com.br]
>
> A friend has suggested to stop opening a new connection at each page. I
> don't know whether this is a good idea to open the connection in
> global.asa... or put it in a session... I'm not sure how to do it, if
> it's possible. Could you help me?