What’s best: leave Conn open or close it?
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=14130
Printed Date: 29 March 2026 at 9:19am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: What’s best: leave Conn open or close it?
Posted By: theSCIENTIST
Subject: What’s best: leave Conn open or close it?
Date 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. ------------- :: http://www.mylittlehost.com/ - www.mylittlehost.com |
Replies:
Posted By: theSCIENTIST
Date 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. ------------- :: http://www.mylittlehost.com/ - www.mylittlehost.com |
Posted By: Gullanian
Date 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. |
Posted By: theSCIENTIST
Date 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. ------------- :: http://www.mylittlehost.com/ - www.mylittlehost.com |
Posted By: Mart
Date Posted: 06 March 2005 at 3:18pm
Ahh the joys of ADO.net and managed connection pooling
|
Posted By: theSCIENTIST
Date 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. ------------- :: http://www.mylittlehost.com/ - www.mylittlehost.com |
Posted By: sifra
Date Posted: 21 March 2005 at 12:46am
|
For background, see my reply to your question on how to free recordset and connections.
http://forums.webwiz.net/forum_posts.asp?TID=14073&PN=1&get=last#79387 - http://forums.webwiz.net/forum_posts.asp?TID=14073&PN=1&get=last#79387
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.
|