View previous topic :: View next topic
|
Author |
Message |
chinnielr
New User
Joined: 11 Jul 2005 Posts: 87
|
|
|
|
Can you please let me know whether the below query can be fine tuned.
Looking at the high level, can it be fin tuned?
Code: |
SELECT A.NAME,
SUM(TBL2.VAL1),
SUM(TBL2.VAL2),
SUM(TBL2.VAL3),
SUM(TBL2.VAL4),
SUM(TBL2.VAL1 + TBL2.VAL3),
SUM(TBL2.VAL2 + TBL2.VAL4),
DT.C_EXT
TBL1.TOTAL,
'TEST VALUE'
FROM KA05.U_NAME A,
SI.101_T2 TBL2,
KA05.AK_CON CT
KA05.AK_HPS TS
KA05.DATS DT
SI.101_T1 TBL1
WHERE CT.TYPE IN ('Z', 'H')
AND CT.STAT IN ('K','L','M','S')
AND CT.ITYP IN ('J','R','F')
AND TBL2.T_3 = 'B'
AND CT.VER = DT.C_EXT
AND CT.TCD = TS.TCD
AND TS.CMP = A.CMP
AND TS.CMP = CT.CMP
A.CMP = TBL1.CMP
AND TS.A_IND = 'N'
AND TS.PBLE = TBL2.TB_1
AND TS.ASLE = TBL2.TB_2
GROUP BY A.NAME,
DT.C_EXT,
TBL1.TOTAL
UNION ALL
SELECT A.NAME,
SUM(TBL2.VAL1),
SUM(TBL2.VAL2),
SUM(TBL2.VAL3),
SUM(TBL2.VAL4),
SUM(TBL2.VAL1 + TBL2.VAL3),
SUM(TBL2.VAL2 + TBL2.VAL4),
DT.C_EXT
TBL1.TOTAL,
'TEST VALUE'
FROM KA05.U_NAME A,
SI.101_T2 TBL2,
KA05.AK_CON CT
KA05.AK_HPS TS
KA05.DATS DT
SI.101_T1 TBL1
WHERE CT.TYPE IN ('Z', 'H')
AND CT.STAT IN ('K','L','M','S')
AND CT.ITYP IN ('J','R','F')
AND TBL2.T_3 = 'A'
AND YEAR(CT.VER) = YEAR(DT.C_EXT)
AND MONTH(CT.VER) = MONTH(DT.C_EXT)
AND CT.TCD = TS.TCD
AND TS.CMP = A.CMP
AND TS.CMP = CT.CMP
A.CMP = TBL1.CMP
AND TS.A_IND = 'N'
AND TS.PBLE = TBL2.TB_1
AND TS.ASLE = TBL2.TB_2
GROUP BY A.NAME,
DT.C_EXT,
TBL1.TOTAL
ORDER BY 10, 9 DESC, 1 |
Thanks in advance.
Rregards,
Chinni. |
|
Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009 Posts: 197 Location: Mumbai
|
|
|
|
are you using this query in a src code or you are simply unload this data using this query in the file |
|
Back to top |
|
|
chinnielr
New User
Joined: 11 Jul 2005 Posts: 87
|
|
|
|
Ketan,
This query is being used in source code.
Regards,
Chinni. |
|
Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009 Posts: 197 Location: Mumbai
|
|
|
|
try to unload the query using the unload utility and the process the file in the SRC, this will reduce CPU usage and also reduce the usage of the DB2 |
|
Back to top |
|
|
chinnielr
New User
Joined: 11 Jul 2005 Posts: 87
|
|
|
|
I have tried executing the query from the file and the result is same.
I do not see much of difference in terms of performance.
Regards,
Chinni |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Queries that are "resource hogs" will be so whether run from a file or within code.
You need to analyze the sql and determine where the time is being wasted. . .
Suggest you work with the dba to determine alternatives to this query. |
|
Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009 Posts: 197 Location: Mumbai
|
|
|
|
If you have some tools like APTUNE or STROBE you can get the detail analysis of the code where the code is consuming more CPU usage. check that. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Tracking cpu tme for a query is often just spinning wheels. . .
The single major cause of time wasted in a query is how much interaction with the database is needed. . . Certainly more interaction uses more cpu, but the issue/cause is this interaction, not the cpu usage. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
Probably you could try making a temporary table with the complete data satisfying all your where clause and then do a sum on this table.
For populating the temp table use an INSERT with full select.
Or else you can even use a CTE (common table expression) which would help you eliminate the creation of temp table and inserts. |
|
Back to top |
|
|
ankvardhan
New User
Joined: 05 Sep 2008 Posts: 14 Location: Mumbai
|
|
|
|
Hi,
As per your Query, try the following activities:
1) Replace Union by (IN OR Between clause these give better performance)
2) Try to Optimize for "N" possible rows (during fetch).
3) Avoid "SORT" if possible.
Also from DBA prespective, check the Clusterratio in Sysibm.Sysindexes it should be 100, if its less than 80 your I/O operations performance get degraded....!!
Hope these things might help you...!!
Thanks
Ank |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi Chinnielr,
Can you tell me,
1. How often this query is executed ?
2. How often table is updated ?
Thanks,
Sushanth |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
This is a UNION of a six (6) table join
to
a six (6) table join.
we do not have any idea of the machine & OS,
version of DB2
we have no idea of the size of the tables,
the expected number of returned rows.
their keys,
their runstats,
their rebinds.
Nor do we have any idea of the relationships of the tables,
the columns used as predicates ... and on and on and on and on and on
The fact that this is imbedded sql,
my first inclination would to be break-up this crap into several CURSORS and do a match merge.
But we only have this bare sql, and are asked how to tune it.
That is like being given a nail-file and told to tune up a car.......
we need more information from the TS. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
This reminds me of the sql written by 'graduates' of IBM sql classes,
who come back and write these complicated queries,
that in theory work and can run,
and save all that requirement to write application code
but we forget that this takes iron to run on,
and IBM is very good at linking everything to more sales |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Quote: |
who come back and write these complicated queries,
that in theory work and can run, |
And these are always demonstrated using tables that only have a few rows. . . . See? The query runs sub-second. . .
When one has the hardware concession, the only time tuning comes into play is when the wall-time gets really ugly.
The tendancy to put way too much code into a single query is quite scary . . . At least to me
d |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
as an aside,
IBM, IMUO, sells the best hardware/OPS-System in the world.
I thoroughly enjoy IBM COBOL, REXX, etc..
I also support DB2 over other rational db systems. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi,
Quote: |
This reminds me of the sql written by 'graduates' of IBM sql classes,
who come back and write these complicated queries,
that in theory work and can run, |
Quote: |
And these are always demonstrated using tables that only have a few rows.
The tendancy to put way too much code into a single query is quite scary . . . At least to me
|
When i was in college, 3 years back. I had no idea about, there could be so much of performance issues and stuff like that.
All i knew is, a table will have columns and it can hold lots of rows. And, i have created a few tables in college and those tables will not have more than 10 - 20 rows. Most of the time in college, i had put of lots of things in the queries itself, so there would be less programming work.
Now, the scenario is completely opposite.
Since the query the TS posted has a lot of calculations, if the table is updated rarely and query is executed oftenly. MQT would be a good choice, i think.
Thank You,
Sushanth |
|
Back to top |
|
|
|