On Fri, Jun 26, 2009 at 2:13 AM, Hans Zaunere<[email protected]> wrote: >> >> Wait, are you advocating //against// prepared statements? >> > >> > Not at all, but when using mysql, you should emulate them. I am >> > actually all for "prepared" style queries, if I ever see >> > "mysqli_real_escape_string" in someone's code, I immediately write the >> > person off as clueless. >> >> What's so clueless about using mysql_real_escape_string()? I would be >> interested to find out. > > Nothing in my opinion. Prepared statements can be a benefit in various > situations, with those benefits being largely dictated by the database in > use. With MySQL, prepared statements often are an actual drag, rather than > benefit. > > Consider: > > http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statements.html > > A key statement being: > > "Prepared execution is an efficient way to execute a statement more than > once." > > Most statements aren't executed more than once in a typical web > application/request/response cycle. > > Cases where I would use prepared statements: > > -- batch processes where the same queries are executed numerous times with > varying data > > -- dealing with large data storage (blobs, etc.) > > H > > > _______________________________________________ > New York PHP User Group Community Talk Mailing List > http://lists.nyphp.org/mailman/listinfo/talk > > http://www.nyphp.org/show_participation.php >
If, and this is an enormous if, you properly build your application around persistent connections, you ought to be able to take advantage of the erm, advantages of preparing your statements. AFAIK, the cache for prepared statements (which I'm not entirely sure MySQL even bothers to build and use, I've heard that it doesn't and after several hours of searching have given up looking for a conclusive answer) exists on a per connection basis, so the persistency of your connection ought to preserve that gain. As a general rule, I use parameterized queries with named parameters (a huge advantage PDO has over MySQLi) in all my queries, because I can afford the extra wait if the query only runs once on that connection and it's far too easy to rely on mysqli_real_escape_string and forget to use it once and have everything come crashing down around you. As far as portability is concerned, one thing I've had some level of success with is storing my queries in XML and fetching them to be prepared, then binding and executing, which makes it a whole hell of a lot easier to write queries for two different databases (in my case, PostgreSQL and MySQL) than try to implement some sort of rewriter, generator, or some other language that gets compiled into SQL (think Doctrine). Regarding have another layer between your code and your database (driver), I would have to disagree (and Hans and I have had a back and forth off list about this very thing). Consider PDO: PDO call -> PDO -> driver -> database Now consider a userland implemented DBAL: DBAL call -> DBAL -> driver -> database They look exactly the same! The primary difference being whether the extra layer of abstraction is implemented in C or in the userland and Common Wisdom has always (as far as I know, once again) suggested that it's very rare that userland code will outperform something with the same functionality written in C. I think the OP's benchmarks agree, but once again, do a few milliseconds really matter that much when the actual query is going to steal most of the time? Probably not, unless you're choking on CPU time, rather than I/O queue, which is very rarely the case. And as far as the native driver thing goes, the difference is how the driver is implemented, but in the end it's the same driver being used. The main argument against PDO that I've heard is that some of the quirks and non-standard behaviors that the various RDBMS's offer isn't handled, but in writing portable apps, consider whether or not using database-specific behavior is really a good idea. I personally don't think it is. --Eddie tl;dr: I'm a PDO fanboy _______________________________________________ New York PHP User Group Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk http://www.nyphp.org/show_participation.php
