|
|
Posted by Rik Wasmus on 01/02/08 15:18
On Wed, 02 Jan 2008 15:52:49 +0100, Gordon <gordon.mcvey@ntlworld.com> =
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
[Back to original message]
|