Re: MERGE: performance advices

On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark <sclark@netwolves.com> wrote:

> Is there a way to do something similar with the following? I am an SQL noob
> and the
> following takes longer to run than is reasonable, on the order of hours.
>
> insert into myevents select * from t_unit_event_log a where exists
>       (select b.event_log_no from myevents b
>        where a.event_status = 1 and a.event_ref_log_no IS NOT NULL
>               and a.event_ref_log_no = b.event_log_no and a.event_log_no not
> in
>                       (select event_log_no from myevents)
>       )


To start off with, this SQL statement can be refined a bit.  Many of
the sub-query WHERE clause constraints have nothing to do with the
Correlated sub-query.  The refinement would look like so:

INSERT INTO Myevents
     SELECT *
       FROM T_unit_event_log AS A
      WHERE A.event_status = 1
        AND A.event_ref_log_no IS NOT NULL
        AND A.event_log_no NOT IN ( SELECT event_log_no
                                      FROM Myevents)
        AND EXISTS ( SELECT B.event_log_no
                       FROM Myevents AS B
                      WHERE A.event_ref_log_no = B.event_log_no );


The next step would be to rework the NOT IN sub-query into a LEFT JOIN
WHERE IS NULL;

INSERT INTO Myevents
     SELECT *
       FROM T_unit_event_log AS A
  LEFT JOIN Myevents AS C
         ON A.event_log_no = C.event_log_no
      WHERE A.event_status = 1
        AND A.event_ref_log_no IS NOT NULL
        AND C.event_log_no IS NULL
        AND EXISTS ( SELECT B.event_log_no
                       FROM Myevents AS B
                      WHERE A.event_ref_log_no = B.event_log_no );

There is one possible alteration that may or many not improve
performance.  This would be to replace the EXISTS with a LEFT JOIN
WHERE IS NOT NULL;

INSERT INTO Myevents
     SELECT *
       FROM T_unit_event_log AS A
  LEFT JOIN Myevents AS C
         ON A.event_log_no = C.event_log_no
  LEFT JOIN Myevents AS B
         ON A.event_ref_log_no = B.event_log_no
      WHERE C.event_log_no IS NULL
        AND B.event_log_no IS NOT NULL
        AND A.event_status = 1
        AND A.event_ref_log_no IS NOT NULL;


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug