topic Re: regarding execution time in Application Development and Automation Discussions

regarding execution time

Former Member — Tue, 10 Jul 2007 13:01:15 GMT

when i am executing this code, it takes a lot of time. even though i hav checked temp[] whether it is initial. But do i need to check ltap[] because the data has been collected from temp to ltap.Also temp has data and since no record matches the for all entries fetches all the data from ltak.

IF NOT lt_temp[] IS INITIAL.

LOOP AT lt_temp INTO ls_temp.

COLLECT ls_temp INTO lt_ltap.

ENDLOOP.

FREE lt_temp.

SELECT lgnum "Warehouse Number

tanum "Transfer order number

FROM ltak

INTO TABLE lt_ltak1

FOR ALL ENTRIES IN lt_ltap

WHERE lgnum EQ lt_ltap-lgnum

AND tanum EQ lt_ltap-tanum

AND trart EQ 'A'

OR trart EQ 'X'.

Re: regarding execution time

Former Member — Tue, 10 Jul 2007 13:17:39 GMT

After the FREE statement, try:


SORT lt_ltap by lgnum tanum.
DELETE ADJACENT DUPLICATES FROM lt_ltap comparing lgnum tanum.

Then do the SELECT.

Rob

Re: regarding execution time

Former Member — Tue, 10 Jul 2007 13:30:56 GMT

it has been sorted afterwards.

do i need to check table ltap for initial since the select query doesnt match any condition and all the data from table ltak is being extracted which is taking a lot of time

Re: regarding execution time

Former Member — Tue, 10 Jul 2007 13:34:43 GMT

You need to sort it before.

If there is data in the original table, there will be data in the final table.

Rob

Re: regarding execution time

Former Member — Tue, 10 Jul 2007 13:49:28 GMT

but isnt it a rule that when u do for all entries for any table, that table has to be checked whether it is initial or not.

Re: regarding execution time

Former Member — Tue, 10 Jul 2007 14:08:49 GMT

You have to know that there is data in the table. You checked the original table, so when you move data into the work table, there must be data there as well.

Rob

Re: regarding execution time

Former Member — Tue, 10 Jul 2007 14:20:40 GMT

Hi,

From the look of your code it seems that your internal table is being built from LTAP (Transfer Order Item) , and then you are reading LTAK (TO Header), could you do a JOIN between LTAP and LTAK?

Re: regarding execution time

Former Member — Wed, 11 Jul 2007 10:08:37 GMT

don'tdo like looping , collocting , free ..etc .

just write 2 select queries using FOR ALL ENTRIES it will take care of it see. i am using the sy-subrc .

*Select FOR ALL ENTRIES command
SELECT bukrs belnr gjahr bldat monat budat xblnr awtyp awkey
  UP TO 100 ROWS
  FROM bkpf
  INTO TABLE it_bkpf.

IF sy-subrc EQ 0.

* The FOR ALL ENTRIES comand only retrieves data which matches
* entries within a particular internal table.
  SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
         dmbtr mwart hwbas aufnr projk shkzg kokrs
    FROM bseg
    INTO TABLE it_bseg
    FOR ALL ENTRIES IN it_bkpf
    WHERE bukrs EQ it_bkpf-bukrs AND
          belnr EQ it_bkpf-belnr AND
          gjahr EQ it_bkpf-gjahr.

ENDIF.

reward points if it is usefull ...

Girish

Re: regarding execution time

Former Member — Thu, 12 Jul 2007 14:55:05 GMT

Besides all other points already mentioned here: Please keep in mind that the "FOR ALL ENTRIES IN" statement translates to the database as:

WHERE TANUM = '111123454566' OR

TANUM = '111123454567' OR

TANUM = '111123454568' OR

.....

This makes only sense if your internal table lt_ltap contains just a handful of records. You can even get a runtime error as the length of the SQL statement created is limited (depending on the DB)

Of course I don't know what your program is supposed to do, but it might be smarter to check only against LGNUM (which are most likely just a few records) and do the checking for the TANUM in memory.

Regards

Frank

Re: regarding execution time

Former Member — Fri, 13 Jul 2007 19:50:45 GMT

Frank - I think this is is a problem for RANGES, not FOR ALL ENTRIES (at least in a DB2 or Oracle environment). I created a table with 500K entries and used it with FOR ALL ENTRIES and it didn't dump. I think the SAP kernel is better at translating FOR ALL ENTRIES for the database than it is for RANGES.

Rob

Re: regarding execution time

Former Member — Mon, 16 Jul 2007 08:52:19 GMT

Hi Rob,

that's quite amazing. I once created an itab with just 8K entries and the FOR ALL ENTRIES statement dumped on me - and this was a DB2 on a z390.

Is the max length of an SQL statement a system setting ?

Regards

Frank

Re: regarding execution time

Former Member — Mon, 16 Jul 2007 12:16:59 GMT

As this is a code sample, make sure that your ENDIF is after the select.

We have all assumed that the problem is with the select, BUT the problem may be with the COLLECT. Have the definition of the LT_LTAP table consist only of the fields lgnum, tanum and an interger field. This way you eliminate your duplicates, quickly.

MattG.

Re: regarding execution time

Former Member — Tue, 17 Jul 2007 08:06:18 GMT

Hi,

Here collect statements takes very much time. So remove this and use any other technique to get your desired result instead of collect.

Reward points if useful,

Aleem.

Re: regarding execution time

Former Member — Mon, 30 Jul 2007 14:37:15 GMT

Frank - What was your error message?

Rob

Re: regarding execution time

KjetilKilhavn — Tue, 31 Jul 2007 08:47:28 GMT

First of all, please use the Code button when you paste code. It makes it much easier to read.

I think your problem is in your select statement.

SELECT lgnum "Warehouse Number
       tanum "Transfer order number
FROM ltak
INTO TABLE lt_ltak1
FOR ALL ENTRIES IN lt_ltap
WHERE lgnum EQ lt_ltap-lgnum
AND tanum EQ lt_ltap-tanum
AND trart EQ 'A'
OR trart EQ 'X'.

Should be changed to

SELECT lgnum "Warehouse Number
       tanum "Transfer order number
FROM ltak
INTO TABLE lt_ltak1
FOR ALL ENTRIES IN lt_ltap
WHERE lgnum EQ lt_ltap-lgnum AND
      tanum EQ lt_ltap-tanum AND
      (    trart EQ 'A'
        OR trart EQ 'X' ).

I always write ANDs at the right side and ORs at the left side of WHERE clauses to remind me that ORs start a new clause, while ANDs are a continuation of an existing clause - adding additional constraints.

Message was edited by Kjetil Kilhavn:

But then again, I could of course be wrong. Perhaps you intend to select all entries where TRART = 'X' irrespective of the LGNUM and TANUM values. I don't think so, but if you do this on purpose you must expect poor performance since TRART is not an indexed field.