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

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

Former Member — Fri, 03 Jul 2009 08:23:43 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: SELECT from LTAP table takes much time (Sort in Database layer)

Former Member — Fri, 03 Jul 2009 08:49:53 GMT

Instead of using select....endselect if u can fetch ur data in an internal table and instead of doing ORDER BY in select query

You can use SORT itab by DESCENDING.

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

Former Member — Fri, 03 Jul 2009 08:55:54 GMT

Yep I tried that too, select from ltap into internal table, SORT it descdending by tanum. Then I loop and check with LAGP table.

But the problem is during fetching the records from LTAP into internal table. It still takes much time.(3 minutes for 10K records).

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

Former Member — Fri, 03 Jul 2009 08:57:35 GMT

In your code you are using Select Endselect,to avoid performence issue we should not use this statement untill it is really very necessary.

You can use the statement

Selet (fields) from database table into table itab.

so you can use the below code for example

SELECT single ltap_{tanum ltap}nlpla ltap~wdatu

INTO table itab

FROM ltap INNER JOIN lagp

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.

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

Former Member — Fri, 03 Jul 2009 08:59:23 GMT

Hmm.. actually I need to use the SELECT n ENDSELECT because of UP TO 1 ROWS.

I need to use UP TO 1 ROWS because i select the LTAP not using key fields.

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

Former Member — Fri, 03 Jul 2009 09:00:56 GMT

Try using for all entries instead of join as in ur query in ur where clause u are using non-key fields and not all the key fields are there in where clause...so join will reduce performance

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

Former Member — Fri, 03 Jul 2009 09:10:02 GMT

Yep I tried that too like below. But the performance is better if I use INNER JOIN than FOR ALL ENTRIES.

The problem is when select from the LTAP table.

I have used OPEN CURSOR statement also. And limit the ltap record by 50 but still takes much time accessing LTAP table. Weird!

I even have used MAX GROUP BY in my above INNER JOIN statemen to replace the GROUP BY DESCENDING, but it returned me incorrect result maybe because of the INNER JOIN.

SELECT tanum nlpla wdatu lgnum nltyp INTO TABLE t_ltap

WHERE lgnum = ls_9001_scrn-lgnum

AND pquit = 'X'

AND matnr = ls_9001_scrn-matnr

AND lgort = ls_9001_scrn-lgort

AND nltyp = ls_9001_scrn-nltyp

IF t_ltap[] IS NOT INITIAL.

SORT t_ltap DESCENDING BY lgnum nltyp nlpla tanum.

DELETE ADJACENT DUPLICATES FROM t_ltap COMPARING lgnum nltyp nlpla.

SORT t_ltap DESCENDING BY tanum.

SELECT lgpla INTO TABLE lt_lgap

FROM lagp FOR ALL ENTRIES IN t_ltap

WHERE blablabla

ENDIF.

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

Former Member — Fri, 03 Jul 2009 09:22:42 GMT

Use into corresponding fields of table instead of into table

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

Former Member — Fri, 03 Jul 2009 09:25:55 GMT

Nope, INTO CORRESPONDING slower than INTO TABLE because it transfer the value by the name of the fields while INTO TABLE is by the sequence of the fields.

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

Former Member — Fri, 03 Jul 2009 09:28:12 GMT

Ans also see the order in which you are selecting fields from LTAP it should be:-

select lgnum tanum wdatu nltyp from ltab and so on

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

Former Member — Fri, 03 Jul 2009 09:28:44 GMT

ORDER BY clause directly put a load on Database.

SO never use order by ever in your queries.

Instead always use sort itab by field names.

Secondly Try to minimise the where clause conditions during fetching data in a query.

instead delete them depending upon the conditions.

Regds,

Anil

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

Former Member — Fri, 03 Jul 2009 09:32:48 GMT

Yep! But I cant avoid to use sorting in database layer. Tried sort in application server as well. But the problem not during sort

but during access to LTAP table.

Yes that is the minimum where clause conditions I could use otherwise the records selection could be 3,4 times more.

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

Former Member — Fri, 03 Jul 2009 09:34:20 GMT

did u try to change the order in your select query...............bcoz of it performance becomes slow keep it according to the order defined in table.

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

Former Member — Fri, 03 Jul 2009 09:35:41 GMT

Yep! Tried that too, but it dont improve at all. I checked the ST05, the response is about the same.

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

Former Member — Fri, 03 Jul 2009 10:13:41 GMT

HI,

In your original select:-

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.

in where clause use :

WHERE

ltap~lgnum = ls_9001_scrn-lgnum

AND ltap~pquit = 'X'

AND ltap~matnr = ls_9001_scrn-matnr

AND lagp~skzue = ' '

AND ltap~lgort = ls_9001_scrn-to_lgort

AND ltap~nltyp = ls_9001_scrn-nltyp

ORDER BY tanum DESCENDING.

since there is already an index 'M' defined in the syatem with lgnum,pquit & matnr as the key. If still not satisfied you may have to think about creating a secondary index via SE11 with fields appearing in your 'where' clause as the key. ( obviously in the same order i.e. lgnum,pquit,matnr,skzue,lgort,nltyp )

Regards

Raju Chitale