Re: ref cursor slow compared to sql query

From: Allan Streib <streib_at_cs.indiana.edu>
Date: 29 Jun 2004 14:06:25 -0700
Message-ID: <e334e4a7.0406291306.7040a7ee@posting.google.com>

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. Received on Tue Jun 29 2004 - 16:06:25 CDT