select statement too slow during run time

[Former Member]

Hi Experts,

I'm a newbie, I had a hard time in select statements. It takes 15 minutes to generate the output.

Could somebody help me to reconstruct my statement. Thank you.

Below is my statement:

  SELECT  bukrs
          belnr
          gjahr
          shkzg
          zuonr
          vbeln
          hkont
          prctr
          pswsl
          pswbt
  FROM bseg
  INTO TABLE it_pdc
  WHERE
  bukrs IN s_bukrs
  AND belnr IN s_belnr
  AND zuonr IN s_zuonr
  AND prctr IN s_prctr
  AND hkont = c_sales_pdc.

  SORT it_pdc BY belnr.

  IF sy-subrc = 0.
    SELECT belnr budat blart
    FROM bkpf
    INTO TABLE it_pdc1
    FOR ALL ENTRIES IN it_pdc
    WHERE belnr = it_pdc-belnr
    AND budat LE p_budat.
  ENDIF.


  LOOP AT it_pdc ASSIGNING <wa_pdc>.

    <wa_pdc>-gtotal = 0.

    IF <wa_pdc>-shkzg = 'H'.
      <wa_pdc>-pswbt  =  - <wa_pdc>-pswbt.
    ENDIF.

    MOVE <wa_pdc>-pswbt TO <wa_pdc>-pdc.
    ADD <wa_pdc>-pswbt TO <wa_pdc>-gtotal.

    MODIFY it_pdc FROM <wa_pdc>.

  ENDLOOP.

Edited by: Matt on Oct 21, 2009 10:37 AM - surrounded abap with to retain formatting

[Former Member]

Hi

The bottleneck csn be in both selection:

SELECT bukrs belnr gjahr shkzg zuonr vbeln hkont prctr pswsl pswbt
  FROM bseg INTO TABLE it_pdc
      WHERE  bukrs IN s_bukrs
           AND belnr IN s_belnr
           AND zuonr IN s_zuonr
           AND prctr IN s_prctr
          AND hkont = c_sales_pdc.

Here it's very important the select-options for company code (S_BUKRS) and document number (S_BELNR) are mandatary.

SELECT belnr budat blart FROM bkpf
    INTO TABLE it_pdc1
          FOR ALL ENTRIES IN it_pdc
                WHERE bukrs = it_pdc-bukrs                 "<-----
                       and belnr = it_pdc-belnr
                       and gjahr = it_pdc-gjahr                "<------
                       AND budat LE p_budat.

Here u should use all key fields so company code, fyscal year too.

Max

[Former Member]

Thank you for the pointers guys.

I joined BSID(for zuonr),BSIS and a customized table(for prctr) instead.

problem solved

[kiran_k8]

Aashta,

Just check this blog by Rob Burbank.

/people/rob.burbank/blog/2007/11/12/quickly-retrieving-fi-document-data-from-bseg

K.Kiran.

[Former Member]

Hi,

Firstly, I think this is going to take a while to execute as BSEG is generally a very large table, typically millions of rows, so just be aware of that when you are running this. Apart from that there are two things that I would do:

1) Check the indexes on BSEG, when pulling data like this it will run a lot quicker if the fields you are filtering on are indexed. There is a fine line between space used and performance as the indexes tend to be very large (or so I have found) but they do really help. I would especially put indexes on those fields where the WHERE clause states IN rather than = as these will require more than 1 check.

2) Rewrite the first select statement so that the most restrictive WHERE clause comes first. i.e:

SELECT ...

FROM BSEG

WHERE hkont = c_sales_pdc AND ...

This should help restrict the number of records straight away. By most restrictive I mean the part of the WHERE clause that would return the fewest rows if that was the only part of the WHERE clause. I'm assuming in the example above that setting the account will be the most restrictive but I may be wrong.

3) Are the selection options mandatory? If not then leaving them blank will mean a selection for all values which means it is effectively pointless to have in the WHERE clause but will still take time to execute. If there are not mandatory then I would do a check before the SELECT. Something like this: DESCRIBE TABLE s_buckrs LINES lv_count. For each of the selection options and only include them in the WHERE clause if they have a count > 1. You could use a character string to hold the WHERE clause value and assign it to a field symbol instead of writing multiple select statements. Example below:

DATA: lv_count type i,
           lv_sql type char128.

FIELD-SYMBOLS: <SQL>.

DESCRIBE TABLE s_buckrs LINES lv_count.
IF lv_count > 0.
   lv_sql = ' bukrs IN s_bukrs'.
ENDIF.

DESCRIBE TABLE s_belnr LINES lv_count.
IF lv_count > 0.
   CONCATENATE lv_sql ' AND belnr IN s_belnr' INTO lv_sql.
ENDIF
*...continue for other tables and hkont. 

ASSIGN (lv_sql) to <SQL>.

SELECT ...
FROM BSEG
WHERE (<SQL>).

This will allow you to minimize the amount of SQL executed on the fly based on what is entered in the selection options. As I said earlier I would put the most restrictive parts of the WHERE first (in the example above I just used the order you are using currently).

Hope these help.

Regards,

Gareth

[matt]

If you surround your abap with then you get better formatting. I've edited the posts for you this time.

matt

[Former Member]

Matt,

thanks for editing.. I will do it next time.

[Former Member]

3) Are the selection options mandatory? If not then leaving them blank will mean a selection for all values which means it is effectively pointless to have in the WHERE clause but will still take time to execute. If there are not mandatory then I would do a check before the SELECT. Something like this: DESCRIBE TABLE s_buckrs LINES lv_count. For each of the selection options and only include them in the WHERE clause if they have a count > 1. You could use a character string to hold the WHERE clause value and assign it to a field symbol instead of writing multiple select statements. Example below:

Completely incorrect and not recommended !!!

Select-options are ABAP constructs and processed by the database interface, empty select options are not send to the database!!!! Of course they are not performance overhead. What you do is automatically done.

Check the SQL trace, the processed SQL statement is not the ABAP code, but the detail, first function!!

Siegfried