whats more efficient and why: one db connection pe

Question

I am working on a website which is very MySQL DB driven. So I have a lot of queries going on.

In this topic everyone recommends to connect to the DB at the top of the page, and disconnect at the bottom of the page.

I am wondering what's more efficient, or generally speaking best practice: Make a single db connection per page, or only connect as needed? (Or is there no general answer, and it depends?)

Additionally I am looking to find out WHY is this best practice, from which point of view are you looking at the scenario (e.g. security, speed, ... I don't know what else DB connections might affect?!)

I believe this question has been asked before here - but not for PHP in specific, and therefore I didn't find it helpful.

My current practice has been to connect to the DB per mysqli for each function I write, and disconnect at the end of the function, because it seemed cleaner to me. This way, if a page doesn't call to a function which requires DB access, there will never be a connection opened. However it may happen, that there might be up to approximately 10 connections per page load, depending on what the user does on the site. Now I thought this might be a fair distribution of resources. If I understood it correctly there can only always be 1 DB connection opened. Therefore I assume all connection requests will be queued. So if a user has multiple, long and complicated queries, this user would not hold up all traffic, because in between each of the queries, other short queries could get processed. But that's just me making stuff up, I don't know if it would really work that way... :D

Also I know that a lot of developers around here like to use PDO. I chose to use mysqli when I started developing, and I have no plans of switching. I hope my question can be applicable to both libraries.

Thanks :-)

Answer 1

I'd suggest you consider using a connection factory pattern. This will allow you to only call the factory in functions that need it (avoiding the overhead of a connection if a connection is never needed) while allowing you to reuse a connection if you have previously made a connection (avoiding the overhead of repeatedly constructing and deconstructing connections).

Perhaps a connectionFactory.php included in your pages, or available via yer loader like this.

class ConnectionFactory{

private static $factory;
public static function getFactory(){
    if (!self::$factory){
        self::$factory = new ConnectionFactory();
        $this->db = null;
    }
    return self::$factory;
}

private $db;

public function getConnection(){
    if (is_null($this->db))
        $this->db = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
        if ($this->db->connect_error){
            throw new Exception("Connect Error ("
                . $this->db->connect_errno
                . ") "
                . $this->db->connect_error
        );
    }
    return $this->db;
}

public function closeConnection(){
   if (! is_null($this->db)){
       $this->db::close();
       $this->db = null;
   }
}

}

You can then use it later in a function:

function doStuff(){
try{
    $conn = ConnectionFactory::getFactory()->getConnection();
}catch (Exception $e){

}

}

This ensures you never incur overhead if you don't use a connection at all, and if you do, you can then leverage the connection.

devzone.zend.com says: "Open connections (and similar resources) are automatically destroyed at the end of script execution."

So you don't have to explicitly close the connection. However, there are perhaps times where it is desirable to do so for performance reasons. That will be dependent on the context you are running in, and you will have to balance that yourself as you look at the context.

You might also look at the similar Global or Singleton for database connection?

NOTE: I've not tested any of this code, it is meant to be a possibly working example. ;-)

Answer 2

Typically database connections are expensive to create. This is why most people recommend creating the connection once and reuse it until the execution has stopped, or even longer if the database client library allows it.

As an example, PDO permits creating persistent connections, which supposedly enhance performance because the connection would be reused for serving several requests in a row. From http://php.net/manual/en/pdo.connections.php:

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

Answer 3

one db connection per page or one db connection per function?

one per page

WHY is this best practice,

speed and common sense

If I understood it correctly there can only always be 1 DB connection opened.

Wrong. The only limit can be set at DB side. And there is always a pool.

Make a single db connection per page, or only connect as needed?

What stops you from connecting once but only when needed? Make your db function autoconnect if there is no opened connection and reuse it if exists. Though I don't find it worth the mess for the "very MySQL DB driven" site.