PDO: Isn't it slow!

[Big_Pat]

I converted my site from MySQL to PDO (thanks to some help I received in here!) and all is fine..but my goodness, isn't PDO sl-o-o-o-ow in comparison! I'm not sure I'd want to put my site up on the net with that sort of reaction time.

[requinix]

isn't PDO sl-o-o-o-ow in comparison

Not really. What kind of code are you noticing is slow? How are you constructing the PDO object and are you using any settings?

[Big_Pat]

Really? It's an awful lot slower for me, locally. To be fair, I'm not using objects at all, I'm just doing SQL queries and then formatting or doing whatever I do with the results after. Clearly I should have chosen MySQLi instead, but I wanted to teach myself some things about PDO for future-proofing.

[kicken]

Really? It's an awful lot slower for me, locally.

Then your doing something wrong. PDO is not noticeably slower than using mysql_* or MySQLi. Without you showing us your code though, there isn't anything we can do to help you.

[KevinM1]

Are you running queries in loops?  Because that will bring any system to its knees.

[jazzman1]

I read before lots of posts of different forums on the web, where their "experts" said that PDO and MySQLi drivers are mutch more slower than MySQL one - it's not really true.

 

As kicken said - you're doing somethig wrong in your script.

Edited May 21, 2013 by jazzman1

[Eiseth]

try using persistent connection http://php.net/manual/en/pdo.connections.php

[Big_Pat]

I'll try that, Eiseth, thanks.

There are no looped queries, just standard 'grab the data I want and print it out' ones. I close the connection after each one ($connection=null;). My functions.php fil's quite big - 600 lines - but that's no problem with MySQL.  

[requinix]

You "close" the connection after each query? No no. Leave it open and let PHP close it for you. It's quite possible that PDO has coding where garbage collection frees the underlying connection while mysql's coding doesn't (especially considering how it's not object-oriented).

[mac_gyver]

based on your other recent thread about including a file containing your connection, you are apparently now calling a 'connect_db' function inside of each of your database related functions to open a connection and based on post #8 in this thread, closing that connection after you use it each time.

 

that's most definitely NOT how to manage a database connection in your application. creating a database connection takes a significant amount of time, so of course your code is noticeably slow.

 

the correct method is to create one database connection in your application code and pass it into each function that uses it. you would normally pass this in as a call time parameter, in the same way that the procedural mysqli_ functions accept the mysqli connection as the first call-time parameter in any function that needs it. there are other methods to pass a database connection object into functions, but they are not general purpose.

[Big_Pat]

Right-o, I'll try that when I get home. I was only closing the connection because I'd read that it was good practice to do so.

[mac_gyver]

I was only closing the connection because I'd read that it was good practice to do so.

 

practices are only good if they make sense in the context where you are using them. blindly applying practices in all situations, can lead to bad results.

[Big_Pat]

So you're saying, then, that this code is inefficient, being that this is just one of quite a number of queries in my functions.php file.

$connection = connect_db(); //this is where I call the new PDO connection
    $album     = mysql_prep(rawurldecode($album));
    $performer = mysql_prep(urldecode($performer));
    $query  = $connection->prepare ("select title, length, year, album_artist, artist, orig_artist, track_number, label, coverpic, rating
                from songlist
             where album = :album and album_artist = :performer order by track_number asc");
    $query->bindParam(':album', $album);
    $query->bindParam(':performer', $performer);
    $query->execute();
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {

but I'm not sure how to call the connection (as a call time parameter) any other way. I've tried putting the connection into my header and leaving it open, but that hasn't worked. Sorry if this is a basic question.

[requinix]

It's a good albeit generally unnecessary practice to close it when you're completely done. Not after every query.

 

1. What's mysql_prep() doing? I'm not too confident you need it.

2. That's basically what your code should look like. Is connect_db() returning a new PDO object every time? That's the inefficient part. Create a PDO object the first time and reuse that every subsequent time; the Singleton pattern is typically how that happens.

3. I did find this that explicitly says the connection is closed once the object goes away (eg, with $connection=null).

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

 

Example #3 Closing a connection

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here


// and now we're done; close it
$dbh = null;
?>

 

[Big_Pat]

Thanks for the reply. I'd forgotten all about mysql_prep, actually (a little function that checks which version of PHP and magic_quotes, etc). I commented it out and re-tested, but it's still slow. Your second point, though, I think must be the reason. I am creating a NEW PDO and referring to that with my function call connect_db(). I'm afraid I know no other way - what do I do to call a NEW PDO the first time, but not subsequently?

[requinix]

You can modify connect_db() so it always returns the same object each time it's called.

function connect_db() {
	static $connection = null;
	if (!$connection) {
		$connection = new PDO(/* ... */);
		// and whatever else
	}
	return $connection;
}

That bit with static $connection (so PHP "remembers" $connection across calls) makes this a singleton.

 

Simply reusing $connection might be enough, but if that variable ever drops out of scope (ie, you use it inside a function or class method) then it'll get cleaned up.

 

On the down side this doesn't make it easy to close the connection, should you want to do that manually at some point. Like if you have a long-running script that only needs the database a little bit early on. If that case arises then you should switch to a full class implementation instead of the simple function above.

[Big_Pat]

Thank you for the explanation. I'd seen the term singleton but wasn't sure what it referred to. I've amended my connection.php and it's definitely quicker than before. I still wouldn't say it was anywhere near as fast as MySQL but it's not awkwardly slow any more. I really appreciate the time taken by all here in replying.