Hi All,

I am new to DB2.

The following code gives the correct solution.But the performance of the query is good when the table contains less no of rows and it is bad when the no of rows in the table is large.

I need the optimization of the following query, so that it will take minimum CPU usage and time.

SELECT A.ACCTNO, T.QTY
FROM ACC A LEFT OUTER JOIN TRD T
ON A.ACCTNO = T.ACCTNO
WHERE T.QTY > 250000

Thanks in advance for your replies.

What is the DDL for the two tables? Also include any indexes for the tables. Please provide DB2 version and OS.

Andy

your query will not work ( regardless of any optimization ) !

SELECT A.ACCTNO, T.QTY
FROM ACC A LEFT OUTER JOIN TRD T
ON A.ACCTNO = T.ACCTNO
WHERE T.QTY > 250000

you are using a left outer join.
If a row of table A has no matching columns in table T all values of T will be <NULL>
none of these row will satisfy the WHERE-clause T.QTY > 250000 as <NULL> is never greater than any value

--> DB2 wil rewrite your querry to an INNER JOIN

So, assuming that your query returned the correct result, i.e., you didn't need the outer join (as umayer pointed out correctly), the following will certainly be more performant and equivalent:Also try the following version, which might perform better (but probably it will be exactly the same):And of course add indexes on TRD.QTY, TRD.ACCTNO, ACC.ACCTNO and the combined (TRD.QTY,TRD.ACCTNO) to speed up the query...

Umayer's analysis is good.