hi,

Can u suggest a way to improve this query performance?
I have 2 tables T1,T2.
T1 has two numeric fields n1,n2 which together form the primary key.
T2 also has the fields n1,n2 which together reference the values of T1.

query:

select T1.n1,T1.n2
from T1,T2
where
char(T1.n1)||char(T1.n2) not in char(T2.n1)||char(T2.n2)

My problem is the this query takes a long time to complete as T1 has
over 5000 rows.


PS: I was told that there is a feature in db2 by which i can say
select (n1,n2) from T1,T2
where
(T1.n1,T1.n2) not in (T2.n1,T2.n2).
Can someone throw some light on that?

Thanks,
K Rajkumar

I am not sure what your are trying to do, but it sounds like you want the rows in table1 which don’t exist in table2.

Select A.n1, A.n2
from T1 A
where not exists
(select B.n1 from T2 B
where A.n1 = B.n1 and A.n2 = B.n2)

Make sure T2 has a composite index on (n1, n2). There can be other columns in the index if needed, but n1 and n2 should be the first 2 columns of the index for best performance.

Hi,

Try to write subselect like this:
select n1, n2 from T1 where (n1,n2) not in (select n1, n2 from T2)

I also suggest you to go through the How to improve the performance? thread already discussed in this forum.

Hope this helps,
Grofaty

Grofaty,

I tried your query.But it is giving me a syntax error.
My db2 is Version: V5R1M0 010525
that runs on a AS/400 system.
Could that be a problem?

Reg,
K Rajkumar

I think that my query and grofaty's is the same. But as you noted, his syntax may not be supported by your verison. They both do the same thing and should perform the same.

Hi,

That was an interesting event! When I started to write the reply there was no replys, but when I sumited it then the Marcus_A had already posted the message. So the answers are almost the same... Funny!

As Marcus_A said, your database version does not suppot the above SQLs.

There is one more tip (I don't know if this is supported with your version of db2):

select n1, n2 from T1
except
select n1, n2 from T2

If this works, consider the Marcus_A tip about indexes.

Hope this helps,
Grofaty

It is better instead of (select B.n1) using select 1 and do not make trouble for db2 for searching B.n1 , because the result of not exist is true or false.
Thanks

Maybe or maybe not.

DB2 has to find the column B.n1 anyway because it is part of the WHERE clause. The value is not returned to the application program because it is part of the EXISTS clause and is not included in the columns retrieved to the program in the main SELECT.

I don't think DB2 will be bothered by such an inclusion of the column in the SELECT clause of the sub-select. But that is just my opinion.

query can further be improved by

Select A.n1, A.n2
from T1 A
where not exists
(select 1 from T2 B
where A.n1 = B.n1 and A.n2 = B.n2)

That's what M_RAS said. But I am not so sure, for the reasons that I already gave.

Try this statement:

Select A.n1, A.n2
from T1 A left outer join T2 B
on A.n1 = B.n1 and A.n2 = B.n2
where B.n1 is null