tag:blogger.com,1999:blog-9144505959002328789.post2956693581031294843..comments2024-03-27T08:50:58.451+01:00Comments on Karlsson on databases and stuff: Prepared statements - Are they useful or not?Karlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-9144505959002328789.post-50481100699243592712010-07-08T07:37:07.408+02:002010-07-08T07:37:07.408+02:00Ismith: MySQL optimizer cannot build generic plans...Ismith: MySQL optimizer cannot build generic plans. So it has to re-optimize the query for each new set of parameters.<br />Only certain "safe" transformations (like conversion outer join operations into inner join operations) are done once and forever. BTW some of them are performed at the compilation of the prepared statement, the others are done at the first execution.<br /><br />Karlsson: I would recommend you to ask one of your colleagues at Oracle Konstantin Osipov. the author of the MySQL PS code, how MySQL prepared statement are compiled and what is actually done at their execution. You'll see that the picture of PS you drew is quite inexact.<br /><br />Regards,<br />Igor Babaev, Monty Program ABigorhttps://www.blogger.com/profile/06961843646062810762noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-56483597493603974802010-07-07T07:20:27.982+02:002010-07-07T07:20:27.982+02:00lsmith: That script languages that are often used ...lsmith: That script languages that are often used with MySQL has limited capabilities of dealing with statement handles is hardly an issue with MySQL, but rather with those languages, although I do get your point in the general sense. As for network roundtrips being an issue, that is possibly true in some cases, but my testing shows that there actually IS a benefit, and that it is quite relevant, in the 30 - 50% range for a SELECTs that are reused often.<br /><br />Stewart: As for memory management on the Client, I couldn't agree with you more, but this is no different for prepared statements than for the non-prepared statement C API / protocol. That resultsets are materialized on the Client is hardly optimal.Karlssonhttps://www.blogger.com/profile/04874338187076980133noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-37112384548952070412010-07-07T06:54:31.100+02:002010-07-07T06:54:31.100+02:00of course, the implementation in MySQL makes them ...of course, the implementation in MySQL makes them not only completely useless (server side prepared statements) but actively harmful.<br /><br />Outsourcing server memory management to the client is just not a good idea.Stewart Smithhttps://www.blogger.com/profile/00989044306462002000noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-57576927184332862612010-07-07T00:28:39.170+02:002010-07-07T00:28:39.170+02:00well actually i think SELECTs are a bad use case f...well actually i think SELECTs are a bad use case for prepared statements in MySQL. for one there is the additional network round trip. worse yet the chances o being able to reuse a prepared statements are low to non existant for the bulk of MySQL apps since they are written in script languages that do not really support any sort of pooling for prepared statement handles.<br /><br />worse yet if the scalar values you bind have varying selectivity you run into crappy query plans if you reuse prepared statement handles, since MySQL only computes a generic query plan. some other RDBMS compute the query plan optimized for the first values bound, AFAIK Oracle recently got the ability to maintain multiple query plans for one prepared statement handle.<br /><br />also of course if your data distribution changes over the lifetime of your prepared statement you might also be in trouble.<br /><br />so i think there are many many usecase where parepared statements in their current form hurt performance severely for SELECTs.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-26808883590763651212010-07-06T22:22:01.495+02:002010-07-06T22:22:01.495+02:00Sherri: Yes, that is absolutely true, I wanted to ...Sherri: Yes, that is absolutely true, I wanted to write that to the post, but realized it was already a bit long. But you are of course right!<br /><br />Roland: Well, actually my testing shows that even for simple SELECTs there is a benefit. And the more times a statement is executed, and the more complex it is, the more beneficial it is. My tests were doing using the InnoDB engine. What you say about IO is true, but these days servers has a lot of memory, and although IO is still an issue, that is often so only to support persistence (for INSERT and UPDATES). But if IO really IS the big deal, then you are right, there is always a balance.<br /><br />Didier: I agree with you, Oracle has a great implementation and I have used it on and off for 20+ years. Great stuff. As for MySQL, well we still have a few things to learn, but I think that the SQL is simpler and the optimizer makes some shortcuts are among the reasons that they aren't as efficient with MySQL.<br /><br />In general, my main hangup is that with MySQL they are still client based resultset based, and materialized on top of that. So no mysql_stmt_use_result() call :-(<br /><br />/KarlssonKarlssonhttps://www.blogger.com/profile/04874338187076980133noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-27481731558937111072010-07-06T22:05:22.465+02:002010-07-06T22:05:22.465+02:00Hi Anders!
"But there are cases where there ...Hi Anders!<br /><br />"But there are cases where there are distinct advantages, in particular SELECTs"<br /><br />mm, but this assumes the IO associated with executing the SELECT (be it prepared or not) is small in comparison to parsing and building the plan. I would assume that in most cases the IO is more expensive than a bit of parsing and plan-building, especially in case the SQL implementation is simple (like MySQL's)<br /><br />It would of course change in case you do a lot of these statements (in the same session since that's MySQL PS scope), but I don't think there are that many applications that fit this pattern.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-65666035558338189762010-07-06T17:22:59.131+02:002010-07-06T17:22:59.131+02:00We also had good results with prepared statements ...We also had good results with prepared statements on high-throughput servers running not-so-complex select queries (and even with multiple-rows insert queries).<br /><br />IMO the worst aspects of prepared statements with MySQL are the extra roudtrips they require to prepare/deallocate them, and the fact memory for prepared statements is not shared across sessions.<br /><br />Oracle has an excellent implementation of prepared statements (both on client API and on server side).<br /><br />Regards,<br />Didier.Didier Speziahttps://www.blogger.com/profile/04955334698655079519noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-91899476294462214402010-07-06T17:11:50.540+02:002010-07-06T17:11:50.540+02:00My "short but sweet" for whether or not ...My "short but sweet" for whether or not prepared statements are useful is that they're useful for security (ie, parameterization helps defeat SQL injection) but not useful for performance.Sheeri K. Cabralhttps://www.blogger.com/profile/13990877688502800403noreply@blogger.com