Re: Peformance degradation during array fetch in Pro*C

I'm not very familiar with Pro*C but 1-2 year ago I had a strange problem with OCI8 - performance degradation if host array size is not equal to row prefetch number (a cursor property).

Alex

"Oracle User" <lcrusader_at_yahoo.com> wrote in message news:47cd09c.0304171607.4a55476a_at_posting.google.com...
> This problem turned out to be due to network issue. Getting data over
> sqlnet was slowing down the process. Once I ran the program on the
> instance server, there was no change in fetch time.
>
> Another interesting point I noted is that when I ran the program on
> the instance server with TWO_TASK set (effectively going through
> sql*net), performance degraded as usual. It looks like SQL*Net is also
> a culprit.
>
> Thank you guys for going over this with me.
>
>
> lcrusader_at_yahoo.com (Oracle User) wrote in message
 news:<47cd09c.0303220646.5cfecc20_at_posting.google.com>...
> > Hi guys,
> >
> > My program uses host arrays in Pro*C to read data from oracle db. The
> > size of the table is arbitrary and ranges from 0 - 10 mil records.
> >
> > The program worked fine for small tables, but for larger tables,
> > performance was poorer than sql*plus spooler. So, I traced the timings
> > between each fetch and found that as the program fetched more and more
> > rows, the time to fetch the same number of records also grew, from 1
> > sec to 15 secs. Since the delay is from the server, I couldn't find
> > any way to improve my program.
> >
> > Is there any way to overcome this hurdle? I have tried the program
> > with different array sizes, with the same result. My program uses
> > Dynamic SQL and connects over sql*net. I have seen some posts pointing
> > to the network connection as a culprit. But that doesn't answer why my
> > fetches are faster in the beginning and slower towards the end?
> >
> > Any help is greatly appreciated.
> >
> > Thanks.
Received on Sat Apr 19 2003 - 08:10:17 CEST