topic Re: Fetching data from MSEG table in Application Development and Automation Discussions

Fetching data from MSEG table

Former Member — Wed, 16 Dec 2009 10:07:35 GMT

Dear Abap Gurus ,

I am trying to prepare a vendor Rating Report . There are particular 2 queries which have perfomance issues .The requirement is such that this report has to be executed for the whole financial year .

1st Query :

SELECT BELNR GJAHR BLDAT BUDAT CPUDT FROM EKBE INTO CORRESPONDING FIELDS OF TABLE T_MKPF

WHERE BUDAT IN S_SRDATE

AND BEWTP = 'E'

AND WERKS IN S_PLANT .

Question 1: Is there any additional parameter which i can add in the where clause for getting data from ekbe for vendor rating .

Above query gives 98,000 records .

After This , another below query is taking 12 minutes to get executed which takes data from MSEG . T_EKBE has 98,000 records .

SELECT MBLNR ZEILE CHARG ABLAD EBELN EBELP MATNR MENGE WERKS ABLAD LIFNR FROM MSEG INTO CORRESPONDING FIELDS OF TABLE T_MSEG

FOR ALL ENTRIES IN T_ekbe

WHERE MBLNR = T_ekbe-BELNR

AND MJAHR = T_ekbe-GJAHR

AND MATNR IN S_MATNR

AND WERKS IN S_PLANT

AND BWART = '101'

AND LIFNR IN S_VENDCD .

Question 2 : How to increase speed of this query for MSEG .

Thanks In advance ,

Devendra Singh

Re: Fetching data from MSEG table

Former Member — Wed, 16 Dec 2009 10:14:00 GMT

You can use GJAHR parameter also.

Remoove INTO CORRESPONDIG FIELDS OF statement by defining structure for fields to get good performence

and check if T_ekbe is initial by IF T_EKBE IS NOT INITIAL before FOR ALL ENTRIES SELECT statement

Edited by: kalandar on Dec 16, 2009 11:16 AM

Re: Fetching data from MSEG table

Former Member — Wed, 16 Dec 2009 10:21:50 GMT

Hi,

There are two points of performance improvement which i can see.

1. One PO can have multiple GRN's

. Hence the internal table T_EKBE (98,000) records - can be reduced (in another itab).

by comparing belnr and gjahr


    sort T_EKBE by BELNR GJAHR.
     DELETE ADJACENT DUPLICATES FROM T_EKBE comparing BELNR GJAHR.

The above can be done in another internal table also if you want T_EKBE for other uses also.

So when we use subsequently in FOR ALL ENTRIES, the database will have to search for LESS THAN 98,000 comparisons.

2. In database table MSEG,

secondary indexes already exist for MATNR, WERKS (and ofcourse the primary keys MBLNR and MJAHR already have indexes)

These two fields BWART and LIFNR do not have any secondary indexes.

If we create secondary indexes on these two fields, it will surely improve the performance.

Hope this helps.

regards,

amit m.

Re: Fetching data from MSEG table

Former Member — Thu, 17 Dec 2009 05:05:57 GMT

Hi Amit Sir ,

Thanks for your reply . Using Delete Adjacent Statement has reduced the time by 4 min . I have been instructed not to use secondary index bec it will create unnecessary burden. But still the report takes 19 min to get report for the whole financial year . Is there any other way to reduce the time in the above 2 queries .

Thanks in Advance ,

Devendra

Re: Fetching data from MSEG table

Former Member — Thu, 17 Dec 2009 05:20:12 GMT

Still Time Limit being 19 min

Re: Fetching data from MSEG table

Nawanandana — Thu, 17 Dec 2009 05:47:30 GMT

Try to create the index for the both table, EKBE and MSEG

Secand this is tel to ur BASIS guy to run the statictic for the both table

regard

nawa

Re: Fetching data from MSEG table

Nawanandana — Thu, 17 Dec 2009 05:47:47 GMT

Try to create the index for the both table, EKBE and MSEG

Secand this is tel to ur BASIS guy to run the statictic for the both table

regard

nawa

Re: Fetching data from MSEG table

Former Member — Thu, 17 Dec 2009 05:48:40 GMT

Hi again,

In my opinion and as per my experience, there is no further way (major way) to reduce the time limit.

In my programs, I have done like that only. I have created secondary index (as per the where conditions in select query) and the performance has improved DRASTICALLY.

Some people say it creates burden/overhead. But this is not true as per my opinion. We have to create secondary index once only, and the rest is taken care by the database. Maintaining/updating an index (whenever a record is created / deleted in table is taken care by the database itself, and it really does not have any burden.)

So as per my opinion, secondary index is justified. If these MSEG fields (where condition fields) are used in other programs, then they will also automatically get speeded up. hope this helps.

regards,

amit m.