Richard Broersma wrote:
On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark <sclark@xxxxxxxxxxxxx> 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;
Hi Richard and thanks for the response. When I try the last two queries i get and error. I have listed
the results of explain on all three.
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-# SELECT *
srm2-# FROM T_unit_event_log AS A
srm2-# WHERE A.event_status = 1
srm2-# AND A.event_ref_log_no IS NOT NULL
srm2-# AND A.event_log_no NOT IN ( SELECT event_log_no
srm2(# FROM Myevents)
srm2-# AND EXISTS ( SELECT B.event_log_no
srm2(# FROM Myevents AS B
srm2(# WHERE A.event_ref_log_no = B.event_log_no );
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using indx_tuel_usn_ec_es on t_unit_event_log a (cost=31711.73..3607445990.61 rows=51844 width=146)
Index Cond: (event_status = 1)
Filter: ((event_ref_log_no IS NOT NULL) AND (subplan) AND (NOT (subplan)))
SubPlan
-> Materialize (cost=31711.73..42857.85 rows=830612 width=4)
-> Seq Scan on myevents (cost=0.00..28041.12 rows=830612 width=4)
-> Index Scan using indx1myevents on myevents b (cost=0.00..8.37 rows=1 width=4)
Index Cond: ($1 = event_log_no)
(8 rows)
srm2=#
srm2=#
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-# SELECT *
srm2-# FROM T_unit_event_log AS A
srm2-# LEFT JOIN Myevents AS C
srm2-# ON A.event_log_no = C.event_log_no
srm2-# WHERE A.event_status = 1
srm2-# AND A.event_ref_log_no IS NOT NULL
srm2-# AND C.event_log_no IS NULL
srm2-# AND EXISTS ( SELECT B.event_log_no
srm2(# FROM Myevents AS B
srm2(# WHERE A.event_ref_log_no = B.event_log_no );
ERROR: INSERT has more expressions than target columns
srm2=#
srm2=# explain
srm2-# INSERT INTO Myevents
srm2-# SELECT *
srm2-# FROM T_unit_event_log AS A
srm2-# LEFT JOIN Myevents AS C
srm2-# ON A.event_log_no = C.event_log_no
srm2-# LEFT JOIN Myevents AS B
srm2-# ON A.event_ref_log_no = B.event_log_no
srm2-# WHERE C.event_log_no IS NULL
srm2-# AND B.event_log_no IS NOT NULL
srm2-# AND A.event_status = 1
srm2-# AND A.event_ref_log_no IS NOT NULL;
ERROR: INSERT has more expressions than target columns
I really appreciate your help.
Steve
