How often should I close database connections?

Question

Currently, I'm opening a database connection in my app's initialization. It's a fairly small app, PHP if that's relevant.

Should I be connecting to the database, making calls, then closing and repeating this process for each database function I write?

For example, I have the following function which grabs the $db variable from my app's initialization.

function get_all_sections()
{
    global $db;
    $sql = 'select * from sections'; 

    if (!$db->executeSQL($sql, $result))
    {
        throw new Exception($db->getDatabaseError());
        exit();
    }

    $sections = array();

    for ($i = 0; $i < $db->numberOfRows($result); $i++)
    {
        $sections[] = new Section($db->fetchArray($result, MYSQLI_ASSOC));
    }

    return $sections;
}

Would it be better if I opened the connection then closed it after I fetched the rows? That seems like a lot of connections that are opened and closed.

Answer 1

If you have connection pooling on (http://en.wikipedia.org/wiki/Connection_pool) its ok to be grabbing a new connection when you need it. HOWEVER, I'd say to be in the habit of treating any resource as "limited" and if you open the db handle keep it around for as long as possible.

Answer 2

Connecting to the database takes a finite amount of time. It's negligible when connecting over a domain socket or named pipe, but it can be much larger if over a network connection, or worse yet, the open Internet. Leave it connected for the life of the request at least.

Answer 3

Use mysql_pconnect for connection pooling, and close at the end of each operation. The thread won't really close, and the thread will be re-used. This way its both safe and efficient.

Answer 4

Since PHP MySQL connections are fairly light-weight, you are probably OK opening and closing the connection when needed. The same is not true of other database connections, such as connecting to SQL Server, which has rather heavy connections.

In all cases, however, do whatever makes the most sense in terms of maintainability/logic/dependencies. Then, if you find you are feeling a measurable slowdown, you can optimize those areas that need the speed-boost.

When in doubt, follow the golden rule: Don't optimize prematurely.

Answer 5

The simplest solution would be to use mysql_pconnect() - see here

This way if the connection is already open it will use it instead of creating a new one. If it's not it will connect again