DB Connection with Application/Page Scope

[Hazzardusa]

if a website is getting 100+ DB connection and recordset calls a minute, would it be better to set up 1 application level database connection strings and about 10 application level recordset objects that would handle 90% of the traffic? or would it be better to just open the database everytime i need it.

[mopacfan]

I would think that having appllication level connections could get confused between which pages are calling them. I would recommend opening and closing the connection on each page. I've run some pretty high traffic sites and never had a problem with the database connections being set up on each page.

[Hazzardusa]

if i only had one connection string used in the whole website and there were 10 or so recordsets being called every few seconds.

i am already going to set up the recordsets as application veriables because they are the same for everyone no matter when you call them

i am still trying to figure out how to set up a connection or a recordset inside the application onstart section of the global.asa file
i read that you are supposed to use the object tag but i can never find an example of it in the asa file and this doesn't work

Sub Application_OnStart
<object id="connection" runat="server" progid="ADODB.Connection">
</object>
End Sub

and neither does this

Sub Application_OnStart
Set Application("cn ")= Server.CreateObject("ADODB.Connection")
Application("cn").Open Application("Data_Server")
End Sub

any ideas?

[J.D.]

if a website is getting 100+ DB connection and recordset calls a minute, would it be better to set up 1 application level database connection strings and about 10 application level recordset objects that would handle 90% of the traffic? or would it be better to just open the database everytime i need it.Unless you have very specific requirements and you know what you are doing, it's a bad idea to implement your own connection pooling. Windows supports connection pooling on the driver level (ODBC or OLEDB) and you just need to make sure it's turned on on your system. Here's more info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

J.D.

[heyman]

make sure you use the latest OleDb providers. If you are not using ASP.NET - then I will recommend you use it. Within ASP.NET use SqlClient to connect to your Sql database. It is very scalable and handles connection pooling very nicely