|
View previous topic :: View next topic
|
| Author |
Message |
Anil Khanna
New User
Joined: 06 Sep 2013
Posts: 15
Location: Cube
|
|
|
|
We are trying to optimize below DB2 query, as we are facing problem with performance for this DB2 query -- the requirement is : user provides an input as a "till date", say 01-01-2014, and the query retrieves the records, for the dates which is maximum(latest) to the input 01-01-2014 date.
In the query:
1. "FIELD1","FIELD2", "FIELD3", "FIELD4", "FIELD5","FIELD6" are Table's Primary Keys.
2. Total number of records in table: 175,243,290
3. Query takes appx. 50 seconds to get the records.
4. FIELD3 is a number, however stored as char 4 and is primary key of other table which is also char
5. The table has 2 indexes with properties:
a) CLUSTER BUFFERPOOL <pool name> CLOSE NO;
6) NOT CLUSTER BUFFERPOOL <pool name> CLOSE NO PIECESIZE 2097152 K. These indexes are again on primary fields.
| Code: |
SELECT A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5,
A.FIELD6, A.FIELD7, A.FIELD8
FROM (
SELECT (ROW_NUMBER() OVER ( ORDER BY A.FIELD2,A.FIELD3 ASC)) AS RUNNING_NO,
A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5,
A.FIELD6, A.FIELD7, A.FIELD8
FROM ABC.XYZ AS A, (
SELECT MAX(FIELD1) AS DATE1, FIELD2, FIELD3
FROM ABC.XYZ
WHERE FIELDB1 < '2014-06-14'
AND FIELD2 BETWEEN <10 digit int> AND <10 digit int> AND FIELD3 = '<4 digit int>'
GROUP BY FIELD2, FIELD3 ) AS B
WHERE A.FIELD1 = B.DATE1
AND A.FIELD2 = B.FIELD2 AND A.FIELD3 = B.FIELD3
ORDER BY A.FIELD2, A.FIELD3 ASC
FETCH FIRST 50 ROWS ONLY )AS A where A.RUNNING_NO BETWEEN 1 AND 50 |
|
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
* If I read correctly the query below would give the same data.
* there is no absolute garantee that the result is as your "order by".
* there is no need to limit the result twice : by "fetch first 50" AND "row_number between 1 and 50"
| Code: |
select A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5, A.FIELD6, A.FIELD7, A.FIELD8
FROM ABC.XYZ AS A
WHERE FIELD1 < '2014-06-14'
AND FIELD2 BETWEEN <10 digit int> AND <10 digit int>
AND FIELD3 = '<4 digit int>'
AND FIELD1 = (select max(field1)
FROM ABC.XYZ B
WHERE B.FIELD1 < '2014-06-14'
AND B.FIELD2 = A.FIELD2
AND B.FIELD3 = A.FIELD3)
FETCH first 50 rows only |
|
|
| Back to top |
|
|
Anil Khanna
New User
Joined: 06 Sep 2013
Posts: 15
Location: Cube
|
|
|
|
| GuyC wrote: |
| * there is no absolute garantee that the result is as your "order by". |
I did not follow this points of yours, can you please help me to understand which ORDER BY you are referring to? This one:
| Quote: |
| SELECT (ROW_NUMBER() OVER ( ORDER BY A.FIELD2,A.FIELD3 ASC)) |
|
|
| Back to top |
|
|
magesh23586
Active User
Joined: 06 Jul 2009
Posts: 213
Location: Chennai
|
|
|
|
The performance of an SQL statement based on the access paths chosen.
The DB2 optimizer can choose from a variety of different techniques as it creates optimal access paths for each SQL statement. These techniques range from a simple series of sequential reads to much more complicated strategies such as using multiple indexes to access data.
Check when run stats and reorg happened, mostly this will fix your issue.
Refer below link
Tuning DB2 SQL Access Paths |
|
| Back to top |
|
|
Anil Khanna
New User
Joined: 06 Sep 2013
Posts: 15
Location: Cube
|
|
|
|
| Magesh Thanks but I'm not sure where you are trying me to guide. We are using EXPLAIN to get the query cost and optimize it but my personal feeling was that the original programmer for this query has written it badly and we can modify the query itself. And that's where I need guidance before I even start looking at EXPLAIN or RUNSTATs etc. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| Quote: |
| And that's where I need guidance before I even start looking at EXPLAIN |
NOPE ....
start looking at the EXPLAIN with the help of Your support,
from the EXPLAIN try to understand where the access path bottlenecks are
and if things could be done better ...
and proceed from there. |
|
| Back to top |
|
|
Anil Khanna
New User
Joined: 06 Sep 2013
Posts: 15
Location: Cube
|
|
|
|
GuyC- need one more guidance from you. This query is part of a query using which we show "Page x of Y" on front end (a Java screen). In your query if let's say I need to know the count of field1 for "Y" and make use of count(field1) like this:
| Code: |
select count (A.FIELD1), A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5, A.FIELD6, A.FIELD7, A.FIELD8
FROM ABC.XYZ AS A
.
.
.
|
The query takes a long time - can you please suggest a way around to optimize it better for the count thing. The table is not hit by many user for a given moment, so the value of "Y" can be considered static (mostly). |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Please check the explain and see which part of the query is not indexed. |
|
| Back to top |
|
|
Anil Khanna
New User
Joined: 06 Sep 2013
Posts: 15
Location: Cube
|
|
|
|
The EXPLAIN says the select count (A.FIELD1) is the which takes time. Which looks logical as the table has got 1.4+ million rows.
The number of rows need to show the "Y" of "Page x of Y" at front end, I've searched online but I don't really found something good...but count (*), count(column_name) are usually discouraged. I'm trying with count (1) now but have not got a reply from my application team yet.
Any pointers are greatly appreciated in this regard. |
|
| Back to top |
|
|
Anil Khanna
New User
Joined: 06 Sep 2013
Posts: 15
Location: Cube
|
|
|
|
To get the number of rows ina given table, I found suggestion about using
| Code: |
SELECT CARD
FROM SYSIBM.SYSTABLES WHERE TBNAME ='tablename' ; |
but this can not include the WHERE clause to be applied on the table which should be like this also this assumes the RUNSTATs was executed just recently:
| Code: |
WHERE FIELD1 < '2014-06-14'
AND FIELD2 BETWEEN <10 digit int> AND <10 digit int>
AND FIELD3 = '<4 digit int>'
AND FIELD1 = (select max(field1)
FROM ABC.XYZ B
WHERE B.FIELD1 < '2014-06-14'
AND B.FIELD2 = A.FIELD2
AND B.FIELD3 = A.FIELD3) |
What I might do for this!? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
count() takes time even with the best access path, not much you can do about it.
If the table is "mostly" static you might have a look at MQTs |
|
| Back to top |
|
|
Anil Khanna
New User
Joined: 06 Sep 2013
Posts: 15
Location: Cube
|
|
|
|
| Thanks GuyC. |
|
| Back to top |
|
|
|
|
