Fork me on GitHub

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 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.


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


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.


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.


It seems that MySQL uses streaming only


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

👍 4

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 🙂


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


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.