Re: SQL: View performance problem

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?
>
> Thanks.
>
> Sandy Stock

IMHO your basic problem is that you need to have appropriate columns indexed or,
if you have index, to write a query so that it can use it. Use any kind of EXPLAIN PLAN faccility to verify your execution plan.

But if you are satisfied with geting only one record (no matter witch one) of all matching rows, as I understood last part of your message, than use pseudocolumn ROWNUM :

        SELECT xyz FROM table_name WHERE ROWNUM < 2

will return you only the first record the query finds of all matching records.

Regards, Jure

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================