Oracle JDBC driver performance prefetch note

[naxels]

Is your feature request related to a problem? Please describe. When trying to process a 1000+ record set it seems as if everything slows to a grinding halt for next.jdbc, while in reality the Oracle JDBC driver has a default setFetchSize of 10 records (per Statement) and a default PrefetchValue of 10 as well (per Connection), meaning it will contact the database for every 10 records you want to fetch.

Describe the solution you'd like I would like to see an update to the documentation in the proper place so that it is clear to anyone using the Oracle JDBC and next.jdbc that by either updating the connection string and setting an addition :prefetch option:

(def oradb {:dbtype "oracle:sid" :dbname "db" :host "host" :port 1521 :user "user" :password "pass" :prefetch 5000})

or jdbc connection string (in my case a Thin/SID based), setting &prefetch=:

"jdbc:oracle:thin:@host:1521:db?user=user&password=pass&prefetch=5000"

or adding the {:fetch-size } param to the query they want to execute:

(jdbc/execute! con oraquery {:fetch-size 5000})

This problem is resolved.

Thank you, Patrick

[seancorfield]

There's an Oracle section in Tips & Tricks but it's pretty minimal right now. These would be great tips to add there:

https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.709/doc/getting-started/tips-tricks#oracle

[seancorfield]

The following text has been added to the Oracle section of Tips & Tricks:

An important performance issue to be aware of with Oracle's JDBC driver is that the default fetch size is just 10 records. If you are working with large datasets, you will either need to either specify :prefetch in your db-spec hash map with a suitable value (say 1,000 or larger), or specify &prefetch= in your JDBC URL string. If you want to keep the default, you can change it on a per-statement basis by specifying :fetch-size as an option to execute! etc.

[naxels]

Absolutely perfect, thanks! :)