07-03-2009 9:22 AM
Guys,
Im having problem with this select statement. It takes much time just to get single record.
The problem is with accessing the LTAP table and the ORDER BY DESCENDING statement.
The objective of this select statement is to get the non blocked storage bin which is used by latest transfer order number.
If the latest transfer order no storage bin is blocked, then it will loop and get the 2nd latest transfer order no's storage bin and
checks whether it blocked or not. It will keep looping.
The secondary index has been created but the it still taking much time (3 minutes for 10K records in LTAP)
Secondary Indexes:
a) LTAP_M ->MANDTLGNUM PQUIT MATNR
b)LTAP_L ->LGNUM PQUIT VLTYP VLPLA
Below is the coding.
******************Start of DEVK9A14JW**************************
SELECT ltaptanum ltapnlpla ltap~wdatu INTO (ltap-tanum, ltap-nlpla, ltap-wdatu)
UP TO 1 ROWS
FROM ltap INNER JOIN lagp "DEVK9A15OA
ON lagplgnum = ltaplgnum
AND lagplgtyp = ltapnltyp
AND lagplgpla = ltapnlpla
WHERE lagp~skzue = ' '
AND ltap~pquit = 'X'
AND ltap~matnr = ls_9001_scrn-matnr
AND ltap~lgort = ls_9001_scrn-to_lgort
AND ltap~lgnum = ls_9001_scrn-lgnum
AND ltap~nltyp = ls_9001_scrn-nltyp
ORDER BY tanum DESCENDING.
ENDSELECT.
IF sy-subrc EQ 0.
ls_9001_scrn-nlpla = ltap-nlpla.
EXIT.
ENDIF.
******************End of DEVK9A14JW**************************
07-03-2009 11:42 AM
Try removing the "ORDER BY tanum DESCENDING" addition and use SORT abap statement .
This would decrease Database Lead time
Thanks
A J R
07-03-2009 2:08 PM
Hi,
which of your WHERE conditions are filled? all of them?
WHERE lagp~skzue = ' '
AND ltap~pquit = 'X'
AND ltap~matnr = ls_9001_scrn-matnr
AND ltap~lgort = ls_9001_scrn-to_lgort
AND ltap~lgnum = ls_9001_scrn-lgnum
AND ltap~nltyp = ls_9001_scrn-nltyp
ORDER BY tanum DESCENDING.
For your index... have you tried it like this?
it should contain the (selective) fields that are used with = and the TANUM field.
Kind regards,
Hermann
07-06-2009 9:42 AM
> Im having problem with this select statement. It takes much time just to get single record.
This is not true. Together with the ORDER BY the UP TO 1 ROWS does not read 1 record but prepare all records, orders them and return one record, i.e. the largest in sort order.
You must check what you need, either you need the largest record, then this can be your only possible solution.
If you need only one recoird then the ORDER BY does not make sense.
If you need the single largest record, then sometimes the aggregate function MAX can be an alternative.
I did not look at the index support, this can always be a problem.
Siegfried
07-07-2009 7:43 PM
Hi,
I guess you sort descending is used to get the last tanum number.
If so, you could try something like this:
SELECT MAX( ltap~tanum ) INTO ltap-tanum
FROM ltap INNER JOIN lagp "DEVK9A15OA
ON lagp~lgnum = ltap~lgnum
AND lagp~lgtyp = ltap~nltyp
AND lagp~lgpla = ltap~nlpla
WHERE lagp~skzue = ' '
AND ltap~pquit = 'X'
AND ltap~matnr = ls_9001_scrn-matnr
AND ltap~lgort = ls_9001_scrn-to_lgort
AND ltap~lgnum = ls_9001_scrn-lgnum
AND ltap~nltyp = ls_9001_scrn-nltyp.
SELECT SINGLE ltap~tanum ltap~nlpla ltap~wdatu INTO (ltap-tanum, ltap-nlpla, ltap-wdatu)
FROM ltap
WHERE tanum = ltap-tanum.
Hope can help you,
Frisoni
07-09-2009 5:22 AM
I think in your case going for a max statement will be good and i agree with Siegfried its not selecting one record but selecting all and then returning one.
Nafran
07-09-2009 5:31 AM
Hi:
Remove the join statement within select...endselect.
instead of that fatch the data in the primary table first and then take the data from 2nd table based on condition for For all entries....
Regards
Shashi
07-09-2009 9:23 AM
> Remove the join statement within select...endselect.
> instead of that fatch the data in the primary table first and then take the data from
> 2nd table based on condition for For all entries....
sometimes there is only one comment possible: nonsense !