WHY In Clause Faster than EXISTS clause? 2

[jdu2006]

I used to use the exists clause in my embedded sql statement and it was always working fine. But my process is suddenly chocked and I found out the problem was exists clause, if I changed the exists clause to in clause, the process would be resumed. Any idea? Please help and thanks in advance

 

[fredericofonseca]

Many things affect that.

Just a small example

select ...
from tbl1
where (fld1,fld2,fld3) in
(select fld1,fld2,fld3
from tbl2
)

suppose tbl1 had 20 million records, and tbl2 has 100k records, with some duplicates on the values of fld1,fld2,fld3 resulting on only 70k records being returned on the tbl2 select.
Also from the 20 million records of tbl1, only 2 million records satisfy the "in" clause.

DB2 will be smart enough to determine the better access path on this case, and if fld1,fld2,fld3 are a index (or at least the first 3 fields of an index) of tbl1, then it will be a lot faster than doing the EXISTS, as this one will read ALL index pages from TBL1 in order to access TBL2.

As i said this is just one possible explanation. All depends on your SQL, your table indexes, number of records in each that satisfy your SQL predicates, and on a few other things.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[jdu2006]

Great point. I will check my sql data and indexes and return you back. Thanks. John