Performance Question: Keep DB connect open for longer?

[Ackkster]

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.

[longneck]

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.

[briansol]

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

[longneck]

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.

[briansol]

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.

[Ackkster]

thanks for the responses.