OCI*ML: Minor updates

A few minor updates to OCI*ML:

• oraprefetch to set rows pre-fetched and buffered by OCI. This is set per-statement handle, with a default for newly-created handles set in .ocamlinit.
• oradeqtime to set the number of seconds to wait on oradequeue operations. Set to -1 (the default) for indefinite wait. Raises Not_found if no message is received within the timeout period.
• orabindexec changed to take a list of Col_value arrays
• rows_affected populated in statement handles for DML
• Included .ocamlinit for use with ocimlsh.

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
1	94.93	52.6	1
10	20.41	244.98	4.65
50	8.50	588.24	11.17
100	7.10	704.23	13.37
500	4.84	1033.06	19.61
1000	5.99	834.72	15.85

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 sqlplus, ocimlsh can have many open connections; I am not sure how to best represent that.

ocimlsh

† As the crow flies; it’s ~36ms measured by ping and ~70ms measured by tnsping due to firewalls most likely.

About these ads