Fork me on GitHub
#sql
<
2020-04-22
>
nikolavojicic19:04:19

What would be the most efficient read-only options for Connection and PreparedStatement? DB changes won't happen during those SELECTs. DB pools are not used but connections and statements are re-used many times, closed only at the end. I did some research and came up with this... get-connection opts:

{:read-only   true
 :auto-commit false}
prepare opts:
{:concurrency :read-only
 :cursors     :close
 :result-type :forward-only}
I was thinking of :fetch-size but https://github.com/seancorfield/next-jdbc/blob/master/doc/all-the-options.md#statements--prepared-statements mentiones "zero or a negative value [...] trigger streaming of result sets"... Not sure what that streaming is... I don't really need laziness here, the whole result is needed, i.e. I'm using execute, not plan.

seancorfield19:04:28

How :fetch-size (or, in fact, any of those options) interact with performance and/or streaming is very DB-specific.

seancorfield19:04:10

I probably ought to update the docs about fetch size in particular since for PostgreSQL at least you need fetch size set to something positive when you're streaming result sets.

nikolavojicic19:04:40

I need most universal read-only opts as defaults since it will be used for different rdbms... Guess I should avoid setting the :fetch-size.

nikolavojicic19:04:23

It seems that MySQL uses streaming only https://stackoverflow.com/a/20900045

seancorfield20:04:35

Yeah, it's really hard to configure things in a way that will work the same universally 😞

seancorfield20:04:30

Ah, I see if you set useCursorFetch as a connection option on MySQL you can use fetch size just like other DBs. Oh great. And then of course if you want multi-row inserts to work as single batch operations, you need a custom option ... which is different for PostgreSQL and for MySQL 🙂

seancorfield20:04:09

(argh! That option enables server-side cursors in a temporary table 👀 Good grief... whoever thought that was a good idea?)

nikolavojicic20:04:14

I'm looking for a read-only minimum 🙂 Users will be able to change it... But not all users know / understand these JDBC options (me neither). Will test performance on most popular RDBMs to find out.