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
 

 

Eliminate MAX Clause for fine tuning SQL

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

New User


Joined: 16 Dec 2006
Posts: 1
Location: Chennai

PostPosted: Sat Dec 16, 2006 12:29 pm    Post subject: Eliminate MAX Clause for fine tuning SQL
Reply with quote

Can any one help me to eliminate MAX Clause for SQL fine tuning
This table has some 5 million records. Job is running for a long time.

For Eg:
SELECT MAX(XXX_NBR)
FROM XXX_YYY
WHERE XXX_NBR > :WS-MIN-NBR
AND YYY = :WS-YYY
WITH UR
Back to top
View user's profile Send private message

die7nadal

Active User


Joined: 23 Mar 2005
Posts: 156

PostPosted: Mon Dec 18, 2006 10:04 am    Post subject:
Reply with quote

What do u want to acomplish thru this query. If u need the Max value, then then u shud use the MAX.
But here are a few things to consider.
1) Are XXX_NBR and YYY Indexed, if not try doing it.
2) If both the columns are indexed, is the predicate that brings back less rows executed first. If not try forcing the predicate that brings back more rows to execute last by concatenating Null to the corresponding host variable as here YYY = :WS-YYY || ''.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Mon Dec 18, 2006 11:49 am    Post subject: Re: Eliminate MAX Clause for fine tuning SQL
Reply with quote

Try GROUP BY ...
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 Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Performance tuning of Online system bipinpeter All Other Mainframe Topics 2 Thu Nov 26, 2015 2:29 pm
No new posts [SQL0029] INTO clause missing from em... HABBIE DB2 2 Fri Sep 04, 2015 3:54 pm


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