Re: SQL: View performance problem

In article <5bdpe8$35e_at_prometheus.acsu.buffalo.edu>, sstock_at_cs.buffalo.edu (Sandra M Stock) wrote:

>We are running into a performance problem with a view, and I wonder if
>anyone has any ideas to improve response time. The problem is:
>
>We have an EMPLOYEE view that needs to be joined to an APPOINTMENT
>view. For each record in EMPLOYEE, there may be many records in
>APPOINTMENT. However, we only want one record output for each distinct
>employee (regardless of how many records exist in APPOINTMENT).
>Without removing duplicates, the queries against the view (select *
>from EMPLOYEE) return results in approximately 40 seconds. As soon as
>we add any logic to remove duplicates (i.e.: select distinct, a
>subquery in the WHERE clause to match a field to the MAX value of that
>field from a second instance of the APPOINTMENT table) response time
>jumps to 3 minutes, which times out our users before they get any
>results.
>
>Is there any way to do a join in such a way as to only get the first
>match back, without using distinct?
>Is there some SQL logic that can efficiently remove duplicate rows?
>

I had a similar issue last year, but unfortunately I don't have the code in front of me right now. I think what I did went as follows, but I'm composing this response as I go, so check my work...

Since you said you tried select distinct, I assume that you only need columns from your employee table in the select list. Try something like this:

create or replace view busy_employee as
select

   e.* -- or whatever
from

   employee e
where

   exists

      (
      select
         *
      from
         appointment a
      where
         a.employee_id  = e.employee_id   -- or whatever your key is
         and rownum <= 1
      )

The "rownum <= 1" was important in my version of ORACLE, at least (7.1.3 on VMS).

Let me know if this helps, please.

-- 
Larry Reid                                          lcreid_at_web.net