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
 

 

COALESCE function - Removing will improve performance?

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

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Wed Jul 23, 2008 9:10 pm    Post subject: COALESCE function - Removing will improve performance?
Reply with quote

Hi,
I have a SQL which uses COALESCE function.
Code:
SELECT COALESCE(SUM(YY_QTY),0)           
   FROM   TABLE01
  WHERE ACC_NO       =  :WS-AC-NO       
    AND EF_BDT    <=  :CURR-DATE
    AND (CURR_CD = '   '                       
        OR CURR_CD = 'USD')                     
    AND XXX_NO       =  :WS-XXX-NO       
    AND XPR_BDT    >  :CURR-DATE
    AND SYS_CD  =  ' '                 


This sql is the expensive sql in the entire program. Upon analysis I found that the column "YY_QTY" is defined as a "NOT NULL" field.
How do I know? simple, I checked the colum definition in sysibm.syscolumns and value of column "NULLS" is "N".

My question:
Will removing COALESCE from the sql statement and having just SUM improve performance? I mean reduction of CPU usage?

I ran explain on the them, but could not see any difference. Since the program handles huge data, I feel we can see some improvement when we run the job as whole.

I don't have access to execute the job, I want to suggest this to development group so that they can try this. I am looking for a second opinion.

Pls let me know if any additional details are needed. Thanks for your help.
Thanks,
Viji
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 23, 2008 9:30 pm    Post subject:
Reply with quote

I do not think your SQL is expensive because of the COALESCE function ... it is used in the SELECT so even removing it wont make a difference ... You will have to check your WHERE clause and tune it for better performance ...
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


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

PostPosted: Sat Aug 02, 2008 12:38 am    Post subject: Reply to: COALESCE function - Removing will improve performa
Reply with quote

Viji,

I have a question, when column YY_QTY is defined as NOT NULL (and looks like it is a numeric column) there must be a default value to set and it should be ZERO in your case.

So when you do a SUM on YY_QTY, you'll get either a numeric value greater than zero or a ZERO.

So why exactly function COALESCE is being used here ?

Quote:
Will removing COALESCE from the sql statement and having just SUM improve performance? I mean reduction of CPU usage?

Try that and let us know. icon_smile.gif
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Sat Aug 02, 2008 12:54 am    Post subject:
Reply with quote

If there are not rows selected then SUM(YY_QTY) will be NULL, can the program handle a null for that, probably not.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


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

PostPosted: Sat Aug 02, 2008 1:26 am    Post subject: Reply to: COALESCE function - Removing will improve performa
Reply with quote

You are right Craq, didn't think of this one.
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: Sat Aug 02, 2008 1:51 am    Post subject:
Reply with quote

Hello,

Quote:
This sql is the expensive sql in the entire program.
How was this determined and what is considered expensive?

Quote:
I ran explain on the them, but could not see any difference.
There should not be any difference.

How many rows are likely to match on ACC_NO? How many rows are likely to match on ACC_NO but not the other predicates?

ACC_NO is a key or the high-order part of a key?
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Mon Aug 04, 2008 4:21 pm    Post subject:
Reply with quote

Can you paste the explain output.?. how many rows are there in the table.?
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 BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am


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