performance issue

[Former Member]

Hi ,

I am getting performance issue while fetching data from vbfa table .

fields fetched form vbfa are Preceding sales and distribution document(vbelv)

Subsequent sales and distribution document(vbeln) and

doccument category of preceding SD document(vbtyp_v) into an internal table and in select query where condition....... i m giving .

VBELN , POSNN ,VBTYP_N, AND VBTYP_V

\[removed by moderator\]

code is

DATA : BEGIN OF it_vbfa OCCURS 0,

vbelv LIKE vbfa-vbelv,

vbeln TYPE vbfa-vbeln,

vbtyp_v TYPE vbtyp_v,

END OF it_vbfa.

DATA : it_vbfax LIKE TABLE OF it_vbfa WITH HEADER LINE.

it_vbrp1x[] = it_vbrp1[].

SORT it_vbrp1x BY vbeln.

DELETE ADJACENT DUPLICATES FROM it_vbrp1x COMPARING vbeln.

SELECT vbelv

vbeln

vbtyp_v FROM vbfa

INTO TABLE it_vbfax

FOR ALL ENTRIES IN it_vbrp1x

WHERE vbeln = it_vbrp1x-vbeln

AND posnn = it_vbrp1x-posnr

AND vbtyp_n = 'M'

AND vbtyp_v = 'C'. " BIL_DOC.

Edited by: Jan Stallkamp on Aug 7, 2008 11:14 AM

[Former Member]

Hi,

Before using for all entries, you need to check whether itab in filled with values or not?

if not it_vbrp1x [ ] is initial.
SELECT vbelv
vbeln
vbtyp_v FROM vbfa
INTO TABLE it_vbfax
FOR ALL ENTRIES IN it_vbrp1x
WHERE vbeln = it_vbrp1x-vbeln
AND posnn = it_vbrp1x-posnr
AND vbtyp_n = 'M'
AND vbtyp_v = 'C'. " BIL_DOC.
endif.

Thanks,

Sriram Ponna.

[Former Member]

hi Ponna ji

i have checked initial condition

thanks no improvement in performance of select query

[Former Member]

Hi,

Delete the adjacent duplicates for the line items as well.

DELETE ADJACENT DUPLICATES FROM it_vbrp1x COMPARING vbeln posnr.

Give the the order of the fields in the same order as in any of the index for the table VBFA.

the order in which the fields are selected should be in the same order as in the table.

This will improve performance a BIT.

If is not a viable option use extract datasets and LDB.

Regards

Naren

[Former Member]

thanks naren..........

but without ldb any other option

[Former Member]

Hi,

why dont you try JOIN ??? It should solve your problem.

SORT it_vbrp1x BY vbeln.                <--- add posnr too
DELETE ADJACENT DUPLICATES FROM it_vbrp1x COMPARING vbeln.   <- add posnr.

Regards,

Madhu

[Former Member]

u r right but it is also increasing performance issue.

[Former Member]

Hi,

can u try using this:

loop at it_vbrp1x.

SELECT SINGLE vbelv vbeln vbtyp_v

FROM vbfa

......

WHERE vbeln = it_vbrp1x-vbeln

AND posnn = it_vbrp1x-posnr

AND vbtyp_n = 'M'

AND vbtyp_v = 'C'.

endloop.

I would again suggest you to try out join with the correct JOIN conditions.

regards,

madhu

[Former Member]

Check the code below:

SELECT vbelv

vbeln

vbtyp_v FROM vbfa

INTO TABLE it_vbfax

FOR ALL ENTRIES IN it_vbrp1x

WHERE vbeln = it_vbrp1x-vbeln

AND posnn = it_vbrp1x-posnr

AND vbtyp_n = 'M'

*AND VBTYP_v = 'C'. "Remove this

* Selecting the Sales Documents from VBFA for corresponding Invoice
    SELECT vbelv
                 posnv
      FROM vbfa
      INTO TABLE gt_itab3
   FOR ALL ENTRIES IN it_vbrp1X
     WHERE vbeln EQ it_vbrp1x-vbeln
       AND posnn EQ it_vbrp1x-posnr
       AND vbtyp_n EQ 'M'.

    IF sy-subrc EQ gc_zero_num.
      SORT gt_itab3 BY vbelv posnv.
    ENDIF.
  ENDIF.

  IF NOT gt_itab3 IS INITIAL.
* Selecting the Purchase Orders from VBFA for corresponding Sales Documents
    SELECT vbeln
           posnn
      FROM vbfa
      INTO TABLE gt_itab4
   FOR ALL ENTRIES IN gt_itab3
     WHERE vbelv EQ gt_itab3-vbelv
       AND posnv EQ gt_itab3-posnv
       AND vbtyp_n EQ 'V'.

ENDIF.

Regards

Kannaiah

[Former Member]

hi ,

This table is related to SD module.

we have following SD tables.

1.vbak--Sales Document: header Data

vbap--Sales Document: Item Data

vbfa--Sales Document Flow

u need to get fields from vbfa table right.

do the following.

1. get vbelv ,vbeln,vbtyp_n from vbap (item) table where vbeln = itab-vbeln.

2.then go to vbfa table give conditions and fopr all entries u ll get all fields for the given conditions.

after getting entries,check with se11 for the same conditions.if no of records and values are same then this method is correct..

and also u get idea of all tables related to

[Former Member]

related to SD module ,then u can easily get idea of logic.

\[removed by moderator\]

regards,

diana.

Edited by: Jan Stallkamp on Aug 7, 2008 11:14 AM

[Former Member]

hi subramani,

u can join vbap and vbfa for the conditions what u have given it is very simple.

u send select statement that is used to fill it_vbap.

regards,

diana.

[Former Member]

Hii

My understanding is that you want Sales Order number corresponding to every billing No in it_vbrp1x

You can take these details from VBRP table,(Being a huge table try to avoid vbfa )

AUBEL Sales ORDER

AUPOS Sales Order Item

IF NOT it_vbrp1x[] IS INTIAL

SELECT aubel AS vbelv

vbeln

FROM vbrp

INTO TABLE it_vbfax

FOR ALL ENTRIES IN it_vbrp1x

WHERE vbeln = it_vbrp1x-vbeln

AND posnn = it_vbrp1x-posnr.

ENDIF.

Regards,

Mukundan.R

[Former Member]

hi use following join statement.it will definitely improve performance.because i have tried already.

Select f_{vbelv f}vbeln f~vbtyp_v

Into table it_vbfa

from Vbap as p

join vbfa as f

on p_{vbeln = f}vbeln and

p_{posnr = f}posnr and

p_{vbtyp_n = f}vbtyp_n

WHERE vbtyp_n = 'M'

AND vbtyp_v = 'C'. " BIL_DOC.

In this where condition if u have any vbeln and posnr from select option u can include, or if those are all in internal table then u can use for all entries in itab.(contains vbeln ,posnr).

test and tell howmuch is improved.

\[removed by moderator\]

diana.

Edited by: Jan Stallkamp on Aug 7, 2008 11:14 AM

[Former Member]

Hii

My understanding is that you want Sales Order number corresponding to every billing No in it_vbrp1x

You can take these details from VBRP table,(Being a huge table try to avoid vbfa )

AUBEL Sales ORDER

AUPOS Sales Order Item

IF NOT it_vbrp1x[] IS INTIAL

SELECT aubel AS vbelv

vbeln

FROM vbrp

INTO TABLE it_vbfax

FOR ALL ENTRIES IN it_vbrp1x

WHERE vbeln = it_vbrp1x-vbeln

AND posnn = it_vbrp1x-posnr.

ENDIF.

Regards,

Mukundan.R

[Former Member]

Your query is OK. I would recommend you to create an INDEX on table VBFA with following fields

vbeln

posnn

vbtyp_n

vbtyp_v.

Your performance will be resolved 100%.

rgds

Rajesh