speed up a slow view

From: Sarah Officer <officers_at_aries.tucson.saic.com>
Date: Wed, 14 Jul 1999 13:15:49 -0700
Message-ID: <378CEFF5.A2696BFB@aries.tucson.saic.com>

I have created some views in my database which are complex. The select statements which define the views include unions and subqueries, and some views depend on other views. Is there a way to speed up the execution of the views? How can I tell whether my views are executing as quickly as possible? Would it be faster to create a stored procedure?

I *know* this is gross. I just don't know any way to get around it. Any suggestions or tips would be appreciated. Here's a sample:

---

This view is supposed to show only the latest date/time records for the pid & category. It also has to join tables. The reason I have a subquery here is because of a grouping problem. I only want the maximum for each pid & category, but I need lots of other fields from the mdata table.

create view CurrentResultsView as
  select r.pid, r.mi_id, r.category, r.count,     m.mname, m.idatetime, m.processed, m.gfile   from results r, mdata m
  where r.mi_id = m.mi_id
  and m.processed = 'P'
  and (r.pid, r.category, m.idatetime) in
(select r2.pid, r2.category, max(m2.idatetime)

     from results r2, mdata m2
     where r2.mi_id = m2.mi_id
     and m2.processed = 'P'
     group by r2.pid, r2.category);

---

This view is supposed compare current results to normal value ranges, and create a status indicating whether the range is normal, outofbounds, or an error for the pid & category. It is supposed to take into account that there may be a normalcy record but no current result record, in which case the current result should be considered 0

create view AlertsView as
  select cr.pid, cr.mi_id, cr.category, cr.count,     n.normal_min, n.normal_max, 'NORM' status   from CurrentResultsView cr, Normalcy n

  where cr.pid = n.pid
  and   cr.category = n.category
  and   cr.count >= n.normal_min
  and   cr.count <= n.normal_max

union
  select cr.pid, cr.mi_id, cr.category, cr.count,     n.normal_min, n.normal_max, 'OUT-OF_BOUNDS' status   from CurrentResultsView cr, Normalcy n   where cr.pid = n.pid
  and cr.category = n.category
  and (cr.count < n.normal_min

      or cr.count > n.normal_max)
union
  select n.pid, NULL, n.category, 0,
    n.normal_min, n.normal_max, 'NORM' status   from Normalcy n
  where n.normal_min = 0
  and (n.pid, n.category) not in
(select r.pid, r.category

     from CurrentResultsView r)
union
  select n.pid, NULL, n.category, 0,
    n.normal_min, n.normal_max, 'OUT-OF-BOUNDS' status   from Normalcy n
  where n.normal_min > 0
  and (n.pid, n.category) not in
(select r.pid, r.category

     from CurrentResultsView r)
union
  select r.pid, r.mi_id, r.category, r.count,     0, 0, 'ERROR' status
  from CurrentResultsView r
  where (r.pid, r.category) not in
(select n.pid, n.category

     from Normalcy n)
;

Sarah Officer
officers_at_aries.tucson.saic.com Received on Wed Jul 14 1999 - 15:15:49 CDT