Select on VBAP giving a dump in preproduction system

[Former Member]

Hello Experts,

We are trying to fetch records from VBAP based on the primary key of VBELN. Even with the primary key, it gives dump in pre production system.

select vbeln posnr matnr arktx pstyv abgru zmeng meins netwr waerk
          kwmeng kbmeng vrkme werks vstel netpr kpein kmein mvgr5 cuobj
           z_vessel_code z_container_size z_container_type
           z_voyage_number z_hub_port z_serv_typ_orig z_serv_typ_dest
           z_ship_typ_orig z_ship_typ_dest z_passthrough z_shipmentterm
           z_oti z_equipment_type
      into table i_vbap
      from vbap
       for all entries in i_vbak
     where vbeln = i_vbak-vbeln.

<Added code tags>

If we try to add more parameters in the select query it again gives the dump as primary index or secondary index is not used completely.

Please suggest some way of optimizing this query.

Edited by: Suhas Saha on Jan 17, 2012 2:52 PM

[former_member585060]

Hi,

Just check your internal table I_VBAP structure declaration, compare the selected fields from VBAP and the internal table I_VBAP fields are same or not.

Thanks & Regards

Bala Krishna

[Former Member]

Hi

It seems like either the structure of I_VBAP is not in sync with the fields in your select query ( may be the structure I_VBAP 7 fields in Select query are different ) or may be the ZFields used are not moved properly in the Preproduction Server ...

Please check.

[Former Member]

Jaydeep,

can you please read the exact dump to us. that will help us help you

[Former Member]

The dump is as follows.

if s_etd[]    is not initial.
      delete i_vbak where etd   not in s_etd[].
    endif.

    if s_por[]    is not initial.
      delete i_vbak where por   not in s_por[].
    endif.

    if s_pol[]    is not initial.
      delete i_vbak where pol   not in s_pol[].
    endif.

    if s_podis[]  is not initial.
      delete i_vbak where podch not in s_podis[].
    endif.

    if s_pod[]    is not initial.
      delete i_vbak where pod   not in s_pod[].
    endif.
  endif.

  if i_vbak[] is initial.
    message i038.
    leave list-processing.
  endif.

  if not i_vbak is initial.
>>>    select vbeln posnr matnr arktx pstyv abgru zmeng meins netwr waerk
          kwmeng kbmeng vrkme werks vstel netpr kpein kmein mvgr5 cuobj
           z_vessel_code z_container_size z_container_type
           z_voyage_number z_hub_port z_serv_typ_orig z_serv_typ_dest
           z_ship_typ_orig z_ship_typ_dest z_passthrough z_shipmentterm
           z_oti z_equipment_type
      into table i_vbap
      from vbap
       for all entries in i_vbak
     where vbeln = i_vbak-vbeln.

Edited by: Jaydeepsahu on Jan 17, 2012 10:56 AM

Moderator Message: Please use the tags to format your code snippet.

Edited by: Suhas Saha on Jan 17, 2012 3:44 PM

[yuri_ziryukin]

Well, looks like a TIME_OUT dump due to the fact that either I_VBAK table is loo large.

Run your program as a batch job to avoid TIME_OUT dump.

And your initial select from VBAK is most probably not optimal. Remove all DELETE statements from your ABAP code and put all conditions into the WHERE clause of the SELECT. This may significantly improve your program.

Edited by: Yuri Ziryukin on Jan 17, 2012 11:01 AM

[Former Member]

Also, I checked and found that the data declaration for the structure matches the sequence in which they are fetched for VBAP. Also all the z fields are present in the system and are active.

[Former Member]

Hi Yuri,

VBAK is already checked for being initial. And we are trying to run it in background too. But even in background its mostly unsuccesful and time outs after running for 20,000 + seconds.

[Former Member]

Jayadeep,

what is the DUMP saying? means what is the description of the error.. is it saying time out or some illegal statement or what?

[Former Member]

Hi,

Its a time out.

[Former Member]

Run the program in background.

[Former Member]

It time out even in background.

Is there a way of explicity mentioning a key ?

[Former Member]

after how much time did it time out, usaully backgound job won't time out even if they run for hours. Please check with basis team on increasing the time out parameter for backgound job.

If that does not resolve the issue should try using cursors instead of selects.

[SuhaSaha]

It time out even in background.
> Is there a way of explicity mentioning a key ?

What key do you want? You're selecting from VBAP using VBELN, i don't see this causing any performance bottleneck!

As mentioned by Yuri your SELECT is getting timed out because of too many entries in FAE driver table(IT_VBAK). Did you check how many entries are there in IT_VBAK at runtime?

BR,

Suhas

[Former Member]

Hi ,

VBAK and VBAP are fetching following number of records.

I_VBAK

Table IT_91 - 62484x694

I_VBAP

Table IT_93 - 111881x366

<edited formatting>

Edited by: Suhas Saha on Jan 17, 2012 4:39 PM

[Former Member]

after how much time you are getting the time out in background job?

[yuri_ziryukin]

Hi ,
> 
> VBAK and VBAP are fetching following number of records.
> 
> I_VBAK
>     Table IT_91 - 62484x694
> 
> I_VBAP
>     Table IT_93 - 111881x366
> 
> <edited formatting>
> 
> Edited by: Suhas Saha on Jan 17, 2012 4:39 PM

This is already a good start. Selection with VBELN should result in something like 5 ms per row for VBAP select.

5ms x 111.881 records = around 600 seconds.

Something seems to be wrong with your program. Are you sure that you don't put this selection into some loop or anything like that?

Make a trace with ST12 transaction and see in ABAP trace how many executions did you have to the select and where else the time is spent.

Yuri

[Former Member]

surprising thing is he is getting dump in background job as well..

[former_member585060]

Hi,

How is your internal table declared? is it defined with workarea or not?

Before the select statement,

if not i_vbak is initial. " if the internal table is declared with OCCURS n statement the this statement will check for only workarea not the internal table.

The select statement must be executing in some LOOP and ENDLOOP., Just check the background job which you executed for any message "message i038." displayed in background job? with the use of statement LEAVE TO LIST-PROCESSING in START-OF-SELECTION, the program will execute the remaining code with out going to selection screen.

if i_vbak[] is initial.
    message i038.
    leave list-processing.
  endif.
 
" the below code must have executed with blank i_vbak.

  if not i_vbak is initial.   
>>>    select vbeln posnr matnr arktx pstyv abgru zmeng meins netwr waerk
          kwmeng kbmeng vrkme werks vstel netpr kpein kmein mvgr5 cuobj
           z_vessel_code z_container_size z_container_type
           z_voyage_number z_hub_port z_serv_typ_orig z_serv_typ_dest
           z_ship_typ_orig z_ship_typ_dest z_passthrough z_shipmentterm
           z_oti z_equipment_type
      into table i_vbap
      from vbap
       for all entries in i_vbak
     where vbeln = i_vbak-vbeln.

Thanks & Regards

Bala Krishna

[Former Member]

Hi Jaydeep,

I agree with BalaKrishna, check your I_VBAK table declaration or write separate work area for IT_VBAK.

also you can put Break point on if not IT_VBAK is initial statement and check in foreground run in production if u have the permission.

[Former Member]

May I suggest creating a blank range

Ranges r_posnr for vbap-posnr. 

Select ... where vbeln = it_vbak-vbeln and POSNR in r_posnr.

since you have cleared all the other questions that usually arise....

[yuri_ziryukin]

May I suggest creating a blank range 
> 
since you have cleared all the other questions that usually arise....

And how do you think it will help????????

[Former Member]

To be honest....I can't really explain what is going on in the back ground...

But try a program if you would please; I've .....

When not all the fields of the primary key are available in a select statement when I've passed blank ranges so as to imitate a full key index, the execution times have been reduced by at least a third. Now this is based on solely the time to execute a Select statement. I used GET RUNTIME FIELD for measuring the time.

But please do correct me if I'm wrong.

[Former Member]

Try both ways a number of times and take the lowest time from each.

A better way is to use transaction ST05 and look at the results of the 'explain' function.

Rob

[Former Member]

Hello Jaydeep,

Did you check if there are multiple entries with same VBELN in the i_vbak table.

If any , delete adjacent duplicates from i_vbak (or its copy ) based on VBELN and then use it for selecting data from VBAP for all entries in i_vbak.

Regards,

Saurabh Mathuria

[Former Member]

Could you please check and dump in ST22 and let us know if this happening due to memory over flow or due to timeout?

[raymond_giuseppi]

There should be an error in program logic/structure giving something like an endless loop.

If you cannot resolve it "visually" in the code; try a tool like SE30 or SAT, or just put a break-point at the select from vbap statement to detect multiple execute of this statement.

Else you could also add a UP TO n ROWS to the select statement, transport it in preprod and check once again.

Regards,

Raymond

[parshuram_kokare]

Hi,

If timeout is coming due to large number of records to be fetched then you can try Cursor concept to fetch data from database server to fetch data with some packet size. When you use a cursor to read data, you decouple the process from the SELECT statement.

Refer below link:

[http://help.sap.com/saphelp_470/helpdata/en/fc/eb3b23358411d1829f0000e829fbfe/content.htm]

Regards,

Parshuram.

[yuri_ziryukin]

Hi,
> 
> If timeout is coming due to large number of records to be fetched then you can try Cursor concept to fetch data from database server to fetch data with some packet size. When you use a cursor to read data, you decouple the process from the SELECT statement.
> 
> Refer below link:
> [http://help.sap.com/saphelp_470/helpdata/en/fc/eb3b23358411d1829f0000e829fbfe/content.htm]
> 
> Regards,
> Parshuram.

Alone this cannot help avoiding the timeout dump. In addition something like function SAPGUI_PROGRESS_INDICATOR should be used to force the roll-out and roll-in of the user context between fetches.

[Former Member]

Hello Jaydeep,

Please use CURSOR on I_VBAK and fetch I_VBAP based on that. I have given a link for CURSOR usage. Please let me know if you require any example to implement CURSOR in ABAP Report.

[http://help.sap.com/saphelp_470/helpdata/en/fc/eb3b23358411d1829f0000e829fbfe/content.htm]

This could significantly improve the performance. Please give a try.

[Former Member]

Also, try to use VIEWS involving VBAK and VBAP joins. It could also improve your performance significantly - Views are always faster than normal select statement with FOR ALL ENTRIES.

Please check join conditions before using any views - if that suits your requirement.

[Former Member]

Please use CURSOR on I_VBAK and fetch I_VBAP based on that. I have given a link for CURSOR usage. Please let me know > is could significantly improve the performance. Please give a try.
Also, try to use VIEWS involving VBAK and VBAP joins. It could also improve your performance significantly - Views are always faster than normal select statement with FOR ALL ENTRIES.

I think both statements are either incorrect or not necessarily correct.

Rob

[former_member194613]

Reduce the size of the FOR ALL ENTRIES table to 10.000

append lines of itab1 from 1 to 10000 to itab2

Test either with GET RUNTIME or better with ST05. What is the runtime?

The ranges recommendation is nonsense, this will dump at around 1.000 records.

Overall, try to change into a join

[Former Member]

Hi,

Internal table has some memory space available and if records in in huge numbers then you will get DUMP. By checking VBELN from VBAP should not give dump. Also if your VBAK table has extra/not-required data then delete those entries before selecting from VBAP table.

Hope this may help you.

Regards,

Shailendra