topic Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer) in Application Development and Automation Discussions

Problem : SELECT from LTAP table takes much time (Sort in Database layer)

Former Member — Fri, 03 Jul 2009 08:22:30 GMT

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 ltap_{tanum ltap}nlpla ltap~wdatu INTO (ltap-tanum, ltap-nlpla, ltap-wdatu)

UP TO 1 ROWS

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

ORDER BY tanum DESCENDING.

ENDSELECT.

IF sy-subrc EQ 0.

ls_9001_scrn-nlpla = ltap-nlpla.

EXIT.

ENDIF.

******************End of DEVK9A14JW**************************

Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer)

Former Member — Fri, 03 Jul 2009 10:42:06 GMT

Try removing the "ORDER BY tanum DESCENDING" addition and use SORT abap statement .

This would decrease Database Lead time

Thanks

A J R

Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer)

HermannGahm — Fri, 03 Jul 2009 13:08:50 GMT

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

Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer)

Former Member — Mon, 06 Jul 2009 08:42:41 GMT

> 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

Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer)

guilherme_frisoni — Tue, 07 Jul 2009 18:43:06 GMT

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

Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer)

Former Member — Thu, 09 Jul 2009 04:22:26 GMT

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

Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer)

Former Member — Thu, 09 Jul 2009 04:31:03 GMT

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

Re: Problem : SELECT from LTAP table takes much time (Sort in Database layer)

Former Member — Thu, 09 Jul 2009 08:23:44 GMT

> 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 !