perl.dbi.dev
[Top] [All Lists]

Re: patch to DBD-Oracle to allow faster fetch from REF CURSORs

Subject: Re: patch to DBD-Oracle to allow faster fetch from REF CURSORs
From: Tim Bunce
Date: Thu, 20 Sep 2007 09:22:00 +0100
Newsgroups: perl.dbi.dev

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 <alex@xxxxxxxxxx> 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.
> >
> >

<Prev in Thread] Current Thread [Next in Thread>