PHP PostgreSQL open/close connection performance

Question

I am new in programming (especially in PHP). I write simple PHP site. I also use PostgreSQL database. Here is my little class for work with database.

class dbWorker {

    private static $conString = "host=localhost port=5432 dbname=myDB user=postgres password=root";

    public static function execute($sql) {
    $db_conn = pg_connect(self::$conString) or die('Could not connect: ' . pg_last_error());
    pg_query($sql) or die('Query error: ' . pg_last_error());
    pg_close($db_conn);
    return;
    }

    public static function queryOne($sql) {
    $allData = self::queryAll($sql);
    if ($allData) {
        return $allData[0];
    }
    return null;
    }

    public static function queryAll($sql) {
    $db_conn = pg_connect(self::$conString) or die('Could not connect: ' . pg_last_error());
    $qu = pg_query($db_conn, $sql) or die('Query error: ' . pg_last_error());


    $retval = [];
    while ($data = pg_fetch_object($qu)) {
        $retval[] = $data;
    }


    pg_free_result($qu);
    pg_close($db_conn);

    if (!empty($retval)) {
        return $retval;
    }

    return null;
    }

}

It is quite convenient using this. But when I use this class 10-20 times on page - page loading time takes for about 5-6 sec. Then I removed the following lines:

   pg_free_result($qu);
   pg_close($db_conn);

After that, page loading time became 161 milliseconds.

The question is - how better organize this process.

Should I write something like this?

dbWorker::open_connection();
...
all my functions calls (like getComments(), getMessages(), getTasksList() ect.)
...
dbWorker::close_connection();

For now I am novice and I am searching simple solution. (Thanks in advance for your help and excuse me for my english)

Yes, there's no point in opening and closing a connection several times in the one page. I don't know if there is any value in explicitly closing the connection either, if this is a web page. — halfer
– halfer, Commented Jan 28, 2015 at 17:59
First, why do want to open and close your database connection many times for a single page? It only causes performance issues. Second, always use a connection pool like pgBouncer or pgPool to increase performance and scalability. — Frank Heikens
– Frank Heikens, Commented Jan 28, 2015 at 18:03

trogers1884 · Accepted Answer · 2015-01-28 18:03:56Z

3

I would create the connection once, perform all your sql queries , then close the connection.

You might be tempted to create persistent connections but there's quite a body of discussion that implies that's probably not a great idea, which I agree with.

answered Jan 28, 2015 at 18:03

trogers1884

1628 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Heikens Over a year ago

Persistency should be handled by the connection pool, not by the application.

Dmitry Novice Over a year ago

In that simple case persistent connection do not needed I think.

Collectives™ on Stack Overflow

PHP PostgreSQL open/close connection performance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related