DB2/400 SQL Sub Query Performance?
These tables are on a DB2/400 database.
1) Original Query that takes forever:
select EVENT, INUMBR, ISTORE, EVHYY, EVHMM, EVHDD
from INVEHD
where EVENT in (Select EVENT
from INVEHH
where (EVHYY*10000 + EVHMM*100 + EVHDD)=
20040617)
2) If I run the subquery first then I get the following.
Select EVENT
from INVEHH
where (EVHYY*10000 + EVHMM*100 + EVHDD)=
20040617
Event Number
19
967
970
972
986
990
991
993
994
998
******** End of data ********
3) And then if I re-run the first query with the results from the subquery like this, I get instentaneous results.
select EVENT, INUMBR, ISTORE, EVHYY, EVHMM, EVHDD
from INVEHD
where EVENT in ( 19,
967,
970,
972,
986,
990,
991,
993,
994,
998)
As far as I know query one should perform just as well as query 3. But one takes forever and 3 has instentaneous response.
Let me know, if you can tell me what is it that I am doing wrong. I had discussed this with my colleagues and we came up with some alternatives that executes pretty fast. But theoretically query one should run just as fast.
Ajish



