getting short dump on VBFA table ABAP Query.

[Former Member]

Hi All,

when i am doing performance tuning on z program (Freight Agent Details), iam getting the runtime error on  2nd select statement as 'Time Limit Exceeded'  which is given below:

SELECT mblnr

            mjahr

            zeile

            matnr

            werks

            kunnr

            wempf

            bukrs

            FROM mseg

            INTO TABLE l_i_mseg

            FOR ALL ENTRIES IN l_i_mkpf

            WHERE mblnr = l_i_mkpf-mblnr

            AND mjahr = l_i_mkpf-mjahr

            AND werks = p_werks

            AND kunnr IN s_kunnr

            AND bukrs = p_bukrs

            AND bwart IN (lc_text_601,lc_text_641).

     IF sy-subrc IS INITIAL

       AND l_i_mseg IS NOT INITIAL.

       l_i_mseg_tmp[] = l_i_mseg[].

       SORT l_i_mseg_tmp BY mblnr mjahr.

       DELETE ADJACENT DUPLICATES FROM l_i_mseg_tmp COMPARING mblnr mjahr.

       SELECT  vbelv

               vbeln

               vbtyp_v

               mjahr

               FROM vbfa

               INTO TABLE l_i_vbfa

               FOR ALL ENTRIES IN l_i_mseg_tmp

               WHERE vbeln   = l_i_mseg_tmp-mblnr

               AND   vbtyp_n = 'R'.

please help me out on  this issue.

Regards,

Sandhya.

[Former Member]

Hi All,

when i am doing performance tuning on z program (Freight Agent Details), iam getting the runtime error on  2nd select statement as 'Time Limit Exceeded'  which is given below:

SELECT mblnr

            mjahr

            zeile

            matnr

            werks

            kunnr

            wempf

            bukrs

            FROM mseg

            INTO TABLE l_i_mseg

            FOR ALL ENTRIES IN l_i_mkpf

            WHERE mblnr = l_i_mkpf-mblnr

            AND mjahr = l_i_mkpf-mjahr

            AND werks = p_werks

            AND kunnr IN s_kunnr

            AND bukrs = p_bukrs

            AND bwart IN (lc_text_601,lc_text_641).

     IF sy-subrc IS INITIAL

       AND l_i_mseg IS NOT INITIAL.

       l_i_mseg_tmp[] = l_i_mseg[].

       SORT l_i_mseg_tmp BY mblnr mjahr.

       DELETE ADJACENT DUPLICATES FROM l_i_mseg_tmp COMPARING mblnr mjahr.

       SELECT  vbelv

               vbeln

               vbtyp_v

               mjahr

               FROM vbfa

               INTO TABLE l_i_vbfa

               FOR ALL ENTRIES IN l_i_mseg_tmp

               WHERE vbeln   = l_i_mseg_tmp-mblnr

               AND   vbtyp_n = 'R'.

please help me out on  this issue.

Regards,

Sandhya.

[yogendra_bhaskar]

Hi Sandhya Raju ,

First of all see that how much time the 1st select statement is taking , since mseg is a cluster table , much of the performance issue may be due to this .

regards,

Yogendra Bhaskar

[former_member129652]

Hi Yogendra Bhaskar,

   MSEG is a transparent table.  But, I believe that the first SELECT will take a lot of time because it does not hit the primary key or any index.

To Sandhya Raju:

   You can create a new index for MSEG, or run the report in the background.

   Try discussing with your Basis.  Maybe he will increase the timeout value or perform data archiving.

Good Luck.

[Former Member]

Hi Bhaskar,

First select statement (MSEG)  takes a lot of time to excecute, when it comes to second select it is showing runtime error . This problem has been occurred in QTY  system due to large amount of data , In DEV system it working fine . For that i kept range for  posting period(BUDAT) on selection screen only for one month , if it exceeds one month period it shows error . but the problem is, it shows runtime error 'Time limit exceeded' on VBFA statement even for  5 days also.

Regards,

Sandhya.

[yogendra_bhaskar]

Hi Sandhya Raju ,

I understand your problem , this usually occurs , DEV system didn't have enough data as well as load .

There can be one solution for your problem , You can use the Database view WB2_V_MKPF_MSEG2

it contains most of fields from MKPF and MSEG .

Try to fetch data from it , I hope it will work for you

Regards ,

Yogendra Bhaskar

[Former Member]

Hi Bhaskar,

i am getting the problem in VBFA.

Regards,

Sandhya.

[former_member196490]

Usually if the program takes more than certain time to exceed (Depending upon the basis setting) it gives a dump due to time out.

You can execute the program in background to overcome the issue.

[Former Member]

Hi,

You need to create an index on VBFA ( Via SE11 )  with VBELN ( at least ) as the primary key.

I hope this helps,

Regards

Raju Chitale

[venkateswaran_k]

Hi Sandhya,

1. Can you please review for - how many records the first query is returning

2. How many records that MKPF query is returning..

Secondly,

1. You need to create an Index for the MSEG table.  Based on that the query performance will be increased.

Thirdly, always use INTO CORRESPONDING FIELDS OF  in place oc INTO TABLE   in the query statement.

Also can you please paste the exact Dump statement.

regards,

Venkat

[yogendra_bhaskar]

Hi Venkateswaran K ,

I believe the use of INTO CORRESPONDING FIELDS OF will affect the performance , using INTO TABLE is much better ,

regards ,

Yogendra Bhaskar

[Former Member]

Hi Venkateswaran K,

my selection-screen fields are :

company code , plant, posting date, and delivery. first three fields are obligatory.

i am using 3 radio buttons here 1. our frieght details

                                               2. customer frieght details

                                               3. all freight details

These are all running based on posting date.

1.when i excecute the 1st radio button in quality system the mkpf  table returns15618  records only for 10 days based on posting date and it executes the above select statements but it took lot of  time on VBFA statement.

2. when i excecute the 2nd and 3rd radio button in quality system the MSEG  table returns1693  records only  for 10 days based on posting date ,when the control moves to VBFA select statement  iam getting the below error:

Runtime Error:

Short text

    Time limit exceeded.

What happened?

    The z program  has exceeded the maximum permitted runtime

     without

    interruption and has therefore been terminated.

Error analysis

    After a specific time, the program is terminated to make the work area

    available to other users who may be waiting.

    This is to prevent a work area being blocked unnecessarily long by, for

    example:

    - Endless loops (DO, WHILE, ...),

    - Database accesses with a large result set

    - Database accesses without a suitable index (full table scan)

     The maximum runtime of a program is limited by the system profile

    parameter "rdisp/max_wprun_time". The current setting is 600 seconds. If this

     time limit is

    exceeded, the system attempts to cancel any running SQL statement or

    signals the ABAP processor to stop the running program. Then the system

    waits another 60 seconds maximum. If the program is then still active,

Programs with long runtime should generally be started as background

jobs. If this is not possible, you can increase the system profile

parameter "rdisp/max_wprun_time".

Regards,

Sandhya.

[former_member129652]

Dear Sandhya Raju,

  If your MSEG and/or VBFA is huge, there is not much that developers can do.  Since there is no proper index, the database may perform a full scan in the worst case.

  You should talk to your Basis.  Ask him/her to create index, increase the timeout value, or perform data archiving.

  I have encountered a similar situation.  The MSEG contains more than 20 million records.  The basis solved the problem by data archiving.

  If the basis refuses to help, you should run in background.

Good Luck.

[Puneet_Gupta]

Check if the select is using a table index or if it is doing a full scan. If it is doing a full scan check to see if an index can be created on these tables to match your need.

Since these are huge volume tables the choice of creating a new index should be weighed thoroughly before implementing as too many indices can also impact performance.

- Puneet

[venkateswaran_k]

Hi Sandhya,

Thanks for the information provided. 

I would rather go for following approach as the tables you are searching is a heavy one.

Option 1

Create index on MSEG table and VBFA according to your whare clause..  However, this needs to be thoroughly studied before implementing the index as it may impact as creating more indices is also a techincal point.  You need to discuss with DBA for that.. 

Option 2

This is one i implemented in one of my customer location. Similar scenario a MIS report for the CEO. It was fetching data from BSEG and MSEG tables...

What I did is, I created the summary z tables similar to the output of report.  I just used the same report logic to populate the summary table.  Set the program to run in every 3 hours in BACKGOUND.  Then write a small report program just select * from this z table to report...

Your program should work fine in background.  So try to adopt the above method and update if it is working for you.

Please post back with yoru results.

Regards,

Venkat

[Former Member]

i suggest you to create an index to mseg table, this will surely work out