topic Re: Improve AUFM SELECT Statement in Application Development and Automation Discussions

Improve AUFM SELECT Statement

Former Member — Tue, 20 Mar 2007 16:12:36 GMT

Hi All,

I was wondering have any of you had issues when selecting from AUFM. It is working fine in the test server however in production it is short dumping saying that I am pulling back too many records. Is there any way to solve this?

Thanks for any help.

My statement is this:


  SELECT MBLNR MJAHR ZEILE BUDAT MATNR AUFNR EBELN EBELP SAKTO
         FROM AUFM INTO TABLE ITAB_AUFM
          WHERE BUDAT IN S_BUDAT
          AND  AUFNR IN R_AUFNR
          AND  BWART IN S_BWART
          AND  MATNR IN S_MATNR
          AND  LIFNR IN S_LIFNR.

Re: Improve AUFM SELECT Statement

Former Member — Tue, 20 Mar 2007 16:15:55 GMT

Try adding MBLNR MJAHR ZEILE in where condition.

if not try creating an index with the fields mentioned in the where condition in the same order.

The WHERE condition you are using doesnt have primary keys or it doesnt trace any index.

**reward if helpful

regards,

madhumitha

Re: Improve AUFM SELECT Statement

Former Member — Tue, 20 Mar 2007 16:20:20 GMT

Hi!

Do not start the program without any restrictions. Make obligatory at least the S_BUDAT field on your selection screen, and fill it with a default value, for example with the previous month. You can do it in the INITIALIZATION. event.

Regards

Tamá

Re: Improve AUFM SELECT Statement

Former Member — Tue, 20 Mar 2007 16:22:50 GMT

I was afraid I was going to have to index it. I think it might be the only thing I can do but I was hoping someone else might have an idea.

There are restrictions on the selection but if the user selects any more than 2 months it crashes.

Thanks anyway

Re: Improve AUFM SELECT Statement

Former Member — Tue, 20 Mar 2007 18:26:26 GMT

Hi Colm,

You may consider doing cpl of things..

You may revise the sequence of field extraction..

MBLNR MJAHR ZEILE BUDAT MATNR AUFNR EBELN EBELP SAKTO to

MBLNR MJAHR ZEILE BUDAT MATNR EBELN EBELP AUFNR SAKTO

And also based on data size you may use only BUDAT in where clause

and delete the internal table for rest of the conditions..

Jus do some trials on these method and i am sure you wud c a considerable change..

Rakesh

PS: Please close the thread if your question is answered..

Re: Improve AUFM SELECT Statement

Former Member — Tue, 20 Mar 2007 20:16:49 GMT

if it fails due to lack of memory and you are OK with the performance, you can redesign your select (if it's OK for your program) to process data in packages - check help for "package size " addition to the select statement.

OR you may use field-groups instead of internall table if it's just a report and you do not have any complex logic involving itab_aufm.

Re: Improve AUFM SELECT Statement

Former Member — Wed, 21 Mar 2007 01:54:38 GMT

hi,can you explain field-groups?

if the prg have the complex logic involving itab_aufm? we can't use field-groups???

is it the limit of the field-groups???

Re: Improve AUFM SELECT Statement

Former Member — Wed, 21 Mar 2007 11:42:06 GMT

I think the best way to do this is to create a work around. The reason it is failing is due to the large amount of data being pulled back at once.

If i split the dates in half by picking a date in the middle and doing two select statements instead it should be run OK.

Performance will be slower but the business user just wants the report to run.

Thanks for help.

Re: Improve AUFM SELECT Statement

Former Member — Thu, 22 Mar 2007 11:21:42 GMT

Consider why you are getting all the data in one hit. If this is the data that drives all the other processes then split your select, by package size. This may not be slower, as the data may not be paged.

If however you are going to read / loop this internal table then you search conditions will be critical. Read the Rob Burbank blog <a href="/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops">Performance of Nested Loops</a>.

MattG.