CODEWITHSUNDEEP
sqlperformancenetezza
Teja
Teja

Reputation: 13534

Best way to tune NOT EXISTS in SQL queries

I am trying to tune SQLs which have NOT EXISTS clause in the queries.My database is Netezza.I tried replacing NOT EXISTS with NOT IN and looked at the query plans.Both are looking similar in execution times.Can someone help me regarding this?I am trying to tune some SQL queries.Thanks in advance.

 SELECT   ETL_PRCS_DT, COUNT (*) TOTAL_PRGM_HOLD_DUE_TO_STATION
            FROM DEV_AM_EDS_1..AM_HOLD_TV_PROGRAM_INSTANCE D1
           WHERE NOT EXISTS (
                             SELECT *
                              FROM DEV_AM_EDS_1..AM_STATION
                              WHERE D1.STN_ID = STN_ID
                            ) 
 GROUP BY ETL_PRCS_DT;

Upvotes: 1

Views: 6829

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can try a join, but you sometimes need to be careful. If the join key is not unique in the second table, then you might end up with multiple rows. The following query takes care of this:

SELECT ETL_PRCS_DT, 
   COUNT (*) TOTAL_PRGM_HOLD_DUE_TO_STATION
FROM DEV_AM_EDS_1..AM_HOLD_TV_PROGRAM_INSTANCE D1 
left outer join
(
   select distinct STN_ID
   from DEV_AM_EDS_1..AM_STATION ams
) ams
  on d1.STN_ID = ams.STN_ID
WHERE ams.STN_ID is NULL

Upvotes: 1

Kai
Kai

Reputation: 39641

You can try a JOIN:

SELECT ETL_PRCS_DT, COUNT (*) TOTAL_PRGM_HOLD_DUE_TO_STATION
FROM DEV_AM_EDS_1..AM_HOLD_TV_PROGRAM_INSTANCE D1
LEFT JOIN DEV_AM_EDS_1..AM_STATION TAB2 ON D1.STN_ID = TAB2.STN_ID
WHERE TAB2.STN_ID IS NULL

Try to compare the execution plans. The JOIN might produce the same you already have.

Upvotes: 1

Related Questions