ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SAP ABAP程序性能优化 2-sql语法

SAP ABAP程序性能优化 2-sql语法

原创 Linux操作系统 作者:chouer523 时间:2019-06-01 13:48:04 0 删除 编辑

作者tag: CSDN 转:


Performance Tuning

Contributed by

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

  • Large amount of data
  • Mixing processing and reading of data
  • Fast internal reprocessing of data
  • Fast

The Minus

  • Difficult to program/understand
  • Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

  • Removing duplicates from the driver table
  • Sorting the driver table
  • If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:
                   FOR ALL ENTRIES IN i_tab
                      WHERE mykey >= i_tab-low and             mykey <= i_tab-high.

    The plus:

    • Small amount of data
    • Mixing processing and reading of data
    • Easy to code - and understand

    The minus:

    • Large amount of data
    • when mixed processing isn’t needed
    • Performance killer no. 1

    The plus

    • Very large amount of data
    • Similar to Nested selects - when the accesses are planned by the programmer
    • In some cases the fastest
    • Not so memory critical

    The minus

    • Very difficult to program/understand
    • Mixing processing and reading of data not possible

    SELECT * FROM SBOOK.                    
      CHECK: SBOOK-CARRID = 'LH' AND        
                      SBOOK-CONNID = '0400'.         
    ENDSELECT.                              
    SELECT * FROM SBOOK                      
      WHERE CARRID = 'LH' AND                
            CONNID = '0400'.                 
    ENDSELECT.                               

    C4A = '000'.               
    SELECT * FROM T100         
      WHERE SPRSL = 'D' AND    
            ARBGB = '00'.      
      CHECK: T100-MSGNR > C4A. 
      C4A = T100-MSGNR.        
    ENDSELECT.                 
     
    SELECT MAX( MSGNR ) FROM T100 INTO C4A  
     WHERE SPRSL = 'D' AND                 
           ARBGB = '00'.                   

    SELECT * FROM DD01L                     
      WHERE DOMNAME LIKE 'CHAR%'            
            AND AS4LOCAL = 'A'.             
      SELECT SINGLE * FROM DD01T            
        WHERE   DOMNAME    = DD01L-DOMNAME  
            AND AS4LOCAL   = 'A'            
            AND AS4VERS    = DD01L-AS4VERS  
            AND DDLANGUAGE = SY-LANGU.      
    ENDSELECT.                              
     
    SELECT * FROM DD01V                     
     WHERE DOMNAME LIKE 'CHAR%'            
           AND DDLANGUAGE = SY-LANGU.      
    ENDSELECT.                              

    SELECT * FROM T100             
     WHERE     ARBGB = '00'       
           AND MSGNR = '999'.     
    ENDSELECT.                     
     
    SELECT * FROM T002.              
      SELECT * FROM T100             
        WHERE     SPRSL = T002-SPRAS 
              AND ARBGB = '00'       
              AND MSGNR = '999'.     
      ENDSELECT.                     
    ENDSELECT.                       
     

    REFRESH X006.                  
    SELECT * FROM T006 INTO X006.  
      APPEND X006.                 
    ENDSELECT
     
    SELECT * FROM T006 INTO TABLE X006.
     

    SELECT * FROM DD01L               
      WHERE DOMNAME LIKE 'CHAR%'      
            AND AS4LOCAL = 'A'.       
    ENDSELECT
     
    SELECT DOMNAME FROM DD01L     
     INTO DD01L-DOMNAME          
     WHERE DOMNAME LIKE 'CHAR%'  
           AND AS4LOCAL = 'A'.   
    ENDSELECT

    LOOP AT TAB.           
     CHECK TAB-K = KVAL.  
     " ...                
    ENDLOOP.               
     
    LOOP AT TAB WHERE K = KVAL.      
      " ...                          
    ENDLOOP.                         
     

    REFRESH TAB_DEST.               
    LOOP AT TAB_SRC INTO TAB_DEST.  
      APPEND TAB_DEST.              
    ENDLOOP.                        
     
    TAB_DEST[] = TAB_SRC[].

    LOOP AT TAB.              
      IF TAB-FLAG IS INITIAL. 
        TAB-FLAG = 'X'.       
      ENDIF.                  
      MODIFY TAB.             
    ENDLOOP.                  
     
    TAB-FLAG = 'X'.                   
    MODIFY TAB TRANSPORTING FLAG      
               WHERE FLAG IS INITIAL. 
     

    DO 101 TIMES.                
      DELETE TAB_DEST INDEX 450. 
    ENDDO.                       
     
    DELETE TAB_DEST FROM 450 TO 550.
     

    READ TABLE TAB WITH KEY K = 'X'.
     
    READ TABLE TAB WITH KEY K = 'X' BINARY SEARCH.

    DESCRIBE TABLE: TAB1 LINES L1,       
                    TAB2 LINES L2.       
                                         
    IF L1 <> L2.                         
      TAB_DIFFERENT = 'X'.               
    ELSE.                                
      TAB_DIFFERENT = SPACE.             
      LOOP AT TAB1.                      
        READ TABLE TAB2 INDEX SY-TABIX.  
        IF TAB1 <> TAB2.                 
          TAB_DIFFERENT = 'X'. EXIT.     
        ENDIF.                           
      ENDLOOP.                           
    ENDIF.                               
                                         
    IF TAB_DIFFERENT = SPACE.            
      " ...                              
    ENDIF.                               
     
    IF TAB1[] = TAB2[].   
     " ...               
    ENDIF.                

    LOOP AT TAB.            
     TAB-DATE = SY-DATUM.  
     MODIFY TAB.           
    ENDLOOP.                
     
    WA-DATE = SY-DATUM.                     
    LOOP AT TAB.                            
     MODIFY TAB FROM WA TRANSPORTING DATE. 
    ENDLOOP.                                

    LOOP AT TAB_SRC.               
      APPEND TAB_SRC TO TAB_DEST.  
    ENDLOOP
     
    APPEND LINES OF TAB_SRC TO TAB_DEST.

    LOOP AT TAB_DEST WHERE K = KVAL.  
      DELETE TAB_DEST.                
    ENDLOOP
     
    DELETE TAB_DEST WHERE K = KVAL.
     

    ·                The runtime analysis (SE30)
    ·                SQL Trace (ST05)
    ·                Tips and Tricks tool 
    ·                The performance database

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/332542/viewspace-53579/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2019-03-28

  • 博文量
    74
  • 访问量
    49953