SQL Query is taking long time

Question

The query below is taking long time and I am trying to tune the query but it seems to be not working .Is there anyway I can rewrite the query. Please help me in tune the query.

 SELECT DISTINCT TC.V_ID,A.ID,A.NAME 
    FROM OM_A_DATA TC 
       INNER JOIN ACC_DOM_EXT AE ON TC.DOMAINS = AE.DOMAIN
        LEFT OUTER JOIN ACCOUNT A ON A.ID=AE.ACCOUNTID
    WHERE V_ID not in (select distinct V_ID from DATA_ACC_MAP)


OM_A_DATA  has 41,696,776 rows
ACC_DOM_EXT has 106,725 rows
ACCOUNT has 106731 rows
DATA_ACC_MAP has 91021 rows

No need for select DISTINCT in sub-query. – jarlh Nov 02 '15 at 12:03 — jarlh, Nov 02 '15 at 12:03
Are the V_ID columns defined NOT NULL? – William Robertson Dec 10 '15 at 18:14 — William Robertson, Dec 10 '15 at 18:14

score 3 · Accepted Answer · answered Nov 02 '15 at 12:05

Often not exists works better than not in:

SELECT DISTINCT TC.V_ID, A.ID, A.NAME 
FROM OM_A_DATA TC INNER JOIN
     ACC_DOM_EXT AE
     ON TC.DOMAINS = AE.DOMAIN LEFT OUTER JOIN
     ACCOUNT A
     ON A.ID = AE.ACCOUNTID
WHERE NOT EXISTS (SELECT 1 FROM DATA_ACC_MAP dam WHERE dam.V_ID = tc.V_ID );

Clearly, you should have an index on DATA_ACC_MAP(V_ID). You should have indexes on other columns as well: OM_A_DATA(V_ID, DOMAINS), ACC_DOM_EXT(DOMAIN, ACCOUNTID), and ACCOUNT(ID, NAME).

score 1 · Answer 2 · answered Nov 02 '15 at 12:09

Either Remove the DISTINCT.

SELECT DISTINCT TC.V_ID, A.ID, A.NAME 
FROM OM_A_DATA TC 
INNER JOIN ACC_DOM_EXT AE ON TC.DOMAINS = AE.DOMAIN
LEFT OUTER JOIN ACCOUNT A ON A.ID = AE.ACCOUNTID
WHERE V_ID NOT IN (SELECT V_ID FROM DATA_ACC_MAP)

Or Use a INNER JOIN rather than sub query.

SELECT DISTINCT TC.V_ID, A.ID, A.NAME 
FROM OM_A_DATA TC 
INNER JOIN ACC_DOM_EXT AE ON TC.DOMAINS = AE.DOMAIN
INNER JOIN DATA_ACC_MAP AD ON TC.V_ID != AD.V_ID 
LEFT OUTER JOIN ACCOUNT A ON A.ID = AE.ACCOUNTID

Or Use a LEFT JOIN rather than sub query and an altered WHERE clause.

SELECT DISTINCT TC.V_ID, A.ID, A.NAME 
FROM OM_A_DATA TC 
INNER JOIN ACC_DOM_EXT AE ON TC.DOMAINS = AE.DOMAIN
LEFT JOIN DATA_ACC_MAP AD ON TC.V_ID = AD.V_ID 
LEFT OUTER JOIN ACCOUNT A ON A.ID = AE.ACCOUNTID
WHERE AD.V_ID IS NULL

SQL Query is taking long time

2 Answers2