1 connection for whole page vs many 1

[earme]

I have an ASP app that connects to a SQL database. Most of my database connections occur at the top of the page, but some of them need to occur farther down the page. I've tried to use the 'Declare late, release early' but this isn't possible sometimes.
So, my question is: is it better to have 1 connection that stays open for use thru the entire page or have many connections that are opened for brief periods of time?
Usually, I have 1 for a majority of the work at the top of the page, and then maybe a max of 8 opened and then closed further down.

Thanks,
Earme

 

[FengShui1998]

earme,

I would think "many connections" would be better. You should end your connection at the end of each web page because you don't know for sure if someone will "back" button to another page, therefore never getting to close your connection. Saves on resources.


fengshui_1998

 

[lobstah]

i agree with fengshui_1998

 

[earme]

Ok, so it's better to have many connections in an asp page that are released quickly rather than once connection for the whole page.

I have another question. Is it cheaper on the server resources to pass a connection to a function or to use a new connection in the function?

Thanks!
Earme

 

[FengShui1998]

earme,

Think of it as making a long distance telephone call. Each time you make a call you are assessed a charge for phone call setup plus each minute thereafter.

Wouldn't you rather just use the same phone call to avoid setup charges?


fengshui_1998

 

[earme]

fengshui_1998,

Thank you!
I'm having a problem with timeouts on our test server and I want to make the load as light as possible for it. The actual server the app will be moved to is much, much more powerful, but I figure that if I make these errors come up less on the less powerful test server it might make things that much more quicker on the powerful real server (and maybe avoid problems in the future).
(I've installed the app on the real server for our clients to test on and we don't receive any errors.)

Thank you!
Earme

 

[Ovatvvon]

I will make another suggestion...use a disconnected recordset! You can connect once to the database, get the info you reqeusted according to you sql statement, disconnect from the database, then use the recordset through out your page without being connected to your database, here's how to do it:

-----------------------------------

<%@ Language=VBScript%>
<%
Option Explicit
Response.Expires = 0
%>
<!-- #include file=&quot;adovbs.inc&quot;-->
<%
Dim conn, connString, rs, sql
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
connString = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;
connString = connString & &quot;DBQ=c:\Inetpub\db\yourDB.mdb;&quot;
conn.Open connString

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.CursorLocation =adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

sql = &quot;SELECT * FROM myTable WHERE ((myTable.Fname)='john');&quot;

rs.Open sql, conn, , , adCmdText
Set rs.ActiveConnection = Nothing ' This disconnects the db connection without destroying the recordset
conn.Close
Set conn = nothing

' Now just work like you were still connected

Do while (Not rs.EOF)
Response.write(rs(&quot;firstName&quot & &quot; &quot; & rs(&quot;lastName&quot & &quot;<BR>&quot
rs.MoveNext
Loop

rs.Close
Set rs = nothing
%>


Hope this helps...should produce your performance greatly with only one connection and it's very short. This type of connection works good when all you have to do is read data from the database but not update or append to it.
-Ovatvvon :-Q

 

[earme]

Ovatvvon,

Thank you! I went through my app and made used disconnected recordsets where ever I could. Things are running much better now. I get hung up much less and it runs faster now. The only place I still occasionally get hung up is on the connection.open statement(which i think is odd).

Thank you!
Earme