Re: Slow cursor performance.

You have to remember that Toad doesn't fetch all records. It just fetches enough to fill the grid you have displayed. Your program probably fetches all records.

HTH
Steve...

"Belti" <daniel_borg_at_hotmail.com> wrote in message news:bee6cab9.0210290104.29019ddc_at_posting.google.com...
> Hello! I'm wondering whether somebody can help me figure out this
> problem.
>
> I'm running a select statement which returns transactions for a given
> customer. I have managed to tune the select statement using /*+
> FIRST_ROWS */ hint. To give you an idea of the response time. Before
> tuning it was taking about 8 secs but after the tuning it was taking
> 190msec. In TOAD when I run this statement the performance was better
> but then when it is included in the code and the actual fetch from the
> cursor is done it slows alot.
>
> Have you got any idea why this could be? I haven't found anything on
> the Net that says why the fetch of the cursor slows down the
> performance and when I run the select statement on it's own it's fast.
>
> The indexes are not fragmented cos they have been rebuilt recently and
> also when I run the select statement on it's own it makes full use of
> the indexes.
>
> This is the tuned select statement.
>
> SELECT /*+ FIRST_ROWS */ *
> FROM transaction_list_user_view
> WHERE user_id=17628
> AND lang_id='eng'
> AND transaction_date BETWEEN sysdate-30 AND sysdate
> ORDER BY transaction_date DESC, transaction_id desc;
>
> Thanks in advance
>
> Belti
Received on Tue Oct 29 2002 - 08:37:04 CST