|
|
| Author |
Message |
vijikesavan
Active User
Joined: 04 Oct 2006
Posts: 101
Location: NJ, USA
|
|
|
|
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 |
|
 |
References
|
 Posted: Wed Jul 23, 2008 9:10 pm Post subject: Re: COALESCE function - Removing will improve performance? |
|
|
|
|
ashimer
Senior Member
Joined: 13 Feb 2004
Posts: 311
Location: Bangalore
|
|
|
|
| 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 |
|
|
priyesh.agrawal
Global Moderator
Joined: 28 Mar 2005
Posts: 1511
Location: Chicago, IL
|
|
|
|
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.  |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 968
Location: Virginia, USA
|
|
|
|
| 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 |
|
|
priyesh.agrawal
Global Moderator
Joined: 28 Mar 2005
Posts: 1511
Location: Chicago, IL
|
|
|
|
| You are right Craq, didn't think of this one. |
|
| Back to top |
|
|
dick scherrer
Global Moderator
Joined: 23 Nov 2006
Posts: 7505
Location: 221 B Baker St
|
|
|
|
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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 179
Location: Copenhagen, Denmark
|
|
|
|
| Can you paste the explain output.?. how many rows are there in the table.? |
|
| Back to top |
|
|
|
|
