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 in Query Tuning

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sampaul4u

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Oct 22, 2010 12:15 pm    Post subject: Help needed in Query Tuning
Reply with quote

Hi all,

I declared a cursor as given below.

EXEC SQL
DECLARE SAMPLE CURSOR FOR
SELECT POLY-NO,POLY-ST,AMOUNT,POLY-NO-DATE
FROM SAEMPLOYEE
WHERE POLY-NO LIKE :POLY-NO-DATE
AND EFFECT_DT =:WS-CURR-DT
END-EXEC.


Say for example my table having 5 million records.

I need to fetch below records from the table.

POLY-NO POLY-NO-DATE AMOUNT STATE
******* ************ ******** *****
F02256 F02256-2006 $1132.00 FL
F02256 F02256-2007 $1132.00 FL
F02256 F02256-2008 $1132.00 FL
F02256 F02256-2009 $1132.00 FL
F02256 F02256-2010 $1132.00 FL


My problem is for getting this five rows from the table, it takes lot of time.

please help me to tune this query to reduce the run time.

Thanks in Advance.

Paul

edited to get rid of the annoying coloring!
Back to top
View user's profile Send private message

sampaul4u

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Oct 22, 2010 12:23 pm    Post subject: updated query with changes
Reply with quote

updated query with changes

EXEC SQL
DECLARE SAMPLE CURSOR FOR
SELECT POLY_NO,POLY_ST,AMOUNT,POLYNO_DATE
FROM SAEMPLOYEE
WHERE POLY_NO LIKE :POLYNO_DATE
AND EFFECT_DT =:WS-CURR-DT
END-EXEC.

result needed:
POLY_NO POLYNO_DATE AMOUNT POLY_ST
******* ************ ******** *******
F02256 F02256-2006 $1132.00 FL
F02256 F02256-2007 $1132.00 FL
F02256 F02256-2008 $1132.00 FL
F02256 F02256-2009 $1132.00 FL
F02256 F02256-2010 $1132.00 FL
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 22, 2010 12:58 pm    Post subject:
Reply with quote

What is likely to be filled in in :POLYNO_DATE ? F02256 or something like F022% or %
how many rows have EFFECT_DT = :WS-CURR-DT ?
How often does EFFECT_DT change for a specific row in the lifetime of that row ?

the query is fairly simple.
Only an index beginning with EFFECT_DT, POLYNO will make it faster.
The feasibility of such an index depends on much more than just one query.
Back to top
View user's profile Send private message
sampaul4u

New User


Joined: 27 Mar 2010
Posts: 19
Location: folirida

PostPosted: Fri Oct 22, 2010 1:19 pm    Post subject:
Reply with quote

Hi Guyc,

Thanks for your reply.

I updated the query as below.

EXEC SQL
DECLARE SAMPLE CURSOR FOR
SELECT POLY_NO,POLY_ST,AMOUNT,POLYNO_DATE
FROM SAEMPLOYEE
WHERE POLY_NO LIKE :POLYNO_DATE
END-EXEC.

result needed:
POLY_NO POLYNO_DATE AMOUNT POLY_ST
******* ************ ******** *******
F02256 F022562006 $1132.00 FL
F02256 F022562007 $1132.00 FL
F02256 F022562008 $1132.00 FL
F02256 F022562009 $1132.00 FL
F02256 F022562010 $1132.00 FL

my problem arrives in "WHERE POLY_NO LIKE :POLYNO_DATE"

POLYNO_DATE date like F02256%

ex:F022562006,F022562007

It should pickup all the years of POLYNO.

Is there any better way to change this query to reduce the timing.

Paul
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 22, 2010 2:07 pm    Post subject:
Reply with quote

column POLYNO = F02256
column POLYNO_DATE = F022562006 F02256207

Host Variable :POLYNO_DATE = F02256%

In your query you test column POLYNO like, but you explain the requirement as if you test column POLYNO_DATE like ?

maybe in your query you could write where POLYNO_DATE like :POLYNO_DATE
of
if you had a hostvariable containing F02256 , you could write where POLYNO = :POLYNO

This is ofcourse because the table is not normalized properly. Who in their right mind would repeat the polyno in the column polynodate ? the table should look something like this :
POLYNO, Yr
F02256, 2006
F02256, 2007
Back to top
View user's profile Send private message
steve6

New User


Joined: 10 Jul 2007
Posts: 7
Location: Chennai

PostPosted: Fri Oct 22, 2010 4:41 pm    Post subject:
Reply with quote

Hi,

Just another approach here.

Quote:
my problem arrives in "WHERE POLY_NO LIKE :POLYNO_DATE"


instead of "LIKE" - try the "SUBSTR". As you want to match POLY_NO & POLYNO_DATE and the first 6 chars are expected to be equal... you can try
"WHERE POLY_NO = SUBSTR(:POLYNO_DATE,1,6)"

I am not sure if this will reduce the time - as I couldn't recreate your scenario.
Can you please try this and let us know?
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Oct 22, 2010 5:15 pm    Post subject:
Reply with quote

I think (couldn't verify it) that the use of the intrinsic function SUBSTR is likely to cause data page scans instead of index usage.

I have never heard that it should improve performance over LIKE constructs.

I will recommend going for normalising the keys POLY_NO and POLY_NO_YR, like GuyC suggested, and establish a unique index on those columns, possibly adding other columns to make the key unique.
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
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am


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