A few minor updates to OCI*ML:
oraprefetchto set rows pre-fetched and buffered by OCI. This is set per-statement handle, with a default for newly-created handles set in
oradeqtimeto set the number of seconds to wait on
oradequeueoperations. Set to -1 (the default) for indefinite wait. Raises
Not_foundif no message is received within the timeout period.
orabindexecchanged to take a list of
rows_affectedpopulated in statement handles for DML
.ocamlinitfor use with
Using a representative test for pre-fetching, querying a 5000-row result set over a VPN connection from a 1G Debian VM on my Mac to a test database at corporate HQ about 20 miles away† (with a pre-warmed block buffer cache):
|Prefetch||Time (s)||Rows/sec||Speedup factor|
This shows a nearly 20× improvement from 10 lines of code and a bit of experimental tuning! The tradeoff is obviously memory consumption; two copies of the rows will exist in memory when using
orafetchall – one in the OCI cache, and one in a list in OCaml.
With some assistance on SO, I have also added a prompt to the toplevel, this is a feature I always use in SQL*Plus™ to make sure I don’t run the wrong command on the wrong database! Of course, unlike
ocimlsh can have many open connections; I am not sure how to best represent that.
† As the crow flies; it’s ~36ms measured by
ping and ~70ms measured by
tnsping due to firewalls most likely.