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.