View previous topic :: View next topic
|
Author |
Message |
vijikesavan
Active User
Joined: 04 Oct 2006 Posts: 118 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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 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
Senior Member
Joined: 28 Mar 2005 Posts: 1448 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: 1512 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
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
You are right Craq, didn't think of this one. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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: 178 Location: Copenhagen, Denmark
|
|
|
|
Can you paste the explain output.?. how many rows are there in the table.? |
|
Back to top |
|
|
|