Help!!performance tuning

[Former Member]

SELECT vbrk~vbeln

vbrp~posnr

vbrp~fkimg

vbrp~vrkme

vbrp~netwr

vbrp~aubel

vbrp~aupos

vbrp~matnr

vbrp~charg

vbrk~fkart

vbrk~fkdat

vbrk~erdat

vbrk~kunag

FROM vbrk

INNER JOIN vbrp

ON vbrp_{vbeln = vbrk}vbeln

INTO TABLE gt_vbrk

WHERE vbrk~vbeln IN s_vbeln

AND vbrk~fkdat IN s_fkdat

AND vbrk~fkart IN s_fkart

AND vbrp~matnr IN s_matnr

AND vbrp~charg IN s_charg

AND vbrk~kunag IN s_kunnr.

IF sy-subrc = 0.

SORT gt_vbrk BY vbeln posnr ASCENDING.

DELETE ADJACENT DUPLICATES FROM gt_vbrk COMPARING vbeln posnr.

*sold-to-party

SELECT kunnr

name1

FROM kna1

INTO TABLE gt_kna1_soldn

FOR ALL ENTRIES IN gt_vbrk

WHERE kunnr = gt_vbrk-kunag.

IF sy-subrc = 0.

SORT gt_kna1_soldn BY kunnr ASCENDING.

ENDIF.

*ship-to-party

SELECT vbeln

posnr

parvw

kunnr

FROM vbpa

INTO TABLE gt_vbpa

FOR ALL ENTRIES IN gt_vbrk

WHERE vbeln = gt_vbrk-aubel

AND parvw = 'WE'

AND kunnr IN s_kunnr1.

IF sy-subrc = 0.

SORT gt_vbpa BY vbeln ASCENDING.

DELETE ADJACENT DUPLICATES FROM gt_vbpa COMPARING vbeln posnr.

SELECT kunnr

name1

FROM kna1

INTO TABLE gt_kna1_shipn

FOR ALL ENTRIES IN gt_vbpa

WHERE kunnr = gt_vbpa-kunnr.

IF sy-subrc = 0.

SORT gt_kna1_shipn BY kunnr ASCENDING.

ENDIF.

ENDIF.

*tomg

SELECT vbeln

parvw

kunnr

pernr

FROM vbpa

INTO TABLE gt_vbpa_tomg

FOR ALL ENTRIES IN gt_vbrk

WHERE vbeln = gt_vbrk-aubel

AND parvw = 'A1'.

IF sy-subrc = 0.

SORT gt_vbpa_tomg BY vbeln ASCENDING.

• description

SELECT pernr

sname

FROM pa0001

INTO TABLE gt_pa0001_tomg

FOR ALL ENTRIES IN gt_vbpa_tomg

WHERE pernr = gt_vbpa_tomg-pernr.

ENDIF.

*sales rep

SELECT vbeln

parvw

kunnr

pernr

FROM vbpa

INTO TABLE gt_vbpa_sr

FOR ALL ENTRIES IN gt_vbrk

WHERE vbeln = gt_vbrk-aubel

AND parvw = 'AF'.

IF sy-subrc = 0.

SORT gt_vbpa_sr BY vbeln ASCENDING.

*description

SELECT pernr

sname

FROM pa0001

INTO TABLE gt_pa0001_sr

FOR ALL ENTRIES IN gt_vbpa_sr

WHERE pernr = gt_vbpa_sr-pernr.

ENDIF.

*if s_vbeln2 is initial.

IF s_vbeln2[] IS INITIAL.

SELECT vbak~vbeln

vbap~posnr

vbak~audat

vbak~auart

vbak~vkgrp

vbak~bstnk

vbak~kunnr

FROM vbak

INNER JOIN vbap

ON vbap_{vbeln = vbak}vbeln

INTO TABLE gt_vbak

FOR ALL ENTRIES IN gt_vbrk

WHERE vbak~vbeln = gt_vbrk-aubel

AND vbap~posnr = gt_vbrk-aupos

AND vbak~audat IN s_audat

AND vbak~auart IN s_auart.

IF sy-subrc = 0.

SORT gt_vbak BY vbeln posnr ASCENDING.

DELETE ADJACENT DUPLICATES FROM gt_vbak COMPARING vbeln posnr.

*delivery

SELECT lips~vgbel

lips~vgpos

likp~vbeln

lips~posnr

likp~erdat

likp~kunnr

likp~podat

FROM likp

INNER JOIN lips

ON lips_{vbeln = likp}vbeln

INTO TABLE gt_billing

FOR ALL ENTRIES IN gt_vbak

WHERE lips~vgbel = gt_vbak-vbeln

AND lips~vgpos = gt_vbak-posnr.

IF sy-subrc = 0.

SORT gt_billing BY vgbel vgpos ASCENDING.

DELETE ADJACENT DUPLICATES FROM gt_billing COMPARING vgbel vgbel.

SELECT vttp~vbeln

vttk~tknum

vttk~dtabf

FROM vttk

INNER JOIN vttp

ON vttp_{tknum = vttk}tknum

INTO TABLE gt_shipment

FOR ALL ENTRIES IN gt_billing

WHERE vttp~vbeln = gt_billing-vbeln.

IF sy-subrc = 0.

SORT gt_shipment BY vbeln ASCENDING.

ENDIF.

ENDIF. " likp endif.

ENDIF. "gt_vbak endif.

• s_vbeln2 is not initial.

ELSE.

SELECT vbak~vbeln

vbap~posnr

vbak~audat

vbak~vkgrp

vbak~bstnk

vbak~kunnr

FROM vbak

INNER JOIN vbap

ON vbap_{vbeln = vbak}vbeln

INTO TABLE gt_vbak

FOR ALL ENTRIES IN gt_vbrk

WHERE vbak~vbeln IN s_vbeln2

AND vbap~posnr = gt_vbrk-aupos

AND vbak~audat IN s_audat

AND vbak~auart IN s_auart.

IF sy-subrc = 0.

SORT gt_vbak BY vbeln posnr ASCENDING.

DELETE ADJACENT DUPLICATES FROM gt_vbak COMPARING vbeln posnr.

SELECT lips~vgbel

lips~vgpos

likp~vbeln

lips~posnr

likp~erdat

likp~kunnr

likp~podat

FROM likp

INNER JOIN lips

ON lips_{vbeln = likp}vbeln

INTO TABLE gt_billing

FOR ALL ENTRIES IN gt_vbak

WHERE lips~vgbel = gt_vbak-vbeln

AND lips~vgpos = gt_vbak-posnr.

IF sy-subrc = 0.

SORT gt_billing BY vgbel vgpos ASCENDING.

DELETE ADJACENT DUPLICATES FROM gt_billing COMPARING vgbel vgbel.

SELECT vttp~vbeln

vttk~tknum

vttk~dtabf

FROM vttk

INNER JOIN vttp

ON vttp_{tknum = vttk}tknum

INTO TABLE gt_shipment

FOR ALL ENTRIES IN gt_billing

WHERE vttp~vbeln = gt_billing-vbeln.

IF sy-subrc = 0.

SORT gt_shipment BY vbeln ASCENDING.

ENDIF.

ENDIF. " likp endif.

ENDIF. "gt_vbak endif.

ENDIF.

ENDIF.

*Header fieldnames

CONCATENATE text-005 text-006 text-007

text-008 text-009 text-010

text-011 text-012 text-013

text-014 text-015 text-016

text-017 text-018 text-019

text-020 text-021 text-022

text-023 text-024 text-025

text-026 text-027

INTO gv_header

SEPARATED BY ','.

APPEND gv_header TO gt_output.

IF gt_vbrk[] IS INITIAL.

WRITE text-004.

ENDIF.

LOOP AT gt_vbrk ASSIGNING <fs_vbrk>.

lv_netwr_vbrp = <fs_vbrk>-netwr .

lv_fkimg_vbrp = <fs_vbrk>-fkimg .

lv_vbeln_vbrk = <fs_vbrk>-vbeln .

lv_vrkme_vbrp = <fs_vbrk>-vrkme .

lv_matnr_vbrp = <fs_vbrk>-matnr .

lv_charg_vbrp = <fs_vbrk>-charg .

lv_fkart_vbrk = <fs_vbrk>-fkart .

lv_fkdat_vbrk = <fs_vbrk>-fkdat .

lv_erdat_vbrk = <fs_vbrk>-erdat .

lv_kunag_vbrk = <fs_vbrk>-kunag .

IF lv_netwr_vbrp IS INITIAL.

lv_netwr_output = 0.

ENDIF.

IF lv_fkimg_vbrp IS INITIAL.

lv_fkimg_output = 0.

ENDIF.

SPLIT lv_netwr_vbrp AT '.' INTO lv_string2 lv_string3.

CONCATENATE lv_string2 '.' lv_string3 INTO lv_netwr_output.

SPLIT lv_fkimg_vbrp AT '.' INTO lv_string lv_string1.

CONCATENATE lv_string '.' lv_string1 INTO lv_fkimg_output.

READ TABLE gt_kna1_soldn ASSIGNING <fs_kna1_soldn> WITH KEY kunnr = <fs_vbrk>-kunag

BINARY SEARCH.

IF sy-subrc = 0.

lv_name1 = <fs_kna1_soldn>-name1.

REPLACE ALL OCCURRENCES OF ',' IN lv_name1 WITH space.

ENDIF.

READ TABLE gt_vbpa ASSIGNING <fs_vbpa> WITH KEY vbeln = <fs_vbrk>-aubel

parvw = 'WE'

BINARY SEARCH.

IF sy-subrc = 0.

lv_kunnr_vbpa = <fs_vbpa>-kunnr.

READ TABLE gt_kna1_shipn ASSIGNING <fs_kna1_shipn> WITH KEY kunnr = <fs_vbpa>-kunnr

BINARY SEARCH.

IF sy-subrc = 0.

lv_name2 = <fs_kna1_shipn>-name2.

REPLACE ALL OCCURRENCES OF ',' IN lv_name2 WITH space.

ENDIF.

ENDIF.

READ TABLE gt_vbpa_tomg ASSIGNING <fs_vbpa_tomg> WITH KEY vbeln = <fs_vbrk>-aubel

parvw = 'A1'

BINARY SEARCH.

IF sy-subrc = 0.

READ TABLE gt_pa0001_tomg ASSIGNING <fs_pa0001_tomg> WITH KEY pernr = <fs_vbpa_tomg>-pernr.

IF sy-subrc = 0.

lv_desc_tomg = <fs_pa0001_tomg>-sname.

ENDIF.

ENDIF.

READ TABLE gt_vbpa_sr ASSIGNING <fs_vbpa_sr> WITH KEY vbeln = <fs_vbrk>-aubel

parvw = 'AF'

BINARY SEARCH.

IF sy-subrc = 0.

READ TABLE gt_pa0001_sr ASSIGNING <fs_pa0001_sr> WITH KEY pernr = <fs_vbpa_sr>-pernr.

IF sy-subrc = 0.

lv_desc_sr = <fs_pa0001_sr>-sname.

ENDIF.

ENDIF.

LOOP AT gt_vbak ASSIGNING <fs_vbak> WHERE vbeln = <fs_vbrk>-aubel

AND posnr = <fs_vbrk>-aupos.

IF <fs_vbak>-bstnk IS NOT INITIAL.

REPLACE ALL OCCURRENCES OF ',' IN <fs_vbak>-bstnk WITH space.

ENDIF.

lv_vbeln_vbak = <fs_vbak>-vbeln.

lv_audat = <fs_vbak>-audat.

lv_vkgrp = <fs_vbak>-vkgrp.

lv_bstnk = <fs_vbak>-bstnk.

LOOP AT gt_billing ASSIGNING <fs_billing> WHERE vgbel = <fs_vbak>-vbeln

AND vgpos = <fs_vbak>-posnr.

lv_vbeln_likp = <fs_billing>-vbeln.

lv_erdat_likp = <fs_billing>-erdat.

lv_podat_likp = <fs_billing>-podat.

READ TABLE gt_shipment ASSIGNING <fs_shipment> WITH KEY vbeln = <fs_billing>-vbeln

BINARY SEARCH.

IF sy-subrc = 0.

lv_dtabf = <fs_shipment>-dtabf.

ENDIF.

ENDLOOP.

ENDLOOP.

IF sy-subrc NE 0 AND s_vbeln2[] IS NOT INITIAL.

CLEAR: gv_string ,

lv_vbeln_vbrk, lv_fkart_vbrk ,

lv_erdat_vbrk, lv_fkdat_vbrk,

lv_vbeln_vbak, lv_audat,

lv_bstnk, lv_vkgrp,

lv_desc_tomg , lv_desc_sr ,

lv_kunag_vbrk,

lv_name1,

lv_kunnr_vbpa, lv_name2,

lv_matnr_vbrp, lv_charg_vbrp,

lv_fkimg_output, lv_vrkme_vbrp,

lv_netwr_output, lv_vbeln_likp ,

lv_erdat_likp, lv_podat_likp ,

lv_dtabf.

CONTINUE.

ENDIF.

CONCATENATE lv_vbeln_vbrk lv_fkart_vbrk

lv_erdat_vbrk lv_fkdat_vbrk

lv_vbeln_vbak lv_audat

lv_bstnk lv_vkgrp

lv_desc_tomg lv_desc_sr

lv_kunag_vbrk

lv_name1

lv_kunnr_vbpa lv_name2

lv_matnr_vbrp lv_charg_vbrp

lv_fkimg_output lv_vrkme_vbrp

lv_netwr_output lv_vbeln_likp

lv_erdat_likp lv_podat_likp

lv_dtabf

INTO gv_string

SEPARATED BY ','.

APPEND gv_string TO gt_output.

CLEAR: gv_string ,

lv_vbeln_vbrk, lv_fkart_vbrk ,

lv_erdat_vbrk,lv_fkdat_vbrk,

lv_vbeln_vbak, lv_audat,

lv_bstnk, lv_vkgrp,

lv_desc_tomg , lv_desc_sr ,

lv_kunag_vbrk,

lv_name1,

lv_kunnr_vbpa, lv_name2,

lv_matnr_vbrp, lv_charg_vbrp,

lv_fkimg_output, lv_vrkme_vbrp,

lv_netwr_output, lv_vbeln_likp ,

lv_erdat_likp, lv_podat_likp ,

lv_dtabf.

ENDLOOP.

**guys thats my whole code for retrieving my data.my program is king of slow.

Help me experts to make it more faster.

Thanks!!

[Former Member]

Hi

Do not use Joins. This will cause performance problems. Use 'For All Entries' insead of joins.

Thanks

Vijay

PLZ reward points if helpful

[Former Member]

hI

IN YOUR PROGRAM YOUR USEING MANY SELECT QUERYS

AND IN THAT MOST OF THE SELECT QUERYS ARE ON JOINS

IF YOU USE JOINS WHAT HAPPENS YOU KNOW

THAT DATA BASE CONNECTIVITY IS THERE UPTO YOUR TOTAL PROGRAM EXCUTION SO IT MAKES MORE PERFORMANCE ISSUE

<b>BETTER TO USE FOR ALL ENTRIES OPTION INSTEAD OF JOINS</b>

IN THAT THERE WON'T BE ANY DATA BASE CONNECTIVITY SO IT WILL EXECUTE BIT FASTER THAN USEING JOINS

<b>SELECT DATA FROM DBTABLE INTO ITAB WHERE CONDITION

IF ITAB IS NOT INITIAL

SELECT DATA FROM DBTABLE2 INTO ITAB1 FOR ALL ENTRIES IN ITAB WHERE CONDITION

ENDIF.</b>

<b>rEWARD IF USEFULL</b>

[Former Member]

Hi,

Its the Joins which u have extensively used in ur program affecting performance ..

Instead use diffrerent logic with the help of 'Foe all entries'. Rest usage of 'Delete adjusant duplicates' and 'Sort' is good practise.

Regards,

Dhananjay.

[Former Member]

how about my looping? is there also a problem with that?