SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
-
Jan 27, 2006, 13:55 #1
- Join Date
- May 2005
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Performance Question: Keep DB connect open for longer?
Hello all, I was wondering if anyone has any info on which method of opening/closing DB connects is more efficient from a performance standpoint:
Method 1 - Keeping ONE DB connect open for entire page processing.
for instance, if i have a PHP file, my code would look like this:
db->open();
..
..
code for entire php file
..
..
db->close();
Method 2 - Multiple opening/closing of DB connects at relevant page sections
Instead of keeping one DB connect open for the entire page processing, only open them and close them right before necessary calls to the db, i.e.
db->open();
..
php db handling/queries for this section of code
..
db->close();
..
..
more code
..
db->open();
..
php db handling/queries for this section of code
..
db->close()
..
..
Tradeoffs I can see:
- With method 1, you don't need to open as many connects/close as many connects to the DB. However, the connects are open longer and might not be doing anything most of the time.
- With method 2, connects are only open right when you need them. But then there is the overhead of having to open/close multiple connects within a relatively short time span.
Anyone have any thoughts on which method is more efficient? I guess it all depends on the overhead cost of establishing a connection....I'm using postgresql btw.
-
Jan 27, 2006, 14:59 #2
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
setup and teardown is resource intensive, more so if the db server is not on the same box as the web server. i suggest one conneciton all the way through.
-
Jan 28, 2006, 16:50 #3
- Join Date
- Aug 2003
- Location
- CT
- Posts
- 643
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
the only problem with leaving it open, is if the process you're running sets a lock on the table. if you don't have a lock issue, leave it open till you're done
-
Jan 28, 2006, 20:21 #4
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
there is almost never a good reason to use locks. if you find yourself using locks, then you need to think about switching to transactions.
-
Jan 29, 2006, 16:09 #5
- Join Date
- Aug 2003
- Location
- CT
- Posts
- 643
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I totally agree. Unfortunately, there's a LOT of 3rd part software that uses locks. For example, at my office, our dialer software creates a lock on the record it is calling and then has an update queue. It's horrible. Sometimes we have lag times of a couple hours.
-
Jan 30, 2006, 02:03 #6
- Join Date
- May 2005
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thanks for the responses.
Bookmarks