10 Replies Latest reply: Apr 16, 2010 10:55 AM by Gerd Hotz RSS

Need help for performance tuning select statement

Gerd Hotz
Currently Being Moderated

Hello experts,

 

can anybody check the coding how to speed up the performance ?

 

The bottleneck seems to ne in the selection from cdpos and cdhdr,

but this are large tables.

 

 

SELECT * FROM cdhdr WHERE objectclas = 'DEBI'  AND tcode = 'VD02' AND udate = sy-datum

                    OR objectclas = 'DEBI'  AND tcode = 'XD02' AND udate = sy-datum.

 

 

  IF sy-subrc = 0.

 

    changenr = cdhdr-changenr.

    objectid = cdhdr-objectid.

 

    SELECT SINGLE * FROM cdpos INTO wa_cdpos WHERE changenr = changenr AND fname = 'AUFSD'

                      OR changenr = changenr AND fname = 'LIFSD'

                      OR changenr = changenr AND fname = 'FAKSD'.

 

 

    IF wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '01' OR

       wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR

       wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '02' OR

       wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '03' OR

       wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR

       wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'SL'.

      APPEND wa_cdpos TO it_cdpos.

    ENDIF.

 

    IF sy-subrc = 0.

 

      objectid = cdpos-objectid.

      fname = cdpos-fname.

      chngind = cdpos-chngind.

      value_new = cdpos-value_new.

      value_old = cdpos-value_old.

 

      kundennummer = cdpos-objectid.

  •      PERFORM email.

 

    ENDIF.

  ENDIF.

 

ENDSELECT.

 

 

 

 

BR Gerd

  • Re: Need help for performance tuning select statement
    Thomas Zloch
    Currently Being Moderated

    Be careful with the AND and OR conditions without using brackets. I suggest you surround the AND conditions with brackets to be on the safe side and avoid unexpected results caused by "implicit bracketing".

    Thomas

     

    http://help.sap.com/abapdocu_70/en/ABENLOGEXP_BOOLE.htm

  • Re: Need help for performance tuning select statement
    Vinod Kumar
    Currently Being Moderated

    Use the Standard function modules for extracting Change History.

     

    CHANGEDOCUMENT_READ

    CHANGEDOCUMENT_READ_HEADERS

    CHANGEDOCUMENT_READ_POSITIONS

     

    Regards

    Vinod

  • Re: Need help for performance tuning select statement
    Mark Mergaerts
    Currently Being Moderated

    Hi Gerd,

     

    The select on CDPOS looks very fishy to me. Your WHERE clause only has CHANGENR and FNAME, and since CDPOS is a cluster table (i.e. no indexes apart from the primary), this will trigger a full serial scan.

     

    You might try this one, specifying at least the full primary key from the CDHDR record:

     

    SELECT * FROM cdhdr
      WHERE objectclas = 'DEBI' 
        AND udate = sy-datum
        AND ( tcode = 'VD02' or tcode = 'XD02' ).
    
     IF sy-subrc = 0.
        SELECT SINGLE * FROM cdpos INTO wa_cdpos WHERE
                         objectclas = cdhdr-objectclas AND
                         objectid = cdhdr-objectid AND
                         changenr = cdhdr-changenr AND 
                         ( fname = 'AUFSD' OR fname = 'LIFSD' or fname = 'FAKSD' ).

     

    The code you comes from inside a SELECT loop. It might be better to collect the CDHDR records in an internal table and then use FOR ALL ENTRIES in the CDPOS select (or use a join).

     

    Regards,

    Mark

  • Re: Need help for performance tuning select statement
    Siegfried Boes
    Currently Being Moderated

    if there are standard interfaces then try to use them.

     

    You should always try to write a complete WHERE-clause, and of course try to be simple

    IF wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '01' OR
    wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
    wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '02' OR
    wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '03' OR
    wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
    wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'SL'.
    

     

    is the same as

    AND cdpos-chngind = 'U' 
    AND cdpos-value_new  IN ( '01' , '02', '03', 'VK', 'SL' )
    

     

    Note, that lines 2 and 5 are identical !!

    wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR

    wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR

     

     

    Try to convert the SELECT - SELECT SINGLE - ENDSELECT into a Join!

     

    Ich hätte auch eine Literaturempfehlung in Deutsch:

    http://www.dpunkt.de/buecher/3096.html

     

     

    Siegfried

     

  • Re: Need help for performance tuning select statement
    John Cameron
    Currently Being Moderated

    Hello Gerd,

     

    My preference when accessing change history (or any other set of very large tables) is to try to make my select queries using the key fields and then process the output seperately.

     

    CDHDR contains a lot of entries and trying to access the TCODE and UDATE fields within the select query is slow. Similarly, the CDPOS table is also large and slow to search on fields other than the key ones. Instead try the following.

     

    data:

      lt_cdhdr type standard table of cdhdr with header line.

     

    field-symbols:

      <cdhdr> type cdhdr.

     

    * get relevant CDHDR entries

    select *

      from cdhdr

      into lt_cdhdr

      where OBJECTCLAS = 'DEBI'.

     

    * clean up extracted header lines

    delete lt_cdhdr where udate <> sy-datum.

    delete it_cdhdr where tcode <> 'VD02' and tcode <> 'XD02'.

    • this leaves justthe header lines you want.

     

    * loop through headers and get associated details.

    loop at lt_cdhdr assigning <cdhdr>.

      select *

        from cdpos

        where objectclas = <cdhdr>-objectclas

             and objectid = <cdhdr>-objectid

             and changenr = <cdhdr>-changenr

             and fname in ('AUFSD','LIFSD','FAKSD').

        if cdpos-chngind = 'U' and cdpos-value_new in ( '01','02','03','SL','VK' ).

           PERFORM REQUIRED TASKS FOR SENDING EMAIL.

    *      you wanted to select a single CDPOS per change header so now end this loop pass.

           continue.

        endif.

      endselect.

    endloop.

     

    I haven't tested this particular section of code but it should work a lot quicker than what you had. I applied the same approach to a change history report on purchase orders and it ran a lot quicker than any other code I found on the web.

     

    regards,

    John

  • Re: Need help for performance tuning select statement
    Siegfried Boes
    Currently Being Moderated

    > if cdpos-chngind = 'U' and cdpos-value_new in ( '01','02','03','SL','VK' ).

     

    this is no ABAP, I wrote AND and it should be added to the WHERE-condition.

  • Re: Need help for performance tuning select statement
    Thomas Zloch
    Currently Being Moderated

    my summary so far:

     

    1) use full primary key when selecting from CDPOS

    2) make sure the WHERE-conditions are properly structured and work as expected

    3) read Siegfried's book

     

    Please let us know how you are doing.

     

    Thomas

    • Re: Need help for performance tuning select statement
      Gerd Hotz
      Currently Being Moderated

      Hello again,

       

      I've updated my coding:

       

      SELECT * FROM cdhdr WHERE objectclas = 'DEBI'  AND

                                tcode IN ('VD02' , 'XD02'  , 'VD03' , 'XD03') AND

                                udate = sy-datum.

       

        IF sy-subrc = 0.

          changenr = cdhdr-changenr.

          objectid = cdhdr-objectid.

          username = cdhdr-username.

       

          SELECT SINGLE * FROM cdpos INTO wa_cdpos WHERE changenr = changenr AND

                                                         tabname = 'KNA1' AND

                                                         objectclas = 'DEBI' AND

                                                         chngind = 'U' AND

                                                         fname IN ('AUFSD' , 'LIFSD' , 'FAKSD' ) AND

                                                         value_new IN ('01' , 'VK' , '02' , '03' , 'SL' ).

         ....

         ....

       

       

      and now its running fast !

      Maybe with a join - selection it could be better, but its okay now.

       

      Thanks for help !!!

Actions