Re: Oraperl / DBD / Perl5 Performance for Oracle query?

In article <157ce$112fa.26a_at_news.cegelecproj.co.uk>, Steve Kilbane <Steve_Kilbane_at_cegelecproj.co.uk> wrote:
> In article <68r3rr$6hv$1_at_rdsunx.crd.ge.com>, kornfein@.crd.ge.com (Mark Kornfein) writes:
> >
> > I've recently come on board a web project that uses the Oraperl emulation
> > interface for DBD. The performance is horrible, a query that returns in 4-5
> > seconds from sqlplus takes 50 seconds, This is just to do the "fetch"
> > without calling any cgi scripts.
>
> Is that *just* the fetch, or are you looking at Perl invocation,
> parsing, connection to Oracle, the query opening, and then the
> fetch? Usually, the time is in the connection to Oracle, and the
> README for DBD::Oracle gives some hints and tips on that.
> For example, are you using SQL*Net, and if so, do you actually
> need to?

Here's a copy of a message I recently sent to dbi-users. I'm posting it here so it reaches the same people as Mark's "The performance is horrible" statement.

Tim.

To: dbi-users_at_fugue.com
Subject: DBI / DBD::Oracle fetch performance

I found some time to look into this a little.

I've modified DBD::Oracle such that you can set an attribute which will cause the same row to be fetched from the row cache over and over again (without involving Oracle code but exercising *all* the DBI and DBD::Oracle code in the code path for a fetch).

The results (on my lightly loaded Sparc 10) fetching 50000 rows using:

        1 while $csr->fetch;

were:

	one field:   5300 fetches per cpu second (approx)
	ten fields:  4000 fetches per cpu second (approx)

Obviously results will vary between platforms but it does give a feel for the current theoretical maximum performance.

By way of comparison, using the code:

        1 while _at_row = $csr->fetchrow_array;

(fetchrow_array is roughly the same as ora_fetch) gives:

	one field:   3100 fetches per cpu second (approx)
	ten fields:  1000 fetches per cpu second (approx)

Notice the slowdown and the more dramatic impact of extra fields. (The fields were all one char long. The impact would be bigger for longer strings.)

Changing that slightly to represent actually _doing_ something in perl with the fetched data:

    while(_at_row = $csr->fetchrow_array) {

        $hash{++$i} = [ _at_row ];
    }

gives: ten fields: 500 fetches per cpu second (approx)

That simple addition has *halved* the performance.

I therefore conclude that DBI and DBD::Oracle overheads are small compared with Perl language overheads (and probably database overheads).

So, if you think DBI/DBD::Oracle is slow, try replacing your fetch loop with just:

        1 while $csr->fetch;

and time that. If that doesn't help much then point the finger at the database, the platform, the network etc. But think carefully before pointing it at the DBI or DBD::Oracle.

(Having said all that, if anyone can show me how to make the DBI and DBD::Oracle even more efficient, I'm all ears.)

Tim. Received on Wed Jan 21 1998 - 00:00:00 CET