Re: ref cursor slow compared to sql query

Allan Streib wrote:

> OK I have narrowed this down -- it has nothing to do with the ref
> cursor after all. I have duplicated the performance problem with
> straight SQL.
>
> The bottleneck is a piece of the overall query where a view is joined
> to an inline view. This piece of the query takes about 10 seconds to
> execute. If I replace the inline view with an "in (values)" predicate
> then the whole thing completes in a fraction of a second. The inline
> view only returns about 40 values.
>
> I tried replacing the inline view with a select from a temporary table
> containing the same values, thinking that would be faster, but it made
> little difference. I've tried two versions using the view; these take
> about 18 and 10 seconds respectively. By comparision using hard-coded
> values the query runs in < 0.20 seconds.:
>
> select ra.*
> from resourceavailability ra, (select id from t_resourcelist) rl
> where ra.resourceid = rl.id
> and timeid BETWEEN 55800 AND 59100
> (about 18 seconds)
>
> select * from resourceavailability
> where resourceid in (select id from t_resourcelist)
> and timeid BETWEEN 55800 AND 59100
> (about 10 seconds)
>
> select * from resourceavailability
> where resourceid in (299,300,303,304,318,319,320,
> 321,322,323,324,325,326,327,329,330,331,332,333,
> 334,335,336,337,338,339,340,341,342,343,346,347,
> 348,349,350,351,353,354,776,777)
> and timeid BETWEEN 55800 AND 59100
> (about 0.16 seconds)
>
> "resourceavailability" is itself a view of four other tables, but
> nothing remarkable other than that. No aggregates, analytics, etc.
> The inline view returns the same values that are hard-coded in the
> last version.
>
> How can I improve the performance of this, since obviously my inline
> view will actually return varying results in the real application; I
> cannot just hard-code the list of values.

Look at cast multiset.
http://www.psoug.org/reference/cast.html

-- 
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)