IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Help needed for query optimization.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Tue Jun 24, 2014 11:21 am
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jun 24, 2014 1:38 pm
Reply with quote

* 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
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Tue Jun 24, 2014 2:07 pm
Reply with quote

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
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Jun 24, 2014 2:39 pm
Reply with quote

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
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Tue Jun 24, 2014 3:17 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Jun 24, 2014 6:11 pm
Reply with quote

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
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Wed Jun 25, 2014 1:53 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jun 26, 2014 9:48 am
Reply with quote

Please check the explain and see which part of the query is not indexed.
Back to top
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Fri Jun 27, 2014 12:09 pm
Reply with quote

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
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Fri Jun 27, 2014 4:23 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 27, 2014 5:20 pm
Reply with quote

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
View user's profile Send private message
Anil Khanna

New User


Joined: 06 Sep 2013
Posts: 15
Location: Cube

PostPosted: Tue Jul 08, 2014 11:56 pm
Reply with quote

Thanks GuyC.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Mainframe Programmer with CICS Skill... Mainframe Jobs 0
Search our Forums:

Back to Top