Tunning SELECT MAX

delago · New User Joined: 29 Jul 2005 Posts: 21 Location: Brazil

Hi.

People I need a help.

A wrote a query with statement MAX, but the CPU time on the on-line process é high.
I don't use the complete PRIMARY KEY.

SELECT MAX (CAMPO03) FROM TABLE01
WHERE CAMPO01 = :CAMPO01
AND CAMPO02 = :CAMPO02

I need to rewrite my query to return the highest value of column, but don't use a statement MAX.

My question: If I OPEN a CURSOR and FETCH one only time is better to use a SELECT MAX?

DECLARE CURSOR C1 FOR
SELECT CAMPO03 FROM TABLE01
WHERE CAMPO01 = :CAMPO01
AND CAMPO02 = :CAMPO02
ORDER BY CAMPO03 DESC
FETCH FIRST 1 ROW ONLY
END-CURSOR;

I remember to read in somewhere, the DB2 sorting the data in the 2 situations, but don't remember what's the better.

Another question is: If a create a FOREIGHT KEY using CAMPO01 and CAMPO02, the performance will be better?

Thanks and sorry for my english... I wrote for last time a 2 years ago.
:-p :-)

mlp · New User Joined: 23 Sep 2005 Posts: 91

I think both queries will perform equally which mean, they will consume round about the same CPU time.

But instead of declaring a cursor you can execute a normal select query also as the result set has only one row.

dick scherrer · Posted: Thu Nov 25, 2010 9:59 am

Hello,

Using MAX should not cause excessive cpu use.

A "foreign key" does not fit the requirement does it? You could add an additional key, but the overhead of maintaining the additional key might be more expensive than it is worth. . .

GuyC · Posted: Thu Nov 25, 2010 4:01 pm

only an index starting with (campo01,campo02,campo03) or (campo02,campo01,campo03) will have a positive effect (on THIS query)

delago · New User Joined: 29 Jul 2005 Posts: 21 Location: Brazil

Ok, thank's guy's.
I will can think another solution to my problem.
The performance in my process (15 cobol program's) is the problem.
I think the SELECT MAX is the worst, but I read a IBM Cobol documentation and found another tips to implement in my code.
Thank's a lot.

dick scherrer · Posted: Thu Nov 25, 2010 9:35 pm

Hello,

Good luck, but if you believe the MAX is causing the performance problem, you will waste time until you move on to the real issue(s). . .