Re: ref cursor slow compared to sql query

Allan Streib wrote:

> Oracle 9i R2. I have a query which I can run and returns results in
> sub-second time. Total of 32 rows, not a lot of data per row (a few
> numeric values and a date).
>
> The same query, put in a procedure with some parameterized input and
> results returned as a ref cursor, takes an order of magnitude longer to
> return the same results (i.e. up to almost 10 seconds). This is noticed
> in both SQL Plus (i.e. declaring a refcursor variable, calling the
> procedure, and then printing the variable) and also calling the procedure
> from an ASP page.
>
> Is this commonly seen? What is a good approach to determining the reason
> for this? I can understand some overhead in fetching rows from a cursor
> but this seems out of whack.
>
> Allan
> --
> "The lyf so short, the craft so long to lerne." -- Chaucer

The difference seems excessive. But how do you know the extra time is the REF CURSOR And not all of the ASP page and web server baggage?

Can you post the code?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)