topic Performance optimization : query taking 7mints in Application Development and Automation Discussions

Performance optimization : query taking 7mints

Former Member — Tue, 17 Mar 2009 14:22:00 GMT

Hi All ,

Requirement : I need to improve the performance of custom program ( Program taking more than 7 mints +dump).I checked in runtime analysis and below mention query taking more time .

Please let me know the approach to minimize the query time .

TYPES: BEGIN OF lty_dberchz1,
           belnr    TYPE dberchz1-belnr,
           belzeile TYPE dberchz1-belzeile,
           belzart  TYPE dberchz1-belzart,
           buchrel  TYPE dberchz1-buchrel,
           tariftyp TYPE dberchz1-tariftyp,
           tarifnr  TYPE dberchz1-tarifnr,
           v_zahl1  TYPE dberchz1-v_zahl1,
           n_zahl1  TYPE dberchz1-n_zahl1,
           v_zahl3  TYPE dberchz1-v_zahl3,
           n_zahl3  TYPE dberchz1-n_zahl3,
           nettobtr TYPE dberchz3-nettobtr,
           twaers   TYPE dberchz3-twaers,
         END   OF lty_dberchz1.

  DATA: lt_dberchz1 TYPE SORTED TABLE OF lty_dberchz1
        WITH NON-UNIQUE KEY belnr belzeile
        INITIAL SIZE 0 WITH HEADER LINE.


DATA: lt_dberchz1a LIKE TABLE OF lt_dberchz1 WITH HEADER LINE.

*** ***********************************Taking more time*************************************************
*Individual line items
    SELECT dberchz1~belnr dberchz1~belzeile
           belzart buchrel tariftyp tarifnr
           v_zahl1 n_zahl1 v_zahl3 n_zahl3
           nettobtr twaers
      INTO TABLE lt_dberchz1
      FROM dberchz1 JOIN dberchz3
      ON dberchz1~belnr = dberchz3~belnr
      AND dberchz1~belzeile = dberchz3~belzeile
      WHERE buchrel  EQ 'X'.

    DELETE lt_dberchz1 WHERE belzart NOT IN r_belzart.      

    LOOP AT lt_dberchz1.
      READ TABLE lt_dberdlb BINARY SEARCH
      WITH KEY billdoc = lt_dberchz1-belnr.
      IF sy-subrc NE 0.
        DELETE lt_dberchz1.
      ENDIF.
    ENDLOOP.

    lt_dberchz1a[] = lt_dberchz1[].
    DELETE lt_dberchz1 WHERE belzart EQ 'ZUTAX1'
                          OR belzart EQ 'ZUTAX2'
                          OR belzart EQ 'ZUTAX3'.
    DELETE lt_dberchz1a WHERE belzart NE 'ZUTAX1'
                          AND belzart NE 'ZUTAX2'
                          AND belzart NE 'ZUTAX3'.

***************************second query************************************
*  SELECT opbel budat vkont partner sto_opbel
    INTO CORRESPONDING FIELDS OF TABLE lt_erdk
    FROM erdk
    WHERE budat IN r_budat
      AND druckdat   NE '00000000'
      AND stokz      EQ space
      AND intopbel   EQ space
      AND total_amnt GT 40000.
**************************taking more time*********************************
  SORT lt_erdk BY opbel.

  IF lt_erdk[] IS NOT INITIAL.
    SELECT DISTINCT printdoc billdoc vertrag
      INTO CORRESPONDING FIELDS OF TABLE lt_dberdlb
      FROM dberdlb
* begin of code change by vishal
      FOR ALL ENTRIES IN lt_erdk
      WHERE printdoc = lt_erdk-opbel.

    IF lt_dberdlb[] IS NOT INITIAL.
      SELECT belnr belzart ab bis aus01
             v_zahl1 n_zahl1 v_zahl3 n_zahl3
        INTO CORRESPONDING FIELDS OF TABLE lt_dberchz1
        FROM dberchz1
        FOR ALL ENTRIES IN lt_dberdlb
        WHERE belnr   EQ lt_dberdlb-billdoc
          AND belzart IN ('ZUTAX1', 'ZUTAX2', 'ZUTAX3').
    ENDIF. "lt_dberdlb
   endif.

Regards

Rahul

Edited by: Matt on Mar 17, 2009 4:17 PM - Added tags and moved to correct forum

Re: Performance optimization : query taking 7mints

Former Member — Tue, 17 Mar 2009 14:57:11 GMT

your post is unclear.

1. write how many rows do you have in your tables dberchz1 and dberchz3.

2. do you have indexes on a table? probably not, but this is important after you will write number of rows, because for small tables indexes are not necessary - you should have an index on fields buchrel and belzart and instead of this:

DELETE lt_dberchz1 WHERE belzart NOT IN r_belzart.

just move the condition to the select statement after creating an index (removing NOT before).

3. what is this table lt_dberdlb ?

put the coding into the {/code} tags because now your post is incomplete.

Re: Performance optimization : query taking 7mints

SuhaSaha — Tue, 17 Mar 2009 15:13:38 GMT

Hello Rahul,


*Individual line items
SELECT dberchz1_{belnr dberchz1}belzeile
belzart buchrel tariftyp tarifnr
v_zahl1 n_zahl1 v_zahl3 n_zahl3
nettobtr twaers
INTO TABLE lt_dberchz1
FROM dberchz1 JOIN dberchz3
ON dberchz1_{belnr = dberchz3}belnr
AND dberchz1_{belzeile = dberchz3}belzeile
WHERE buchrel EQ 'X'.
DELETE lt_dberchz1 WHERE belzart NOT IN r_belzart. 
LOOP AT lt_dberchz1.
READ TABLE lt_dberdlb BINARY SEARCH
WITH KEY billdoc = lt_dberchz1-belnr.
IF sy-subrc NE 0.
DELETE lt_dberchz1.
ENDIF.
ENDLOOP.

You are trying to capture data in lt_dberchz1 from the JOIN of dberchz1 & dberchz3 & then deleting the records from lt_dberchz1 which are not there in the table lt_dberdlb.

Is my understanding correct. If so you can change your select query to incorporate FOR ALL ENTRIES like this:

*Individual line items
SELECT dberchz1~belnr dberchz1~belzeile
belzart buchrel tariftyp tarifnr
v_zahl1 n_zahl1 v_zahl3 n_zahl3
nettobtr twaers
INTO TABLE lt_dberchz1
FROM dberchz1 JOIN dberchz3
ON dberchz1~belnr = dberchz3~belnr
AND dberchz1~belzeile = dberchz3~belzeile
FOR ALL ENTRIES IN lt_dberdlb
WHERE 
belnr = lt_dberdlb-billdoc
AND buchrel EQ 'X'.

This will lessen the data fetching time & you can avoid the LOOP...ENDLOOP block as well.

BR,

Suhas

Re: Performance optimization : query taking 7mints

Former Member — Tue, 17 Mar 2009 16:51:30 GMT

Run the SQL Trace and tell us where the time is spent,

see here how to use it:


 SELECT dberchz1~belnr dberchz1~belzeile
           belzart buchrel tariftyp tarifnr
           v_zahl1 n_zahl1 v_zahl3 n_zahl3
           nettobtr twaers
      INTO TABLE lt_dberchz1
      FROM dberchz1 JOIN dberchz3
      ON dberchz1~belnr = dberchz3~belnr
      AND dberchz1~belzeile = dberchz3~belzeile
      WHERE buchrel  EQ 'X'.

I assume that is this select, but without data is quite useless

How large are the two tables dberchz1 JOIN dberchz3

What are the key fields?

Is there an index on buchrel

Please use aliases dberchz1 as a

INNER JOIN dberchz3 as b

to which table does buchrel belong?

I don't know you tables, but buchrel EQ 'X' seems not selective, so a lot of data

might be selected.


lt_dberchz1 TYPE SORTED TABLE OF lty_dberchz1
        WITH NON-UNIQUE KEY belnr belzeile
        INITIAL SIZE 0 WITH HEADER LINE.
 
    DELETE lt_dberchz1 WHERE belzart NOT IN r_belzart.      
 
    LOOP AT lt_dberchz1.
      READ TABLE lt_dberdlb BINARY SEARCH
      WITH KEY billdoc = lt_dberchz1-belnr.
      IF sy-subrc NE 0.
        DELETE lt_dberchz1.
      ENDIF.
    ENDLOOP.
 
    lt_dberchz1a[] = lt_dberchz1[].
    DELETE lt_dberchz1 WHERE belzart EQ 'ZUTAX1'
                          OR belzart EQ 'ZUTAX2'
                          OR belzart EQ 'ZUTAX3'.
    DELETE lt_dberchz1a WHERE belzart NE 'ZUTAX1'
                          AND belzart NE 'ZUTAX2'
                          AND belzart NE 'ZUTAX3'.

This is really poor coding, there is sorted table ... nice a compelelty different key is

needed and used .... useless.

Then there is a loop which is anywy a full processing no sort necessary.

Where is the read if you use binary search on TABLE lt_dberdlb ?

Then the tables are again process completely ...


    DELETE lt_dberchz1a WHERE belzart NE 'ZUTAX1'
                          AND belzart NE 'ZUTAX2'
                          AND belzart NE 'ZUTAX3'.

What is that ???? Are you sure that anything can survive this delete???

Siegfried