SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    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.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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.

  3. #3
    SitePoint Guru
    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

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    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.

  5. #5
    SitePoint Guru
    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.

  6. #6
    SitePoint Member
    Join Date
    May 2005
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the responses.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •