SQL query 'not in' clause execution takes too long

Question

the following query takes too much time, most likely because of a 'not in' use.

Can you suggest any improvement ?

SELECT vcode, 
       vname, 
       1014 AS fid 
FROM   testcodes co 
WHERE  co.vcode NOT IN (SELECT dict.vcode 
                        FROM   datadictionary dict 
                        WHERE  dict.fid = 1014) 

one thing about structure is . vCode,vName is varchar and testCodes and DataDictionary have same structure.

I searched this problem and found that the left join can possibly resolve this? (WHY does it do better and how can it be done)?

can someone guide if it can be improved ???

Answer 1

SELECT vcode, 
       vname, 
       1014 AS fid 
FROM   testcodes co 
       LEFT JOIN datadictionary dict 
         ON co.vcode = dict.vcode 
            AND dict.fid = 1014 
WHERE  dict.vcode IS NULL 

You need to have indexes created on:

• (testcodes.vcode)
• (datadictionary.vcode,datadictionary.fid)

Both do a single index scan on each table, but the IN has a Merge Join and the INNER JOIN has a Hash Match.

Answer 2

If dict.fid is a unique key (sounds so), then your query should be equivalent to

WHERE  co.vcode != (SELECT dict.vcode -- ...

co.vcode and dict.vcode might need an index to speed things up.

This answer is not an attempt to give a better hint than Pentium10, more a sidenote.