search

oracle / node-oracledb

Oracle Database driver for Node.js maintained by Oracle Corp.
http://oracle.github.io/node-oracledb/
Other
2.26k stars 1.07k forks source link

how prefetchRows treated for multiple cursors? #267

Open xpiwo opened 9 years ago

xpiwo commented 9 years ago

Is prefetchRows applied for each out variable?

cjbj commented 9 years ago

If you mean for REF CURSORS, then yes. Check the manual: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#rowprefetching

xpiwo commented 9 years ago

Just to make sure, if i define two REF CURSORS, both will be prefetched using the same prefetchRows value? It seems to be more OCI related question but i could not find documentation about such use case.

Thanks.

cjbj commented 9 years ago

Yes they will use the same value. We have had discussions about whether to allow per-bind variable prefetch sizes; what do you think?

xpiwo commented 9 years ago

i guess those who will bother setting the prefetchRows values, would also like to set it per variable. so cascading from app->query->variable would be nice to have.

cjbj commented 9 years ago

What about your use case? Do you need different prefetch sizes for different REF CURSORs returned from the one execute?

xpiwo commented 9 years ago

i don't know yet; but the easiest example is from the memory allocation standpoint and not bandwidth, if i prefetch 4 cursors with 1000 values, but 3 of them return only a single row. won't i pay the price?

krismohan commented 9 years ago

Yes. Memory allocations happens for all the 4 cursors for 1000 rows each. I recommend using lower prefetch (applies to all) setting and pass appropriate numRows to the getRows() method on the resultsets. For resultsets expecting large number of rows, pass higher numRows. It has the same savings. 'numRows' are retrieved in one round-trip.

General guideline on Resultsets processing large number of rows: For processing batches of small number of rows (numRows is low), set high prefetch. For processing batches of large number of rows (numRows is high), set low prefetch

cjbj commented 9 years ago

I'll change the tag to an enhancement. The desire is to allow a way to set the prefetch count per-ref cursor bind variable.