need help with performence

[Former Member]

hi,

i wont to increase the performence becouse this select is take lot of time

how i can do that?

regards

     SELECT vkorg vtweg spart auart augru vbeln audat mandt kvgr1
            kvgr2 kvgr3 kvgr4 kvgr5 kunnr waerk erdat ps_psp_pnr
            netwr knumv vbtyp
            objnr 
                     FROM vbak
                     INTO wa_reptab
*                     WHERE  audat IN r_sel
                     WHERE  vbtyp IN ('C','G','L')
                       AND  auart IN so_type
                       AND  kvgr1 IN so_csgr1
                       AND  kvgr2 IN so_csgr2
                       AND  KVGR4 IN CONTRTYP 
                       AND  KVGR5 IN OFFSDEAL 
                       AND  vkorg IN so_vkorg
                       AND  vtweg IN so_vtweg
                       AND  spart IN so_spart
                AND  kunnr IN sl_kunnr.

       PERFORM status_desc changing wa_stonr. 


       if wa_stonr = '06'.
         continue.
       endif.

       IF  wa_reptab-vbtyp = 'L' AND wa_reptab-auart <> 'YMC0' .
         CONTINUE .
       ENDIF .
*
       SELECT SINGLE bzirk INTO tmp_bzirk
       FROM vbkd
       WHERE vbeln = wa_reptab-vbeln
         AND posnr = 0 .
       IF NOT tmp_bzirk IN so_sldst .
         CONTINUE .
       ELSE .
         wa_reptab-sldst = tmp_bzirk .
       ENDIF .
*
       SELECT SINGLE brsch INTO tmp_brsch
         FROM kna1
         WHERE kunnr = wa_reptab-kunnr .
       IF NOT tmp_brsch IN so_bsar .
         CONTINUE .
       ELSE .
         wa_reptab-bsar = tmp_brsch .
       ENDIF .

       APPEND wa_reptab TO reptab .
     ENDSELECT .

*
     LOOP AT reptab INTO wa_reptab .
       AUTHORITY-CHECK OBJECT 'V_VBAK_VKO'
                ID 'VKORG' FIELD wa_reptab-vkorg
                ID 'VTWEG' FIELD wa_reptab-vtweg
                ID 'SPART' FIELD wa_reptab-spart
                ID 'ACTVT' FIELD '03'.
       IF sy-subrc NE 0.
         DELETE reptab.
       ENDIF.
     ENDLOOP .

&---------------------------------------------------------------------*
*&      Form  status_desc
*&---------------------------------------------------------------------*
form status_desc changing wa_stonr.

  data: "wa_stonr like tj30-stonr ,
        p_stonr like tj30-stonr ,
        wa_stsma like tj30-stsma .


  clear wa_stonr.

  CALL FUNCTION 'STATUS_READ'
            EXPORTING
                 client           = sy-mandt
                 objnr            = wa_reptab-objnr
                 only_active      = 'X'
            IMPORTING

                 stsma            = wa_stsma
                 stonr            = p_stonr     "wa_stonr

            EXCEPTIONS
                 object_not_found = 1
                 OTHERS           = 2.


      wa_stonr = p_stonr.

Regards

[Former Member]

1 Thg u can do is to put an END SELECT imdtly before PERFORM status_desc changing wa_stonr.

Instead use INTO CORRESPONDING FIELDS OF TABLE and populate the table directly at the first go.

All the checks u want to do for dat table shud be done afterwards..i.e. use LOOP >>>ENDLOOP on dat table and den perform the checks on each record..

The problem is dat SELECT interacts directly wid database and since u r doin all ABAP operations also within dat its takin a lot of time.

Hope this helps..reward points if it does

[Former Member]

Hi

if use

SELECT

ENDSELECT

then the performance will decrease all ways

it acts like an LOOP

plz change that

use like this

replacement to SELECT AND ENDSELECT

SELECT dat from dbtable

INTO TABLE itab

where

then it will get data in one shot

please avoid SELECT ENDSELECT

<b>Reward if usefull</b>

[ThomasZloch]

in order to effectively read from VBAK you need to specify either the sales doc numbers VBELN (primary key), creation dates ERDAT or document dates AUDAT (both are secondary indizes). Maybe you can use a narrow range on either of these dates to speed up the select.

Or you use table VAKPA to narrow down by customer number.

Cheers

Thomas

[Former Member]

Hi Tal_s,

Here's some suggested changes, with comments on why. As mentioned it is not good coding to have SELECT ENDSELECT with all that logic inside.

• Create internal table reptab with fields from vbak in the same order as your

• Select statement and add sldst, bsar to the end of the table. That way you don't

• need to use Corresponding Fields Of. Make reptab with header line.

SELECT vkorg vtweg spart auart augru vbeln audat mandt kvgr1

kvgr2 kvgr3 kvgr4 kvgr5 kunnr waerk erdat ps_psp_pnr

netwr knumv vbtyp objnr

FROM vbak

INTO table reptab

WHERE vbtyp IN ('C','G','L')

AND auart IN so_type

AND kvgr1 IN so_csgr1

AND kvgr2 IN so_csgr2

AND KVGR4 IN CONTRTYP

AND KVGR5 IN OFFSDEAL

AND vkorg IN so_vkorg

AND vtweg IN so_vtweg

AND spart IN so_spart

AND kunnr IN sl_kunnr.

• endselect not required. select stops here.

• now loop through your new table.

LOOP at reptab.

• Do the field checks before you make any calls or Selects so

• you don't call or Select unnecessarily

IF wa_reptab-vbtyp = 'L' AND wa_reptab-auart <> 'YMC0' .

Delete reptab .

ENDIF .

*

PERFORM status_desc changing wa_stonr.

if wa_stonr = '06'.

Delete reptab.

endif.

• Again do your auth check before Select-ing more data

• and then you save on having to loop through the whole table after.

AUTHORITY-CHECK OBJECT 'V_VBAK_VKO'

ID 'VKORG' FIELD wa_reptab-vkorg

ID 'VTWEG' FIELD wa_reptab-vtweg

ID 'SPART' FIELD wa_reptab-spart

ID 'ACTVT' FIELD '03'.

if sy-subrc <> 0.

Delete reptab.

endif.

• Select field into the target field so you don't have a separate move statement later.

SELECT SINGLE bzirk INTO reptab-sldst

FROM vbkd

WHERE vbeln = wa_reptab-vbeln

AND posnr = 0 .

IF NOT reptab-sldst IN so_sldst .

delete reptab .

ENDIF .

• Same goes here with selecting field right into target field.

SELECT SINGLE brsch INTO reptab-bsar

FROM kna1

WHERE kunnr = reptab-kunnr .

IF NOT reptab-bsar IN so_bsar .

Delete reptab .

ENDIF .

• Now that you are looping instead of appending you modify if you get this far.

MODIFY reptab .

ENDLOOP.

This should speed things up, as mentioned, Select endselect is a loop and you bring back

records from the database one at a time.

You were bringing back data which you may later discard in in your auth check.

And by moving the status check after checking checking your fields you save by not Calling

the function and then discarding the record after because your vtyp or auart did not meet

your conditions.

All the Continue statements were changed to Delete, since you are now looping through the table

you want to delete the unqualified records from the results table.

This should be better than your original code. You should look also at the select option fields if

performance is still slow. Check the indexes in your main Select. You may need to analyze the data input

to determine which fields would be beneficial in a secondary index.

Hope this helps.

Filler