Re: prepared statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wednesday 25 June 2008 8:24:06 pm ctx2002 wrote:
> so only benefit for use prepared statement in Web environment is to prevent
> SQL
> injection?
>
> regards

It's somewhat more complicated than that.  (The following is based on my own 
experiences with PDO and a conversation with PDO's original author, Wez 
Furlong.)

A normal query is taken as a single string, parsed, executed, and returned.  
End of story.  A prepared statement is taken as a template string, parsed, 
and cached.  It then has variables passed into it, almost like a function 
call.

Caching the query once tends to cost a little bit more than just executing it 
straight.  The savings come in later calls, when you skip the compilation 
step.  You save per repeated query the amount of the compilation.

As Chris said, you can't save compiled queries between requests.  However, 
they can still be a performance benefit in certain circumstances.  With MySQL 
in particular, those circumstances are weird.

First of all, MySQL's query compiler is actually pretty damned fast to start 
with, especially with the fairly simple queries that tend to predominate in 
web applications.  So if you save the cost of a repeated compilation but the 
compilation itself is already cheap, you won't really notice much 
improvement.  Databases with slower compile engines (eg, Oracle) or queries 
that are considerably more complex (lots of joins that the compiler has to 
figure out how to optimally order, for instance) will see more of a benefit.  
You'll also, of course, see more benefit on queries that are run many many 
many times, but for anything but insert queries if you're smart you're 
already designing your app to avoid repeating the same query anyway. :-)

Secondly, MySQL's native prepared statement support is somewhat braindead, as 
it bypasses the query cache entirely.  So you can have prepared statements or 
the query cache, but not both.  For that reason, you may get better 
performance by using PDO's emulated prepared statements instead.  (Check the 
manual for how to enable that.)  That way PDO will do the prepared statement 
work and MySQL will still use the query cache.  Experiment to see if this 
helps in your case.

Also remember that PDO itself adds some overhead with its database 
abstraction.  It's far less than doing the same abstraction in userspace, but 
it is there.  A straight PDO-based query will not be as fast as, say, the 
same query run directly through ext/mysqli.  However, PDO does give you all 
of the nice things PDO gives you. :-)  

In my testing while working to port Drupal from ext/mysql[i] to PDO, I have 
found that by removing all of our userspace prepared statement code and using 
PDO instead, we're getting about a wash on performance.  We're OK with that, 
however, due to the increased security and flexibility that PDO offers.

So the answer is that PDO is not going to get you a big performance boost on 
MySQL in a typical web app, but you can probably break even on performance 
overall while adding flexibility, additional features, and a nice API.

-- 
Larry Garfield
larry@xxxxxxxxxxxxxxxx

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux