Re: FW: PreparedStatement#setString on non-string parameters

Antony Paul wrote:
> The performance difference I noticed when it is scanning a table of
> 150k records with a long query containing a lot of or conditions. The
> difference is around 10-12 seconds. So I think it is not the time
> taken to prepare the statement. I wasnt interested in further
> investigation as we dropped the plan of upgrading still 8.x becomes
> 9-12 months old.

There are some performance implications of pushing parameter management
to the server side: under 7.4.x, a prepared statement does not gain the
benefit of knowing the concrete parameter values used in a particular
query execution, which can affect the plan chosen (if a concrete value
for a parameter isn't known, the index selectivity estimates may not be
as good).

In theory this should not be an issue if you set prepareThreshold=0
(either globally on the connection, or via setPrepareTheshold on the
statement in question) to prevent reuse of server statements, and use an
8.0.x server. 8.0 defers the planning of unnamed (not reused) statements
until actual parameter values are available, which may improve the plan
chosen for that particular execution.

If it's not that, I'd need a testcase to be able to investigate this
further.

-O