Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Help needed for query optimization.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Help needed for query optimization.
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: 1278
Location: Belgium

PostPosted: Tue Jun 24, 2014 1:38 pm    Post subject:
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    Post subject:
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: 158
Location: Chennai

PostPosted: Tue Jun 24, 2014 2:39 pm    Post subject: Reply to: Help needed for query optimization.
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Jun 24, 2014 6:11 pm    Post subject: Reply to: Help needed for query optimization.
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    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Thu Jun 26, 2014 9:48 am    Post subject:
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    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Fri Jun 27, 2014 5:20 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us