Opening/Closing Connections global.asa

[arbpen]

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.

 

[Bob Barrows [MVP]]

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

 

[Mike Brind]

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

 

[arbpen]

Bob Barrows [MVP] wote:

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.

 

[arbpen]

Mike Brind wote:

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.