Re: MERGE: performance advices

On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark <sclark(at)netwolves(dot)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