topic getting short dump on VBFA table ABAP Query. in Application Development and Automation Discussions

getting short dump on VBFA table ABAP Query.

Former Member — Wed, 04 Jul 2012 05:14:05 GMT

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.

Re: getting short dump on VBFA table ABAP Query.

yogendra_bhaskar — Wed, 04 Jul 2012 05:53:08 GMT

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

Re: getting short dump on VBFA table ABAP Query.

former_member196490 — Wed, 04 Jul 2012 06:10:20 GMT

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.

Re: getting short dump on VBFA table ABAP Query.

former_member129652 — Wed, 04 Jul 2012 06:16:55 GMT

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.

Re: getting short dump on VBFA table ABAP Query.

Former Member — Wed, 04 Jul 2012 06:33:52 GMT

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

Re: getting short dump on VBFA table ABAP Query.

venkateswaran_k — Wed, 04 Jul 2012 06:39:19 GMT

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

Re: getting short dump on VBFA table ABAP Query.

Former Member — Wed, 04 Jul 2012 06:47:15 GMT

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.

Re: getting short dump on VBFA table ABAP Query.

yogendra_bhaskar — Wed, 04 Jul 2012 06:51:38 GMT

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

Re: getting short dump on VBFA table ABAP Query.

yogendra_bhaskar — Wed, 04 Jul 2012 07:19:50 GMT

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

Re: getting short dump on VBFA table ABAP Query.

Puneet_Gupta — Wed, 04 Jul 2012 07:57:46 GMT

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

Re: getting short dump on VBFA table ABAP Query.

Former Member — Wed, 04 Jul 2012 09:04:41 GMT

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.

Re: getting short dump on VBFA table ABAP Query.

Former Member — Wed, 04 Jul 2012 09:19:00 GMT

Hi Bhaskar,

i am getting the problem in VBFA.

Regards,

Sandhya.

Re: getting short dump on VBFA table ABAP Query.

venkateswaran_k — Wed, 04 Jul 2012 09:20:38 GMT

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

Re: getting short dump on VBFA table ABAP Query.

former_member129652 — Wed, 04 Jul 2012 09:30:54 GMT

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.

Re: getting short dump on VBFA table ABAP Query.

Former Member — Wed, 04 Jul 2012 10:26:43 GMT

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