Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Tunning SELECT MAX

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
delago

New User


Joined: 29 Jul 2005
Posts: 21
Location: Brazil

PostPosted: Thu Nov 25, 2010 7:28 am    Post subject: Tunning SELECT MAX
Reply with quote

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 :-)
Back to top
View user's profile Send private message

mlp

New User


Joined: 23 Sep 2005
Posts: 91

PostPosted: Thu Nov 25, 2010 8:43 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 25, 2010 9:59 am    Post subject:
Reply with quote

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. . .

Quote:
I don't use the complete PRIMARY KEY.
What part(s) of which key(s) are CAMPO01 and CAMPO02? Keep in mind that we know nothing about your tables/keys/volumes of data/etc.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 25, 2010 4:01 pm    Post subject:
Reply with quote

only an index starting with (campo01,campo02,campo03) or (campo02,campo01,campo03) will have a positive effect (on THIS query)
Back to top
View user's profile Send private message
delago

New User


Joined: 29 Jul 2005
Posts: 21
Location: Brazil

PostPosted: Thu Nov 25, 2010 4:56 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 25, 2010 9:35 pm    Post subject:
Reply with quote

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). . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm
No new posts Can I use sort to select sample recor... Steve Ironmonger DFSORT/ICETOOL 3 Tue Nov 10, 2015 10:02 pm
No new posts Performance issue in the DB2 program ... Pradst57 DB2 19 Mon Oct 12, 2015 8:31 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us