09-18-2008 10:31 AM
Hi,
i do this select with while loop.
the logic is that the select is running until i get in ukurs data ,
otherwise it add 1 to datecur and and try to find ukurs.
the problem is that in prod it run to long there is a way to improve this performance?
Regards
ukurs = ''.
WHILE ukurs = ''.
SELECT SINGLE ukurs
FROM tcurr
INTO ukurs
WHERE kurst = '1002' AND
fcurr = wa_dp-currency AND
tcurr = 'USD0' AND
gdatu = datecur.
datecur = datecur + 1.
ENDWHILE.
09-18-2008 10:59 AM
hi Ricardo,
if I understand right you need the exchange rate for a given date (datecur) or closest to that date in the future. You can replace the WHILE-ENDWHILE with the following SELECT:
SELECT ukurs MIN( gdatu )
INTO ...
FROM tcurr
WHERE ... gdatu GE datecur
GROUP BY ukurs gdatu.
The trick is to use SELECT ... MIN( gdatu ) which will only select the possible lowest date (pls. note the comparison in the WHERE: gdatu GE datecur
Of course this is not complete, you have to add the other conditions as well.
hope this helps
ec
09-18-2008 10:37 AM
It would probably be best to SELECT all kurst = '1002' entries into an internal table and then loop around that. This is preferable to doing many SELECT statements within a loop.
However how many entries are in your TCURR table? The current system I work on only has 200 so I can't equate it to a performance issue if I were to run your code.
09-18-2008 10:39 AM
Hi,
If you write the SELECT Query inside Loop it will take time to execute. And performance will decrease.
So if possible Avoid Select inside Loop
09-18-2008 10:43 AM
Also reconsider the checking of ukurs = '' since it is not a character-based field. Your loop may not actually be exiting
09-18-2008 10:59 AM
hi Ricardo,
if I understand right you need the exchange rate for a given date (datecur) or closest to that date in the future. You can replace the WHILE-ENDWHILE with the following SELECT:
SELECT ukurs MIN( gdatu )
INTO ...
FROM tcurr
WHERE ... gdatu GE datecur
GROUP BY ukurs gdatu.
The trick is to use SELECT ... MIN( gdatu ) which will only select the possible lowest date (pls. note the comparison in the WHERE: gdatu GE datecur
Of course this is not complete, you have to add the other conditions as well.
hope this helps
ec
09-18-2008 11:52 AM
Hi Eric,
Thanks u understand right ,
but how your select is now if it dont find value,
go to next day and so on?
Regards
09-18-2008 12:15 PM
sorry, what i wrote aboe is not correct, it still selects all lines. I think you have to go for array select:
SELECT gdatu ukurs
FROM tcurr
INTO TABLE ...
WHERE ... gdatu GE decdate
this will select all possible entries in one go
SORT itab BY gdatu.
READ TABLE itab INTO ... INDEX 1.
internal table will be sorted with date, which means, the first line is what you'll finally need.
hope this helps (finally)
ec