On Wed, Sep 19, 2007 at 08:57:26PM +0530, Biswadeep Chowdhury wrote: > Hello, > > There are several things that I would like to point out: > > 1. Look no further than what happens for SELECTs. Data caching is turned on > by default - and I have not heard much complaining about it. Perl is seldom > used for user interface development and the scenario you describe is an > exception rather than the norm. If turning on data caching by default was a > good decision for SELECTs, then there is little reason not to turn on > caching by default for REF CURSORs.
Agreed. The fact it doesn't work now is purely a bug/limitation of the Oracle OCI interface. It would be good if someone with Oracle support would search for (and/or submit) a bug report about this. Perhaps there is some magic OCI API incantation that would make row caching for ref cursors work. I believe there must be, otherwise a great many oracle applications would be suffering the same slowdown. Any volunteers to check Oracle support about this issue? > 2. Nevertheless, in the patch I have kept data caching turned off by default > for REF CURSORs so that users who are currently happy fetching a single row > per DB roundtrip can remain in that state. Only if users set the caching > parameter, multiple rows will be fetched per DB roundtrip. It should be enabled by default. Lack of caching is a bug. > 3. Per-statement cache tuning may be done by setting the RefCursorCacheSize > to an appropriate before executing/fetching from a REF CURSOR. This is just > as you would do for RowCacheSize - which controls rows fetched per DB > roundtrip for SELECTs (Set this to explicitly 1 and fetch a significant > number of rows and you will realize that data caching is turned on by > default for SELECT statement results). > > The reason I recommend this patch is that there is currently no way (that I > am aware of) to improve performance of fetch from REF CURSORs in perl. I > dont mind it being turned off by default - but at least users who want the > efficiency will have an option to do so. I'd want it on by default, with a suitable default cache size (ideally calculated the same way the default row cache is sized), but I'd rather not have to apply the patch at all. Before applying a large workaround we should be quite sure there isn't a simple fix via the OCI API. Tim. > Biswa > > > On 9/19/07, Alexander V Alekseev <[EMAIL PROTECTED]> wrote: > > > > Hello! > > > > On Wed, 19 Sep 2007, Biswadeep Chowdhury wrote: > > > > > This patch speeds up both the regular fetch, as well as the > > > fetchall_arrayref(). To me that seems better than ONLY speeding up > > > fetchall_arrayref. > > If application requires fast response (as typical user > > interface does), you may slow down the process. If you fetch from > > complex query, you may get the first row immediately, and the next > > row may require a few seconds to calculate. So, if you fetch by 1 > > row, you receive immediate response. If you fetch by 3 rows, you have to > > wait for database to calculate 3 rows, before you get the response. > > > > As I understand DBD interface, there is fetchrow_... interface > > for 1-row fetch, and fetchall_arrayref() for multi-rows fetch. > > Am I right? > > Data caching is a good idea, but it should be tuned > > per-statement. Though I don't know the way to do it, as prepare() > > doesn't accept any additional attributes. > > > > Bye. Alex. > > > >
