PDOStatement::prepare overhead

With the move over to PHP 5.x I' mnow writing a lot of code that makes
use of prepared statements as the PHP manual has nothing but good
things to say about them. They have less overhead for the database,
they eliminate the possibility of SQL injection attacks, they are made
of sunshine and fluffy kittens, etc.

Then I got to thinking, in my methods I am preparing a statement and
then running it one to n times, depending on the method and what its
doing. But of course when you go out of scope in a method you destroy
the PDOStatement that you prepared. Of course it gets recreated when
you next run the function, but then what happens whtn the prepare()
statement is run again?

I'm working with Postgres so I assume the PDO driver is using the
prepared statement support already built into Postgres as opposed to
the PDO emulation layer. So what I want to know is what happens when
you prepare the same statement more than once, with different
PDOStatement objects? Will the entire query preparation process run
again or does the prepared statement get cached server side? From the
point of view of program logic it makes very little difference whether
or not the statement is prepared over from scratch, but I would
imagine that there is a performance price to be paid that would
totally negate the performance advantage of prepared statements for
queries that are only run once.

The reason I am asking this is to determine whether or not it is worth
the effort to modify my database querying classes so they store all
prepared statements in static variables? Doing so would allow me to
initialize each prepared statement as needed and only do it once per
page view, but it would require a fair amount of rewriting, a day or 2
at least. I'm wondering if the performance benefit would be enough to
justify the work involved.

Any comments you guys have would be appreciated.

Re: PDOStatement::prepare overhead

On Wed, 02 Jan 2008 15:52:49 +0100, Gordon =

wrote:

> With the move over to PHP 5.x I' mnow writing a lot of code that makes=

> use of prepared statements as the PHP manual has nothing but good
> things to say about them. They have less overhead for the database,
> they eliminate the possibility of SQL injection attacks, they are made=

> of sunshine and fluffy kittens, etc.
>
> Then I got to thinking, in my methods I am preparing a statement and
> then running it one to n times, depending on the method and what its
> doing. But of course when you go out of scope in a method you destroy=

> the PDOStatement that you prepared. Of course it gets recreated when
> you next run the function, but then what happens whtn the prepare()
> statement is run again?
>
> I'm working with Postgres so I assume the PDO driver is using the
> prepared statement support already built into Postgres as opposed to
> the PDO emulation layer. So what I want to know is what happens when
> you prepare the same statement more than once, with different
> PDOStatement objects? Will the entire query preparation process run
> again or does the prepared statement get cached server side? From the=

> point of view of program logic it makes very little difference whether=

> or not the statement is prepared over from scratch, but I would
> imagine that there is a performance price to be paid that would
> totally negate the performance advantage of prepared statements for
> queries that are only run once.
>
> The reason I am asking this is to determine whether or not it is worth=

> the effort to modify my database querying classes so they store all
> prepared statements in static variables? Doing so would allow me to
> initialize each prepared statement as needed and only do it once per
> page view, but it would require a fair amount of rewriting, a day or 2=

> at least. I'm wondering if the performance benefit would be enough to=

> justify the work involved.
>
> Any comments you guys have would be appreciated.

An simplified excerpt of my DB class (which is a Singleton BTW, not =

unimportant for this):

class DBint extends PDO{
...
private $stmtcache =3D array();
...
function prepare($strStmt,$arrOptions =3D array()){
if(!isset($this->stmtcache[$strStmt]) || =

!is_object($this->stmtcache[$strStmt])){
$this->stmtcache[$strStmt] =3D parent::prepare($strStmt,$arrOptions);=

}
return $this->stmtcache[$strStmt];
}
...
function removeStmtCache($strStmt){
if(isset($this->stmtcache[$strStmt])) unset($this->stmtcache[$strStmt]=
);
}
}

Implementing this was for me close to no work, as all prepare() calls we=
re =

guaranteed to run through this class. It requires a little more memory, =
=

but in that project it was clearly faster. Wether or not you can impleme=
nt =

it like this, and wether your application would benefit from it, will =

depend highly on the actual code used. If you estimate the work to take =
2 =

days, and things are fine now (no stretching for resources, fast respons=
e =

times), I wouldn't 'fix' it (after all, what is there to fix?), but mayb=
e =

put the idea in comments/documentation for you & future developers.
-- =

Rik Wasmus

Re: PDOStatement::prepare overhead

On Jan 2, 3:18 pm, "Rik Wasmus" wrote:
> On Wed, 02 Jan 2008 15:52:49 +0100, Gordon
> wrote:
>
>
>
> > With the move over to PHP 5.x I' mnow writing a lot of code that makes
> > use of prepared statements as the PHP manual has nothing but good
> > things to say about them. They have less overhead for the database,
> > they eliminate the possibility of SQL injection attacks, they are made
> > of sunshine and fluffy kittens, etc.
>
> > Then I got to thinking, in my methods I am preparing a statement and
> > then running it one to n times, depending on the method and what its
> > doing. But of course when you go out of scope in a method you destroy
> > the PDOStatement that you prepared. Of course it gets recreated when
> > you next run the function, but then what happens whtn the prepare()
> > statement is run again?
>
> > I'm working with Postgres so I assume the PDO driver is using the
> > prepared statement support already built into Postgres as opposed to
> > the PDO emulation layer. So what I want to know is what happens when
> > you prepare the same statement more than once, with different
> > PDOStatement objects? Will the entire query preparation process run
> > again or does the prepared statement get cached server side? From the
> > point of view of program logic it makes very little difference whether
> > or not the statement is prepared over from scratch, but I would
> > imagine that there is a performance price to be paid that would
> > totally negate the performance advantage of prepared statements for
> > queries that are only run once.
>
> > The reason I am asking this is to determine whether or not it is worth
> > the effort to modify my database querying classes so they store all
> > prepared statements in static variables? Doing so would allow me to
> > initialize each prepared statement as needed and only do it once per
> > page view, but it would require a fair amount of rewriting, a day or 2
> > at least. I'm wondering if the performance benefit would be enough to
> > justify the work involved.
>
> > Any comments you guys have would be appreciated.
>
> An simplified excerpt of my DB class (which is a Singleton BTW, not
> unimportant for this):
>
> class DBint extends PDO{
> ...
> private $stmtcache = array();
> ...
> function prepare($strStmt,$arrOptions = array()){
> if(!isset($this->stmtcache[$strStmt]) ||
> !is_object($this->stmtcache[$strStmt])){
> $this->stmtcache[$strStmt] = parent::prepare($strStmt,$arrOptions);
> }
> return $this->stmtcache[$strStmt];
> }
> ...
> function removeStmtCache($strStmt){
> if(isset($this->stmtcache[$strStmt])) unset($this->stmtcache[$strStmt]);
> }
>
> }
>
> Implementing this was for me close to no work, as all prepare() calls were
> guaranteed to run through this class. It requires a little more memory,
> but in that project it was clearly faster. Wether or not you can implement
> it like this, and wether your application would benefit from it, will
> depend highly on the actual code used. If you estimate the work to take 2
> days, and things are fine now (no stretching for resources, fast response
> times), I wouldn't 'fix' it (after all, what is there to fix?), but maybe
> put the idea in comments/documentation for you & future developers.
> --
> Rik Wasmus

Thanks for that, I'm looking your method over now to get to grips with
how it works, though it seems pretty simple. I already have a PDO
extending method but at the moment all that does is allow nested
transactions by adding transaction counting to beginTransaction () and
commit () / rollback (). I guess I really just need to remind myself
that you can extend built in objects as well as your own from time to
time :) Can be easy to forget that sometimes.