I'm sorry - I don't know PostgreSQL well enough in this area. Please do update this list, I'm sure people might find it useful in the future.
2017-09-01 17:56 GMT+02:00 Rob Sargent <[email protected]>: > 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]>: > >> 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. > > > -- > 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.
