How to speed up the code....

[Former Member]

Hi Experts,

I wrote a sales details report in which following piece of code is taking a long time, if the user is giving billing date interval more.

please suggest how to make the code faster.

SELECT A~kunnr

A~bzirk

A~vkorg

A~spart

B~fkdat

INTO TABLE i_knvv

FROM knvv AS A INNER JOIN VBRK AS B ON

A_{kunnr = B}kunrg

WHERE A~vkorg IN s_vkorg

AND A~vkbur IN s_vkbur

AND A~vkgrp IN s_vkgrp

AND A~bzirk IN s_bzirk

AND A~spart in s_spart

AND B~fkdat in s_fkdat. "billing date

DELETE ADJACENT DUPLICATES FROM i_knvv[].

SORT i_knvv[] BY kunnr.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = TEXT-003.

IF NOT i_knvv[] IS INITIAL.

SORT i_knvv[] BY kunnr.

LOOP AT i_knvv[] INTO wa_knvv.

MOVE:wa_knvv-kunnr TO w_kunnr-kunnr.

APPEND w_kunnr TO i_kunnr.

SORT i_kunnr.

COLLECT i_kunnr.

ENDLOOP.

ENDIF.

IF NOT i_kunnr[] IS INITIAL.

*- Billing Document: Header Data

SELECT vbeln fkart waerk knumv fkdat gjahr kurrf bukrs kunrg

FROM vbrk

INTO TABLE i_vbrk

FOR ALL ENTRIES IN i_kunnr

WHERE kunrg EQ i_kunnr-kunnr

AND vkorg IN s_vkorg

AND fkdat BETWEEN s_fkdat-low AND s_fkdat-high

AND spart IN s_spart

AND fkart NE 'JEX'.

*- General Data in Customer Master

SELECT kunnr name1 name3 name4

FROM kna1

INTO TABLE i_kna1[]

FOR ALL ENTRIES IN i_kunnr

WHERE kunnr EQ i_kunnr-kunnr.

DELETE ADJACENT DUPLICATES FROM i_kna1[].

*- Customer Master Tin no Additional Data

SELECT kunnr j_1icstno j_1ilstno INTO TABLE i_j_1imocust[]

FROM j_1imocust

FOR ALL ENTRIES IN i_kunnr

WHERE kunnr EQ i_kunnr-kunnr.

Regards

Lucky

[Former Member]

Read the help on the COLLECT statement. Then rewrite this:

IF NOT i_knvv[] IS INITIAL.
SORT i_knvv[] BY kunnr.
LOOP AT i_knvv[] INTO wa_knvv.
MOVE:wa_knvv-kunnr TO w_kunnr-kunnr.
APPEND w_kunnr TO i_kunnr.
SORT i_kunnr.
COLLECT i_kunnr.
ENDLOOP.
ENDIF.

As:

IF NOT i_knvv[] IS INITIAL.
  SORT i_knvv[] BY kunnr.
  LOOP AT i_knvv[] INTO wa_knvv.
    COLLECT wa_knvv-kunnr TO i_kunnr[].
  ENDLOOP.
*  SORT i_kunnr[].      "not required as i_knvv is sorted.
  DELETE ADJACENT DUPLICATES FROM i_kunnr[].
ENDIF.

MattG.

[Former Member]

Read the help on the COLLECT statement. Then rewrite this:

IF NOT i_knvv[] IS INITIAL.
SORT i_knvv[] BY kunnr.
LOOP AT i_knvv[] INTO wa_knvv.
MOVE:wa_knvv-kunnr TO w_kunnr-kunnr.
APPEND w_kunnr TO i_kunnr.
SORT i_kunnr.
COLLECT i_kunnr.
ENDLOOP.
ENDIF.

As:

IF NOT i_knvv[] IS INITIAL.
  SORT i_knvv[] BY kunnr.
  LOOP AT i_knvv[] INTO wa_knvv.
    COLLECT wa_knvv-kunnr TO i_kunnr[].
  ENDLOOP.
*  SORT i_kunnr[].      "not required as i_knvv is sorted.
  DELETE ADJACENT DUPLICATES FROM i_kunnr[].
ENDIF.

MattG.

[Former Member]

Hi Lucky,

let's start with the first SELECT on two joined tables... this will go much faster if the field KUNRG in VBRK is indexed. If this statement is taking a long time, then try adding a secondary index with fields KUNRG MANDT (in that order!).

Next, these statements:

DELETE ADJACENT DUPLICATES FROM i_knvv[].

SORT i_knvv[] BY kunnr.

should be

SORT i_knvv[] BY kunnr.

DELETE ADJACENT DUPLICATES FROM i_knvv[]

COMPARING kunnr.

You must sort BEFORE deleting duplicates, and use the COMPARING option otherwise only entire row duplicate matches will be deleted. Same concept should be applied to table i_kna1 later in the program, where you forgot to sort before the delete.

Next: the SELECT statement after "Billing Document: Header Data": The index you created earlier might speed this one up. If possible, remove the

fkart NE 'JEX'

clause from the WHERE. Which values of fkart do you really want? If possible, change this clause to read

fkart IN ('X', 'Y', 'Z') or fkart = 'X' "XYZ are whatever your program needs to retrieve

The point is that database do not perform well for negative conditions.

Also: after adding your new index, don't forget to have your DBA update the table statistics for VBRK, and also run ST05 to see if the SELECT is using your new index. It may not use it for the second SELECT against VBRK, but it should use it fior the first one.

[Former Member]

Thanks Experts.

As per your suggestions i made the changes and select query is working faster.

Now the problem is while sending the data to final internal table its taking a very long time.

can you suggest how to make it faster.

becoz for 6 months selection in input giving 22,000+ records.

LOOP AT i_vbrk INTO wa_vbrk.

LOOP AT i_vbrp INTO wa_vbrp WHERE vbeln = wa_vbrk-vbeln .

IF wa_vbrk-fkart = 'ZG2D' OR wa_vbrk-fkart = 'ZG2X' OR wa_vbrk-fkart = 'ZBKD' OR wa_vbrk-fkart = 'ZBKX' OR wa_vbrk-fkart = 'ZCRD' OR

wa_vbrk-fkart = 'ZRED' OR wa_vbrk-fkart = 'ZCRE' OR wa_vbrk-fkart = 'ZCRT' OR wa_vbrk-fkart = 'ZCRX' OR wa_vbrk-fkart = 'ZREC' OR

wa_vbrk-fkart = 'ZREX' OR wa_vbrk-fkart = 'S1'.

MOVE wa_vbrp-netwr TO t_minus.

COMPUTE t_minus = t_minus * -1.

MOVE t_minus TO wa_vbrp-netwr.

CLEAR t_minus.

MOVE wa_vbrp-fkimg TO t_minus.

COMPUTE t_minus = t_minus * -1.

MOVE t_minus TO wa_vbrp-fkimg.

CLEAR t_minus.

MOVE wa_vbrp-mwsbp TO t_minus.

COMPUTE t_minus = t_minus * -1.

MOVE t_minus TO wa_vbrp-mwsbp.

CLEAR t_minus.

ENDIF.

IF wa_vbrk-fkart = 'ZCRE' OR wa_vbrk-fkart = 'ZCRT' OR wa_vbrk-fkart = 'ZDRT' OR wa_vbrk-fkart = 'ZDRX' .

MOVE : '' TO wa_final-fkimg.

ELSE.

MOVE: wa_vbrp-fkimg TO wa_final-fkimg.

ENDIF.

MOVE : wa_vbrp-mwsbp TO wa_final-mwsbp,

wa_vbrp-netwr TO wa_final-netwr.

v_discount = ''.

LOOP AT i_konv INTO wa_konv

WHERE knumv EQ wa_vbrk-knumv AND kposn EQ wa_vbrp-posnr

AND ( kschl EQ 'ZC20' OR kschl EQ 'ZC30' ).

v_discount = v_discount + wa_konv-kwert.

ENDLOOP.

MOVE : wa_vbrk-vbeln TO wa_final-vbeln,

wa_vbrk-fkdat TO wa_final-fkdat.

CONCATENATE wa_vbrk-fkdat4(2) '.' wa_vbrk-fkdat0(4) INTO wa_final-month.

MOVE: wa_vbrk-fkart TO wa_final-fkart,

wa_vbrk-kunrg TO wa_final-kunrg.

READ TABLE i_tvfkt INTO wa_tvfkt WITH KEY fkart = wa_vbrk-fkart BINARY SEARCH.

IF sy-subrc = 0.

MOVE: wa_tvfkt-vtext TO wa_final-vtext.

ENDIF.

**********************************************************************

* other internal tables not given here *

**********************************************************************

APPEND wa_final TO i_final.

CLEAR : wa_knvv,

wa_konv,

wa_tvfkt,

wa_tvapt,

wa_bseg,

wa_bkpf,

wa_j_1imocust,

wa_kna1,

wa_tvgrt,

wa_final,

wa_t171t.

DELETE i_vbrp.

ENDLOOP.

DELETE i_vbrk.

ENDLOOP.

Regards

Lucky

[Sm1tje]

Depends kinda on the way you have defined your internal tables and the size of them.

When doing a loop within a loop and another read (or loop for that matter), you should try and define keys

(unique of non-unique keys) for them and created sorted / hash tables.

[Former Member]

IF NOT i_knvv[] IS INITIAL.
  SORT i_knvv[] BY kunnr.
  LOOP AT i_knvv[] INTO wa_knvv.
    COLLECT wa_knvv-kunnr TO i_kunnr[].
  ENDLOOP.
*  SORT i_kunnr[].      "not required as i_knvv is sorted.
  DELETE ADJACENT DUPLICATES FROM i_kunnr[].
ENDIF.

This is better than the original one, but still to much! The COLLECT and the

DELETE ADJACENT DUPLICATES FROM i_kunnr[] are doing exactly the same!

So one is enough!

[Former Member]

Hi,

what you write seems to me as absolute beginner coding, you can not open three nested loops!!

And I don't understand these: is the most complicated way to

MOVE wa_vbrp-netwr TO t_minus.
COMPUTE t_minus = t_minus * -1.
MOVE t_minus TO wa_vbrp-netwr.
CLEAR t_minus.

minus = -1.

...

wa_vbrp-netwr = wa_vbrp-netwr * minus_1.

Siegfried

[ThomasZloch]

By the way, you don't need a new index for VBRK if you use existing table VRKPA to find sales invoices for partner numbers.

Thomas