Bind Variables and Prepared Statements -- Good, Bad or Indifferent?


A Gentle Reader provided references to folks who are distrustful of prepared statements.

See "Prepared Statements, Musings" and "Prepared statements are dead, long live prepared statements".

In "musings" we're told "...users do not clean up/close unused prepared statements."  For this reason, they're bad, and should be avoided.  Eventually, "...unless you really happen to know what you are doing with them, you are going to crash the database." Clearly, this isn't true; you only crash the application connection to the database.  The database itself generally keeps on grinding.  

I know that failure to close statements properly doesn't crash "the database".  I did this wrong in a data warehouse application.  DB2 didn't crash, just the long-running applications.

In "long live prepared statements" there's the implication that prepared statements are slow, or slower than single statements, or unsuitable for web transactions because (a) application servers are no good, (b) connection pooling doesn't work or (c) prepared statements involve multiple trips to the server.  There may be other reasons in the posting, also.

Benchmark

So Prepared Statements suffer from two flaws:  Higher overhead -- because we have to actually close them -- and Poor performance.

The first complaint is not a tough problem.  As far as I'm concerned, we can't cater to incompetent programmers by risking huge security flaws.  If people can't follow the API -- well -- they need help or they need new careers.  Allowing them to write flawed software isn't helpful in any way.

["But that's the hand we're dealt," managers whine, "we don't have genius programmers."  You don't need geniuses.  You just need QA.  "But we don't have the budget for QA."  Ah.  If you don't have the budget to create quality, then you do have the budget to create crap.  Enjoy it while you can.  But don't complain about the outsourcing.]

The second complaint -- it appears -- is unlikely to be true.  See "Performance using Bind Variables in a Java Application".   Looks pretty clear to me.  Bind variables are faster.  Also, see "Use Bind Variables for Application Performance".

Exceptions

There are stories of query plans turning to crap with a bind variable but being delightfully optimal with a constant.  This is -- sadly -- incorrect for Oracle.  It may be true for other databases.  A quick benchmark would provide proof for other products.

See "Execution Plan and Reality".  Specifically, "The ... explain plan statement allows ... a query containing bind variables without actually defining these variables,... The optimiser must use the default rule to calculate the selectivity of the predicates with bind variables. The result could be very different from the "real" execution plan based on the peeked values and known data types of bind variables. This is the reason why the execution plan generated by explain plan was so different from the actual one."

So Oracle's explain plan -- with bind variables -- is the worst-case scenario, and not to be trusted.  Good to know.  Don't casually cut-and-paste code for explain plan.

Conclusions

Prepared Statements must be closed.  If you can't follow the API, get help or get another kind of job.

Prepared Statements (and bind variables) are not inherently slow.  When in doubt, benchmark.


Posted: Friday - September 05, 2008 at 09:29 PM
       

Author: Steven Lott
Technorati Tags:
Technorati Cosmos: Technorati Cosmos
Technorati Watchlist: Technorati Watchlist
Add this entry to: