|
|
Posted by Gordon on 01/02/08 16:39
On Jan 2, 3:18 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Wed, 02 Jan 2008 15:52:49 +0100, Gordon <gordon.mc...@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 = 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.
Navigation:
[Reply to this message]
|