[DB2- AS400] S F WHERE col1 IN (SELECT ...) very very very slow !

[JFDELGES]

Hi,

I have a problem with a simple querry:

select * from
tmissdta.prr000f
WHERE NRPOL in
(
SELECT NRPOL
from TMISSDTA.PRR130F
where nrprs = 01545454
)


I think that the sub-querry is executed for each row ... !
:-S


It take approximatively 150 secondes to be executed!

(And this is not a CORRELATIVE querry! like EXISTS...)


PS: i don't want to use a JOIN , because it will be an UPDATE querry ...


Best regards,
JF Delges, Belgium

 

[PruSQLer]

dd,

I was responding to JF's request to try a joined query and then turn it into a subquery, the theory being that if the joined went quicker than the subquery, then DB2 had a problem. Based on my experiment, the correlated subquery caused the optimizer to pick a less efficient path than the joined query.

I don't know if Rudy is wrong or right. I HAVE heard that Version 8 is less efficient moving large chunks of data across partitions than Version 7 was. Basically all I saw him say about non correlated subs is that they lack the correlation variable (which is definitely true )