Opening/Closing Connections global.asa

I am concerned about performance. Usually, in a situation where I am
not using a global.asa (shared host, etc), I explicitly close the
connection at the bottom of the page in a footer, eg: conn.Close set
conn = nothing

Now, I am working with a global.asa file, and the following:
sub Application_OnStart
conString = "driver={sql server}......"
Application("conString")=conString
set Conn = Server.CreateObject("ADODB.Connection")
conn.Open conString
set Application("conn")=conn
end sub

sub Application_OnEnd
Application("conn").close
set Application("conn")=Nothing
end sub

Should I still be explicitly closing the connection at the end of each
page, or just at the end of the Application? The database (MS SQL
2000) is very large.

TIA for any advice.

--
Adrienne Boswell at work
http://atlas.nextblock.com/files/
Please respond to the group so others can share

Bob
Wed May 17 14:56:37 CDT 2006

arbpen wrote:
> I am concerned about performance. Usually, in a situation where I am
> not using a global.asa (shared host, etc), I explicitly close the
> connection at the bottom of the page in a footer, eg: conn.Close set
> conn = nothing
>
> Now, I am working with a global.asa file, and the following:
> sub Application_OnStart
> conString = "driver={sql server}......"
> Application("conString")=conString
> set Conn = Server.CreateObject("ADODB.Connection")
> conn.Open conString
> set Application("conn")=conn

:-) You haven't been paying attention, have you?
It is a bad idea to store an apartment-threaded COM object, such as an ADO
connection object, in Application or Session.
http://www.aspfaq.com/show.asp?id=2053

Store the connection string in Application. Use it in each page (use an
include file) to open a connection in the page. ADO Session pooling
(http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp) will keep
the number of connections opened to a minimum, as well as minimizing the
time used to create the connections on each page. Close and destroy the
connection on each page as soon as you are finished with it, allowing it to
go back into the pool for the next page to use.

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.

Mike
Wed May 17 15:39:42 CDT 2006


arbpen wrote:
> I am concerned about performance. Usually, in a situation where I am
> not using a global.asa (shared host, etc), I explicitly close the
> connection at the bottom of the page in a footer, eg: conn.Close set
> conn = nothing
>
> Now, I am working with a global.asa file, and the following:
> sub Application_OnStart
> conString = "driver={sql server}......"
> Application("conString")=conString
> set Conn = Server.CreateObject("ADODB.Connection")
> conn.Open conString
> set Application("conn")=conn
> end sub
>
> sub Application_OnEnd
> Application("conn").close
> set Application("conn")=Nothing
> end sub
>
> Should I still be explicitly closing the connection at the end of each
> page, or just at the end of the Application? The database (MS SQL
> 2000) is very large.
>
> TIA for any advice.

In addition to what Bob said, you will also improve performance by
using the OLEDB driver instead of the ODBC one you are using at the
moment.
http://www.aspfaq.com/show.asp?id=2126

--
Mike Brind

arbpen
Wed May 17 16:00:14 CDT 2006


Bob Barrows [MVP] wote:
> arbpen wrote:
> > I am concerned about performance. Usually, in a situation where I am
> > not using a global.asa (shared host, etc), I explicitly close the
> > connection at the bottom of the page in a footer, eg: conn.Close set
> > conn = nothing
> >
> > Now, I am working with a global.asa file, and the following:
> > sub Application_OnStart
> > conString = "driver={sql server}......"
> > Application("conString")=conString
> > set Conn = Server.CreateObject("ADODB.Connection")
> > conn.Open conString
> > set Application("conn")=conn
>
> :-) You haven't been paying attention, have you?

Oh, contrair, mon ami! I have definately been paying attention, hence
the question.

> It is a bad idea to store an apartment-threaded COM object, such as an ADO
> connection object, in Application or Session.
> http://www.aspfaq.com/show.asp?id=2053
>
> Store the connection string in Application. Use it in each page (use an
> include file) to open a connection in the page. ADO Session pooling
> (http://msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp) will keep
> the number of connections opened to a minimum, as well as minimizing the
> time used to create the connections on each page. Close and destroy the
> connection on each page as soon as you are finished with it, allowing it to
> go back into the pool for the next page to use.
>

Thank you for the quick reply. I am going to change this right away.

--
Adrienne Boswell at work
http://atlas.nextblock.com/files/
Please respond to the group so others can share

arbpen
Wed May 17 16:08:06 CDT 2006


Mike Brind wote:
> arbpen wrote:
> > I am concerned about performance. Usually, in a situation where I am
> > not using a global.asa (shared host, etc), I explicitly close the
> > connection at the bottom of the page in a footer, eg: conn.Close set
> > conn = nothing
> >
> > Now, I am working with a global.asa file, and the following:
> > sub Application_OnStart
> > conString = "driver={sql server}......"
> > Application("conString")=conString
> > set Conn = Server.CreateObject("ADODB.Connection")
> > conn.Open conString
> > set Application("conn")=conn
> > end sub
> >
> > sub Application_OnEnd
> > Application("conn").close
> > set Application("conn")=Nothing
> > end sub
> >
> > Should I still be explicitly closing the connection at the end of each
> > page, or just at the end of the Application? The database (MS SQL
> > 2000) is very large.
> >
> > TIA for any advice.
>
> In addition to what Bob said, you will also improve performance by
> using the OLEDB driver instead of the ODBC one you are using at the
> moment.
> http://www.aspfaq.com/show.asp?id=2126
>

Thank you, Mike, I reading the relavent article and will make changes.

--
Adrienne Boswell at work
http://atlas.nextblock.com/files/
Please respond to the group so others can share