Hi Rob,
My production experience with these kinds of tuning is mostly with Oracle,
so I'm not 100% qualified to give you an answer on PostgreSQL - but the
most important difference from a performance perspective between using
(static) Statement and PreparedStatement is the simple fact that the latter
more easily profits from your database's execution plan cache. This cache
is used to store calculated execution plans for identical SQL strings. When
you have two consecutive static statements like these:
SELECT * FROM tab WHERE col = 1;
SELECT * FROM tab WHERE col = 2;
Then the plan must be calculated for each query afresh, with all the
overhead of finding the best plan, etc. If, however, you use a
PreparedStatement
SELECT * FROM tab WHERE col = ?;
SELECT * FROM tab WHERE col = ?;
Then, the same plan can be reused at the price of a slight cardinality
estimate error for the "col = ?" predicate, depending on how skewed the
data in "col" is (the more skewed, the bigger the error).
In general, the error is acceptable and the improved performance achieved
by this cache is very desireable. In Oracle, it is quite possible to kill a
database server if you're not using PreparedStatements often enough,
because there will be tons of concurrency on the execution plan cache for
all those statements that can never be reused and must thus be purged from
the non-infinite cache again to make room for new statements.
There are exceptions where constant literals can be parsed into bind
variables by the server (e.g. Oracle and SQL Server can do this), but it is
often not recommended to activate this feature.
Btw, looking for a PostgreSQL specific link, I found this:
http://use-the-index-luke.com/sql/explain-plan/postgres/concrete-planning
Note that the default setting is five, which means that the first four
> executions will actually use the bind parameters during prepare, the later
> ones not. That counter starts fresh for each PreparedStatement instance.
Which maybe also explains that limit of 5, you've encountered
Hope this helps,
Lukas
2017-08-31 23:01 GMT+02:00 Rob Sargent <[email protected]>:
> Good news on the injection front. I ran with the Settings indicated and
> for the first time got 100% of my jobs saved properly. I have a second
> related client which has to store lists with 1k-10K entries. Any guess as
> to the performance difference here with v. without PreparedStatement in
> play? In this scenario, the jobs are much less frequent.
>
> Thanks, as always
>
>
>
>
> On 08/31/2017 02:49 PM, Lukas Eder wrote:
>
> I'll have to weigh the injection risk.
>>
> jOOQ will inline the bind variables for you, escaping them appropriately
> depending on their types - so there's no additional injection risk from
> using StatementType.STATIC_STATEMENT.
>
>
>> Mainly I'm curious as to how this re-use is occurring and if there's
>> any corrective action in my general scheme. An explicit closing of the db
>> connection perhaps?
>>
> I remember having seen a similar (but not identical) issue in the distant
> past when using PreparedStatement with ref cursor results. At the time, it
> had to do with transactions and the JDBC autocommit flag.
>
> This is just thinking out loud, it's not the same issue, but the JDBC
> driver did have some issues with that...
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
