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

Help needed in Query Tuning


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 1281
Location: Belgium

PostPosted: Fri Oct 22, 2010 12:58 pm
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
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: 1281
Location: Belgium

PostPosted: Fri Oct 22, 2010 2:07 pm
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
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
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 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