Re: Under what circumstances does PreparedStatement use stored plans?

Tom Lane wrote:
> I think that is a safe bet to be true, as long as you get *some*
> mileage out of the plan cache.  If the application issues a bunch of
> no-two-alike queries then it's a loss of course.  But doesn't the JDBC
> API distinguish prepared statements from unprepared ones?  ISTM it is
> the app programmer's responsibility to prepare just those statements
> he's likely to use more than once.  I don't think the driver need
> second-guess this choice.

That's true; JDBC makes a distinction between prepared statements and
non-prepared statements.

From a practical matter, though, application developers often use
PreparedStatement when they don't plan to re-use the query.  This is primarily
because it adds to portability; using PreparedStatement.setDate to set a
parameterized date field in a query doesn't require the application developer
to know the proper String representation of the date (which differs, for
example, with Access as the database).

So on one hand, the distinction isn't made so cleanly in the real world.  On
the other hand, there's a compelling argument that a developer who uses
PreparedStatement for a one-shot query simply to avoid handling portability in
a more comprehensive way should expect poor optimization behavior on behalf of
the database.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation