| Author |
 Topic Search  Topic Options
|
theSCIENTIST 
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: What’s best: leave Conn open or close it?
Posted: 06 March 2005 at 11:49am |
|
Hi guys, what would be best, leave the connection to the database open, so it can be reused as people navigate throught the site, or close it and open it all the time?
Is it just me, or closing it and openning it all the time is wasteful and suffers from latency as the server is not reusing connetions, so it has to assign new memory and PIDs to each new connection?
Now, recordsets, should be closed as soon as possible, because chances are that the next page will NOT be requesting the same data, but a connection is different in that respect isn't it?
Thank you.
|
|
|
 |
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2005 at 11:52am |
|
Humm, just remembered something, connections are most likely pooled and limited so having one Conn assigned to a user so he can reuse it, will most likely make the pool to run out of Conns on high traffic sites.
Still I would love to hear from you all about this subject.
|
|
|
|
Gullanian
Senior Member
Joined: 04 January 2002
Location: England
Status: Offline
Points: 4373
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2005 at 12:45pm |
|
I'm no expert on the subject, but with the hyper text transfer
protocol, it works slightly differently to other protocols in that it's
sort of temporary, as soon as it stops sending data it closes.
In an ASP page you should always make sure all connections are closed,
and all objects set to nothing when the page has finished executing,
otherwise the objects all still exist in memory on the server when the
HTTP request has finished.
Every page you load, if it needs a database connection it should create
new objects, open all connections then close the connections. The
most efficient way to design a page as I understand it when it only
connects to one database is to only open the database connection once
on the page. Don't repeatedly open and close it.
I hope this answer helped and it's relevant to your question.
|
|
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2005 at 3:04pm |
|
Yes, the common knowledge has been (open > use > close) but wouldn't that make the server work harder and slower, because it has to open and close a few dozen conns per couple of minutes as a user is going from page to page? It can also generate memory fragmentation, because of all the allocation chunks.
This must've already been researched, but doesn't it make more sense from a server point of view, now, get yourself on the server shoes (:D) a user connects to you and requests a database conn, you grant it, then you process the query, the user spends a few seconds digesting the results, and if the conn would still be open, when the user requests more stuff, you the server would go: another conn from that same user, ohh, the conn is already open, it's even to the same database, great, lets just process the query.
Wouldn't this be faster? I don't know, could just be me thinking out loud.
Who out there does have real data on the subject, I would love to get my eyes on it.
|
|
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 06 March 2005 at 3:18pm |
Ahh the joys of ADO.net and managed connection pooling 
|
|
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 08 March 2005 at 7:57pm |
|
Yes, ADO.net, I'm not far way from it, I'm finishing my last big ASP project, and then will move to .NET, but in the mean time, I'm still with ASP.
I guess, perhaps, closing the connection and opening it all the time is the best we have.
|
|
|
|
sifra
Newbie
Joined: 20 March 2005
Location: United States
Status: Offline
Points: 4
|
Post Options
Thanks(0)
Quote Reply
Posted: 21 March 2005 at 12:46am |
For background, see my reply to your question on how to free recordset and connections.
If using SQL Server, if you have access to Query Analyzer, or SQL Profiler, you can sniff the connections in use.
You will see that even if you do not close your connections explicitly, as soon as the page goes out of scope, the connections are free for reuse by other pages using the same connection string.
To verify the connections are being reused and not orphaned or removed, watch the LastBatch column using sp_who2 in QueryAnalyzer, or look for sp_resetConnection using Profiler RPCCompleted event.
All that being said should give you a warm fuzzy, but it still behooves us to open the connection as late as possible and free it as early as possible, because the page request often outlives the connection's usefulness to a single user.
Since connection pooling is in use, (even on MSDE, the freeware light version of SQL Server) freeing the connection as early as possible will keep the number of connections that have to be maintained in the pool to a minimum.
Hope this helps.
|
|
