Re: prepared statement

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@garfieldtech.com