View previous topic :: View next topic
|
Author |
Message |
sampaul4u
New User
Joined: 27 Mar 2010 Posts: 19 Location: folirida
|
|
|
|
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 |
|
|
sampaul4u
New User
Joined: 27 Mar 2010 Posts: 19 Location: folirida
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
sampaul4u
New User
Joined: 27 Mar 2010 Posts: 19 Location: folirida
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
steve6
New User
Joined: 10 Jul 2007 Posts: 7 Location: Chennai
|
|
|
|
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 |
|
|
Kjeld
Active User
Joined: 15 Dec 2009 Posts: 365 Location: Denmark
|
|
|
|
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 |
|
|
|