topic Re: Dump in a Select query in Application Development and Automation Discussions

Dump in a Select query

Former Member — Tue, 27 Oct 2009 13:31:28 GMT

Hi All,

I am getting a dump & the dump says " TIME LIMIT EXCEEDED". The code as below:-

LOOP AT it_gen1 INTO wa_gen1.

SELECT vbelv posnv

FROM vbfa

INTO TABLE li_vbfa

WHERE vbeln = wa_gen1-bil_number

AND posnn = wa_gen1-itm_number.

DESCRIBE TABLE li_vbfa LINES gv_count .

READ TABLE li_vbfa INDEX gv_count .

IF sy-subrc = 0 .

MOVE: li_vbfa-vbelv TO gv_vbelv1,

li_vbfa-posnv TO gv_posnv.

ENDIF .

CALL FUNCTION 'SD_DOCUMENT_PARTNER_READ'

EXPORTING

i_parvw = 'ZS'

i_posnr = gv_posnv

i_vbeln = gv_vbelv1

IMPORTING

e_vbpa = li_vbpa

e_vbadr = li_vbadr

EXCEPTIONS

not_found = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

MOVE li_vbpa-kunnr TO wa_gen2-kunnr .

MOVE-CORRESPONDING wa_gen1 TO wa_gen2.

APPEND wa_gen2 TO it_gen2 .

ENDLOOP.

If I use Function Module CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' before ENDLOOP this will overcome the error says one of my friend.

Is this a feasible solution for this error?

Thanks in dvance....

Re: Dump in a Select query

Former Member — Tue, 27 Oct 2009 13:37:16 GMT

Hi,

Select inside loop is a performance issue.

You are hitting the database for every loop line, so it would be a time consuming issue.

Better select the vbfa details using for all entries and read the table when ever required.

Re: Dump in a Select query

ThomasZloch — Tue, 27 Oct 2009 13:44:51 GMT

The progress indicator is just a nasty workaround to reset the runtime counter. Look for information in "ABAP performance" forum on how to make good use of table indexes, in your example for table VBFA. Cure the cause, not the symptoms.

Thomas

Re: Dump in a Select query

Former Member — Tue, 27 Oct 2009 13:45:33 GMT

Hi I have tried that but I am getting so many unwanted records & I am not sure how to restrict them....Also Can you tell if using the FM CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR' will help me or not?

Re: Dump in a Select query

Former Member — Tue, 27 Oct 2009 13:47:56 GMT

Hi Thomas a good answer but there are no Index on this table & I have used almost all the important key fields of this table in the where clause.

Re: Dump in a Select query

ThomasZloch — Tue, 27 Oct 2009 13:55:25 GMT

Check out SAP Note 185530. I'll quote the important part for you:

> Document flow:

> Incorrect: SELECT vbelv FROM vbfa WHERE vbeln ...

> In table VBFA only the preceeding document is used to search for the subsequent document (for example, delivery for order). Searching the other way makes no sense with this table since the preceding documents (for example, order for delivery) are stored directly in the document tables. Thus reading in table VBFA is a one-way street.

> Correct: SELECT vgbel FROM lips WHERE vbeln = ...; or SELECT vgbel FROM vbrp WHERE vbeln = ...; or

> SELECT aubel FROM vbrp WHERE vbeln = ...

Thomas

Re: Dump in a Select query

Former Member — Thu, 29 Oct 2009 13:02:23 GMT

First fetch all the data you require into an internal table using FOR ALL ENTRIES statement. => It will help u remove the SELECT query from the loop.

Then read the data from there and into another temp internal table and thereafter process as you require. This should save you a lot of time. Use [parallel cursor logic|https://wiki.sdn.sap.com/wiki/display/Snippets/ABAPCodeforParallelCursor-Loop+Processing] if required.

Regards,

Srinivas

Re: Dump in a Select query

pramodu — Sat, 31 Oct 2009 09:25:43 GMT

Also, VBFA ...selection we face performance issue and takes much time and dump might occur...

I prefer to use use a FM RV_ORDER_FLOW_INFORMATION instead of read on VBFA..

~Pramod

Re: Dump in a Select query

Former Member — Tue, 03 Nov 2009 14:46:19 GMT

Hi Abhii,

First Use sql trace facilty, to find for which query it is consuming more time. Then you can concentrate more on that.

Check indexes on VBFA table. We are using ecc 6.0, there is one index on VBELN and POSNN.

I beleive, if you have that, you wouldn't be hit with this issue.

For each sales order line item, you are reading billing document. If your are using order related billing, then you can read this table once for a sale order. check this.

use VBFA-VBTYP_N (C - ORDERS) in select query. It could filter some data.

I beleive, you can replace the calling of function module 'SD_DOCUMENT_PARTNER_READ' with a select query on VBPA table as It seems you are interested on partner number only.

Regards,

Madhu.

Re: Dump in a Select query

Former Member — Wed, 04 Nov 2009 13:26:19 GMT

Hi All,

Thanks for all your inputs. This issue is resolved now. I have used ranges in the select query & removed FOR ALL ENTRIES construct. The select query is working very fastly now with correct data.

The code with the changes as below :-

LOOP AT it_gen1 INTO wa_gen1.

CLEAR r_bil_number.

CLEAR r_itm_number.

r_bil_number-option = 'EQ'.

r_bil_number-sign = 'I'.

r_bil_number-low = wa_gen1-bil_number.

COLLECT r_bil_number.

r_itm_number-option = 'EQ' .

r_itm_number-sign = 'I'.

r_itm_number-low = wa_gen1-itm_number.

COLLECT r_itm_number.

ENDLOOP.

SELECT vbelv

posnv

vbeln

posnn

vbtyp_n

vbtyp_v

FROM vbfa

INTO TABLE li_vbfa

WHERE vbeln IN r_bil_number

AND posnn IN r_itm_number

AND vbtyp_n = c_m.

Regards

Abhii.

Re: Dump in a Select query

Rui_Dantas — Wed, 04 Nov 2009 14:24:14 GMT


> Thanks for all your inputs. This issue is resolved now. I have used ranges in the select query & removed FOR ALL ENTRIES construct.

Why do you want inputs if you then ignore them?

The correct way to read VBFA was given by Thomas. If you have the billing document, go directly to VBRP to get the preceding document (and not to VBFA).

By the way, your query will not return (only) the records you want.

 WHERE vbeln IN r_bil_number
AND posnn IN r_itm_number

For example, if you want 2 items (Invoice 100/1 and 200/2), you are doing vbeln in (100,200) and posnn in (1,2) which might mean a total of 4 items (so not only the two you need).

Re: Dump in a Select query

Former Member — Thu, 05 Nov 2009 14:09:36 GMT

Hi Rui,

I am generating invoice output from VF03. Hence the VBELN will always be the same. There is no chance that there will be two VBELN at a time.

Thanks & Regards

Abhii...