topic Re: Performance issue with a select statement in Application Development and Automation Discussions

Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 12:54:17 GMT

The follow code is the select statement in question. This statement works beautifully unless I input a matnr or matkl as a parameter.

SELECT b~matnr a~budat b~menge a~mblnr a~mjahr b~bwart b~werks b~meins c~matkl c~mtart d~maktx b~shkzg
  INTO TABLE it_detail_temp
  FROM mkpf AS a
    INNER JOIN mseg AS b
          ON b~mblnr = a~mblnr
          AND b~mjahr = a~mjahr
    INNER JOIN mara AS c
          ON c~matnr = b~matnr
    INNER JOIN makt AS d
          ON d~matnr = c~matnr
                 WHERE  a~budat IN s_budat
                AND b~bwart IN s_bwart
               AND b~werks IN s_werks
                AND b~matnr IN s_matnr
                AND c~matkl IN s_matkl.

If I input matnr or matkl the program does not run, it just hangs there. I don't understand why either of these parameters would cause the program to freeze at the select. Any help would be greatly appreciated.

Regards,

Davis.

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:14:40 GMT

Could you please check wether any of the accessed tables has a non-standard index created on them? If there are any indexes created please post them incuding their fields.

I would say you've got an Index / access problem.

You could also compare the result of SE30 with and without matnr an matkl in the selection to see what the SQL is really doing.

Best regards,

Alfonso

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:31:02 GMT

Alfonso, is there an easy way to see if any of the tables have a non-standard index?

Regards,

Davis

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:31:53 GMT

Hi Davis,

Pl let me know whether u want MATKL in selection field. is it ok if u remove it from selection as i think there is not such need. As per my knowlege every material in one plant is having only one materil group.

also pl check whether that material group is belong to that material or not.

if we consider performance of the program then SAP recomend to use only 2-3 tables in inner join.

Pl check above points and in case of issue pl reply the same.

if this solve ur problem the give appropriate points.

bye thanks and regards,

Kapil Soni

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:34:41 GMT

Davis,

SE11 --> Inser table name, click on display

"Indexes" Button on top of page (right side) a pop-up with all indexes for that table should appear.

Check it out!

Alfonso

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:41:26 GMT

There is one on matkl (MARA-L). This is a non-unique index created by SAP in package ML. The select isn't so bad when you input at matnr but it is with matkl. There is an index with matnr (MSEG-M) in package MB.

I hope this is the information were asking for,

Regards,

Davis

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:44:39 GMT

Kapil, Matkl is not needed but it was requested by the users. It is in there so they can look at only certain material groups. For input I have a date range, movement types, and plants (all select-options). They are all required. We have matkl and matnr as optional inputs (also select-options) so they can further refine the search.

Thanks for you response,

Davis.

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:48:46 GMT

Hi Davis,

If you have inner joins on more than 2 tables it will effect the performance a lot.

Its advisible to use FOR ALL ENTRIES and get the results.

As You are getting only MAKTX from MAKT for MATNRs, so get the MATNRs and their descriptions by using FOR ALL ENTRIES on the result internal table of MSEG and MARA.

You can one more condition in your WHERE clause.

SELECT b_{matnr a}budat b_{menge a}mblnr a_{mjahr b}bwart b_{werks b}meins c_{matkl c}mtart d_{maktx b}shkzg

INTO TABLE it_detail_temp

FROM mkpf AS a

INNER JOIN mseg AS b

ON b_{mblnr = a}mblnr

AND b_{mjahr = a}mjahr

INNER JOIN mara AS c

ON c_{matnr = b}matnr

INNER JOIN makt AS d

ON d_{matnr = c}matnr

WHERE a~budat IN s_budat

AND b~bwart IN s_bwart

AND b~werks IN s_werks

AND b~matnr IN s_matnr

AND c~matkl IN s_matkl

<b> AND D~SPRAS = SY-LANGU.</b>

Thanks,

Vinay

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 13:56:30 GMT

SELECT b_{matnr a}budat b_{menge a}mblnr a_{mjahr b}bwart b_{werks b}meins c_{matkl c}mtart d_{maktx b}shkzg

INTO TABLE it_detail_temp

FROM mkpf AS a

INNER JOIN mseg AS b

ON b_{mblnr = a}mblnr

AND b_{mjahr = a}mjahr

INNER JOIN mara AS c

ON c_{matnr = b}matnr

WHERE a~budat IN s_budat

AND b~bwart IN s_bwart

AND b~werks IN s_werks

AND b~matnr IN s_matnr

AND c~matkl IN s_matkl.

loop at it_detail_temp.

select single maktx from makt

where matnr = it_detail_temp-matnr.

modify it_detail_temp.

endloop.

i had not done anything extra only keep join on 3 tables.

also make sure that there is Foreign key relationshipis set for ( Matnr and Matkl ) at selection screen.

bye

Regards,

Kapil Soni

Re: Performance issue with a select statement

Former Member — Mon, 26 Mar 2007 14:28:48 GMT

Thanks to all for their suggestions. I have not been able to get any of the suggested fixes to work. Vinaykumar and Kapil, thanks for your suggestions but they didn''t seem to improve the performance. This program will take up to an hour to run anyway (without using matkl or matnr) so I think I'll leave it as is for now and if they want to run a the report with either of the two they will have to run it overnight.

Thanks again,

Davis.