Re: performance issue

Kris Jurka <books 'at' ejurka.com> writes:

> When using a PreparedStatement the server must come up with a plan
> that works for all parameter values.  Since the parameter is unknown,
> the generated plan doesn't use an index.  Your options are to
> interpolate the parameter yourself or connect using the
> protocolVersion=2 URL option which will make the driver do the
> interpolation prior to passing the query on to the server.

Kris, actually, is this behaviour considered a bug or a feature?
It seems that moving from protocol v2 to v3 means a disastrous
decrease of performance in this kind of situation - is it
considered an unavoidable tradeoff for other increases of
performances? (I guess that when executing the same prepared
statement multiple times, it would be a win to save SQL parsing
and parameters interpolation) It seems that it's a common case to
hit this problem, and again it can mean orders of magnitude
decrease of performance, hence maybe the programmer could benefit
from a programmatic way to branch between the two behaviours at
the prepared statement level, or at least at the connection level
(shouldn't it be adviced to avoid manual parameter interpolation
to prevent from exposing to SQL injections because of not
sanitized enough approach, or have a driver-provided parameter
interpolation facility that we could trust)? Unless there's
something in the JDBC specifications, or at the implementation
level, which make my question stupid..

--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36