Sorry wrong keyboard
Using
while (i++ < 5) {
try(DSLContext c = DriverManager.getConnection(...)) {
//write(ctx);
}
catch(SQLException e) {
logger.error("attempt #{} failed: {}", e.getMessage(), e);
}
Through 100 (gnu parallel) invocations of the java program I get 98
attempt #1 failures, 96 #2s, 94 #3s, 2 #4s and a single #5 and indeed I
updated the target 99 times. Maybe I should just do #6!
So it isn't the missing/(assumed) connection.close(). So I think I'll
have to chase the other fork of this thread - the pgbouncer side. I'll
up-date here if I learn anything - but I'm a very old dog so chances
aren't good.
On 09/01/2017 06:04 AM, Lukas Eder wrote:
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]
<mailto:[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]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout
<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]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout
<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]
<mailto:[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.
