topic Re: SELECT query performance issue in Application Development and Automation Discussions

SELECT query performance issue

Former Member — Sat, 11 Sep 2010 09:16:50 GMT

Hello experts!!!

I am facing the performance issue in the below SELECT query. Its taking long time to execute this query.

Please suggest how can i improve the performance of this query.

SELECT MBLNR MATNR LIFNR MENGE WERKS BUKRS LGORT BWART INTO CORRESPONDING FIELDS OF TABLE IT_MSEG

FROM MSEG

WHERE MATNR IN S_MATNR

AND LIFNR IN S_LIFNR

AND WERKS IN S_WERKS

AND BUKRS IN S_BUKRS

AND XAUTO = ''

AND BWART IN ('541' , '542' , '543' , '544', '105' , '106').

Thanks in advance.

Regards

Ankur

Re: SELECT query performance issue

Former Member — Sat, 11 Sep 2010 10:19:26 GMT

Hi Ankur,

This query is related to the performancee tuning. So you could have opted for the [Forum: ABAP Performance and Tuning|; for quick anwers.

By the way your select query tries to hit MSEG with none of the primary keys & top of that it uses INTO CORRESPONDING FIELDS.

Try to use at least some of the primary key fields of MSEG like MBLNR MJAHR & ZEILE.

Regards

Abhii

Re: SELECT query performance issue

Former Member — Sat, 11 Sep 2010 10:28:25 GMT

hi abhii!!!

thanks for the response.

actually in this query i want all the transaction of material to a selected vendor from selection screen. there is no concept of fin year or document number. i need to find out the opening stock and closing stock.

still your suggestion was helpful.

Regards

Ankur

Re: SELECT query performance issue

GauthamV — Sat, 11 Sep 2010 10:57:28 GMT

Check for available secondary indexes of that table and create one if required.

Please post performance related questions in the other forum as suggested by Abhi in the above post.

Re: SELECT query performance issue

Former Member — Sat, 11 Sep 2010 12:26:17 GMT

Then you must have to look for the already available indexes in the table MSEG. I have checked in my system, there is an index on named M Material documents for material with at least three of the fields you have used in the select clause.

Try using thee same.

Otherwise you can create your own index with the fields you have used in the where clause.

Regards

Abhii

Edited by: Abhii on Sep 11, 2010 2:26 PM

Edited by: Abhii on Sep 11, 2010 2:28 PM

Re: SELECT query performance issue

Former Member — Sat, 11 Sep 2010 13:48:14 GMT

create index for these table with the fields required.

Thanks and regards

S.Janagar

Re: SELECT query performance issue

Clemenss — Sat, 11 Sep 2010 21:41:26 GMT

Hi Ankur,

the MSEG index for material is

Index MSEG~M

MANDT

MATNR

WERKS

LGORT

BWART

SOBKZ

It could be used very efficient if you supply values for MATNR, WERKS and LGORT.

There is no index on LIFNR. IKf you want the data for specific vendor(s), you should select from EKKO first, ir has index Index EKKO~1

MANDT

LIFNR

EKORG

EKGRP

BEDAT

You can JOIN EKKO and EKBE to get the BSEG key fields GJAHR BELNR BUZEI directly.

I don't know your details but I think you can get all you need from EKKO and EKBE. You may also consider EKPO as is has a material index Index EKPO~1

MANDT

MATNR

WERKS

BSTYP

LOEKZ

ELIKZ

MATKL

Do you really need the (much bigger) MSEG?

Regards,

Clemens

Re: SELECT query performance issue

ravi_lanjewar — Sun, 12 Sep 2010 05:32:54 GMT

Hi Ankur,

Why you are reading data from table MSEG ?


SELECT MBLNR MATNR LIFNR MENGE WERKS BUKRS LGORT BWART INTO CORRESPONDING FIELDS OF TABLE IT_MSEG
FROM MSEG
WHERE MATNR IN S_MATNR
AND LIFNR IN S_LIFNR
AND WERKS IN S_WERKS
AND BUKRS IN S_BUKRS
* AND XAUTO = ''
AND BWART IN ('541' , '542' , '543' , '544', '105' , '106').

You can read data from AUFM table which much faster and less data than the MSEG, But company code (ie BUKRS) is not there, So read company code from another table like MKPF.

Don't used CORRESPONDING FIELDS OF of database table having more fields, It take more time re-arrange the data in your order.

Re: SELECT query performance issue

Former Member — Sun, 12 Sep 2010 21:47:15 GMT

If the records are really huge in number, as its MSEG, you can probably go for SELECT.....ENDSELECT specifying the PACKAGE SIZE.

Re: SELECT query performance issue

Clemenss — Mon, 13 Sep 2010 11:51:37 GMT

Don't used CORRESPONDING FIELDS OF of database table having more fields, It take more time re-arrange the data in your order.

This is simply not true. Data are not re-arranged at all. Trace and analyze SELECT statement for both alternatives.

Always use INTO CORRESPONDING FIELDS OF because it is fail-safe and does not cost performance.

Regards,

Clemens

Re: SELECT query performance issue

Former Member — Mon, 13 Sep 2010 12:04:07 GMT

Always use INTO CORRESPONDING FIELDS OF because it is fail-safe and does not cost performance.

Wish I could give you 10-points for that, but I leave that to the OP

Re: SELECT query performance issue

Former Member — Mon, 13 Sep 2010 16:30:04 GMT

Hi Ankur,

Give this a try.

SELECT b~mblnr
       b~matnr
       b~lifnr
       b~menge
       b~werks
       b~bukrs
       b~lgort
       b~bwart
FROM        mard AS a
INNER JOIN  mseg AS b
ON  a~matnr EQ b~matnr
AND a~werks EQ b~werks
AND a~lgort EQ b~lgort
INTO TABLE it_mseg
WHERE a~matnr IN s_matnr
AND   a~werks IN s_werks
AND   b~bwart IN ('541' , '542' , '543' , '544', '105' , '106')
AND   b~lifnr IN s_lifnr
AND   b~bukrs IN s_bukrs.

Re: SELECT query performance issue

Clemenss — Mon, 13 Sep 2010 16:33:22 GMT

or with some readability:

SELECT mseg~mblnr
       mseg~matnr
       mseg~lifnr
       mseg~menge
       mseg~werks
       mseg~bukrs
       mseg~lgort
       mseg~bwart
FROM        mard
INNER JOIN  mseg
ON  mard~matnr EQ mseg~matnr
AND mard~werks EQ mseg~werks
AND mard~lgort EQ mseg~lgort
INTO TABLE it_mseg
WHERE mard~matnr IN s_matnr
AND   mard~werks IN s_werks
AND   mseg~bwart IN ('541' , '542' , '543' , '544', '105' , '106')
AND   mseg~lifnr IN s_lifnr
AND   mseg~bukrs IN s_bukrs.

Regards,

Clemens

Re: SELECT query performance issue

Former Member — Tue, 14 Sep 2010 04:15:39 GMT

Hello

thanks for the response. Now i am using index M of MSEG table.

But i have some more fileds in my Select query as compared to that in Index M of the table. Although I am putting the fields in the same order as in the index.

But as the fields in SELECT query are more than in Index, will index work?

Please refer the below Select query.

SELECT MATNR WERKS LGORT BWART SOBKZ LIFNR MBLNR MENGE BUKRS INTO CORRESPONDING FIELDS OF TABLE IT_MSEG

FROM MSEG

WHERE MATNR IN S_MATNR

AND WERKS IN S_WERKS

AND BWART IN ('541' , '542' , '543' , '544', '105' , '106').

AND LIFNR IN S_LIFNR

AND BUKRS IN S_BUKRS.

Please reply

Re: SELECT query performance issue

Former Member — Tue, 14 Sep 2010 05:15:22 GMT

Thanks for the reply.

I don't know but LIFNR field is blank in AUFM table.

Can you help me what could be the reason.

No doubt AUFM is much faster than MSEG.

Thanks

Ankur

Re: SELECT query performance issue

ravi_lanjewar — Tue, 14 Sep 2010 13:23:28 GMT

Hi Ankur,


I don't know but LIFNR field is blank in AUFM table

I don't know the exact reason of it,

But know you have the key fields of MSEG table now (ie. after reading data from aufm) using that you can read the table mseg for LIFNR fields.

It will give better performance than direct fetch data from mseg table.

Re: SELECT query performance issue

Former Member — Tue, 14 Sep 2010 13:32:08 GMT

Moderator message - Please see before posting - post locked

If you have a performance problem, there are some testing that you have to do before posting here. Not doing that just wastes everyone's time.

Rob{color