Re: SQL: View performance problem

In article <5bdpe8$35e_at_prometheus.acsu.buffalo.edu>, Sandra M Stock <sstock_at_cs.buffalo.edu> writes
>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?

Try putting the join in a subquery using EXISTS

select * from emp
where exists
( select 1 from app.... where emp.name=app.name)

In any case, check the indexing of the join columns

-- 
Jim Smith