Skip to Content
Currently, I’m working on a Performance project. As you may know, you can get performance down by implementing or using an Index, completing a WHERE statement or by deleting duplicated SELECT’s. Sometimes, you need to JOIN tables, so you need to know which fields must be used. For this, I have created a small performance tool to help me in my everyday work. This tool, perform three tasks…   **  List all indices of a given table. **  List all the tables and views where two given fields are repeated. First, the source code…Then some pictures…   *&————————————————— * *& Report  ZPERFORMANCE_TOOL                          * *&————————————————— * REPORT  ZPERFORMANCE_TOOL.  *=====================================================* * TABLES                                              * *=====================================================* TABLES: DD03L.  *=====================================================* * TYPES                                               * *=====================================================* TYPES: BEGIN OF TY_REPEATED,        FIELDNAME TYPE DD03P-FIELDNAME,        DDTEXT TYPE DD03P-DDTEXT,        END OF TY_REPEATED.  TYPES: BEGIN OF TY_INDEX,        SQLTAB TYPE DD17S-SQLTAB,        INDEXNAME TYPE DD17S-INDEXNAME,        FIELDNAME TYPE DD17S-FIELDNAME,        DDTEXT TYPE DD12T-DDTEXT,        END OF TY_INDEX.  TYPES: BEGIN OF TY_DD03L,        TABNAME TYPE DD03L-TABNAME,        FIELDNAME TYPE DD03L-FIELDNAME,        END OF TY_DD03L.  *=====================================================* * INTERNAL TABLES                                     * *=====================================================* DATA: DD03P_TAB TYPE STANDARD TABLE OF DD03P,       DD03P_TABLE1 TYPE STANDARD TABLE OF DD03P,       DD03P_TABLE2 TYPE STANDARD TABLE OF DD03P,       T_INDEX TYPE STANDARD TABLE OF TY_INDEX,       T_REPEATED TYPE STANDARD TABLE OF TY_REPEATED,       T_DD03L TYPE STANDARD TABLE OF DD03L,       T_DD03L_AUX TYPE STANDARD TABLE OF DD03L.  *=====================================================* * VARIABLES                                           * *=====================================================* DATA: LONG_1 TYPE I,       LONG_2 TYPE I,       W_TITLE TYPE STRING,       W_INDEXNAME TYPE DD17S-INDEXNAME,       FIELD1(30).  *=====================================================* * FIELD-SYMBOLS                                       * *=====================================================* FIELD-SYMBOLS:  LIKE LINE OF T_DD03L.  *=====================================================* * SELECTION-SCREEN                                    * *=====================================================* SELECTION-SCREEN BEGIN OF BLOCK GROUP WITH FRAME TITLE TEXT-001. PARAMETERS: REP_1 RADIOBUTTON GROUP RND USER-COMMAND TEST DEFAULT ‘X’,             REP_2 RADIOBUTTON GROUP RND,             REP_3 RADIOBUTTON GROUP RND. SELECTION-SCREEN END OF BLOCK GROUP.  SELECTION-SCREEN BEGIN OF BLOCK REP_1 WITH FRAME. PARAMETERS:            P_TAB_1 TYPE DD17S-SQLTAB MODIF ID SC1,            P_TAB_2 TYPE DD17S-SQLTAB MODIF ID SC1. SELECTION-SCREEN END OF BLOCK REP_1.  SELECTION-SCREEN BEGIN OF BLOCK REP_2 WITH FRAME. PARAMETERS:            P_TABNAM TYPE DD17S-SQLTAB MODIF ID SC2. SELECTION-SCREEN END OF BLOCK REP_2.  SELECTION-SCREEN BEGIN OF BLOCK REP_3 WITH FRAME. PARAMETERS:                P_FIELD1 TYPE DD03L-FIELDNAME MODIF ID SC3,                P_FIELD2 TYPE DD03L-FIELDNAME MODIF ID SC3. SELECTION-SCREEN END OF BLOCK REP_3.  *=====================================================* * AT SELECTION-SCREEN                                 * *=====================================================* AT SELECTION-SCREEN OUTPUT.   LOOP AT SCREEN.     IF REP_1 EQ ‘X’.       IF SCREEN-GROUP1 = ‘SC2’.         SCREEN-ACTIVE = ‘0’.         MODIFY SCREEN.         CONTINUE.       ENDIF.       IF SCREEN-GROUP1 = ‘SC3’.         SCREEN-ACTIVE = ‘0’.         MODIFY SCREEN.         CONTINUE.       ENDIF.     ELSEIF REP_2 EQ ‘X’.       IF SCREEN-GROUP1 = ‘SC1’.         SCREEN-ACTIVE = ‘0’.         MODIFY SCREEN.         CONTINUE.       ENDIF.       IF SCREEN-GROUP1 = ‘SC3’.         SCREEN-ACTIVE = ‘0’.         MODIFY SCREEN.         CONTINUE.       ENDIF.     ELSEIF REP_3 EQ ‘X’.       IF SCREEN-GROUP1 = ‘SC1’.         SCREEN-ACTIVE = ‘0’.         MODIFY SCREEN.         CONTINUE.       ENDIF.       IF SCREEN-GROUP1 = ‘SC2’.         SCREEN-ACTIVE = ‘0’.         MODIFY SCREEN.         CONTINUE.       ENDIF.     ENDIF.   ENDLOOP.  *=====================================================* * START-OF-SELECTION                                  * *=====================================================* START-OF-SELECTION.    IF REP_1 EQ ‘X’.     CONCATENATE P_TAB_1 P_TAB_2 INTO     W_TITLE SEPARATED BY ‘-‘.      SY-TITLE = W_TITLE.      CALL FUNCTION ‘DDIF_TABL_GET’       EXPORTING         NAME          = P_TAB_1         STATE         = ‘A’         LANGU         = SY-LANGU       TABLES         DD03P_TAB     = DD03P_TABLE1       EXCEPTIONS         ILLEGAL_INPUT = 1         OTHERS        = 2.      DELETE DD03P_TABLE1 WHERE ROLLNAME EQ SPACE.      CALL FUNCTION ‘DDIF_TABL_GET’       EXPORTING         NAME          = P_TAB_2         STATE         = ‘A’         LANGU         = SY-LANGU       TABLES         DD03P_TAB     = DD03P_TABLE2       EXCEPTIONS         ILLEGAL_INPUT = 1         OTHERS        = 2.      DELETE DD03P_TABLE2 WHERE ROLLNAME EQ SPACE.      DESCRIBE TABLE DD03P_TABLE1 LINES LONG_1.     DESCRIBE TABLE DD03P_TABLE2 LINES LONG_2.      IF LONG_1 GT LONG_2.       LOOP AT DD03P_TABLE1 ASSIGNING -DDTEXT.     ENDLOOP.   ELSEIF REP_2 EQ ‘X’.      SELECT DD17S~SQLTAB DD17S~INDEXNAME FIELDNAME DDTEXT     INTO TABLE T_INDEX     FROM DD17S INNER JOIN DD12T     ON DD17S~SQLTAB EQ DD12T~SQLTAB     AND DD17S~INDEXNAME EQ DD12T~INDEXNAME     WHERE DD17S~SQLTAB EQ P_TABNAM       AND DDLANGUAGE EQ SY-LANGU.      CALL FUNCTION ‘DDIF_TABL_GET’       EXPORTING         NAME          = P_TABNAM         STATE         = ‘A’         LANGU         = SY-LANGU       TABLES         DD03P_TAB     = DD03P_TAB       EXCEPTIONS         ILLEGAL_INPUT = 1         OTHERS        = 2.      SY-TITLE = P_TABNAM.      LOOP AT T_INDEX ASSIGNING -DDTEXT.       ENDIF.     ENDLOOP.   ELSE.     SELECT DD03L~TABNAME FIELDNAME     INTO TABLE T_DD03L     FROM ( DD03L INNER JOIN DD02L     ON DD03L~TABNAME EQ DD02L~TABNAME )     WHERE TABCLASS IN (‘TRANSP’,’VIEW’)       AND FIELDNAME EQ P_FIELD1        OR FIELDNAME EQ P_FIELD2.      SORT T_DD03L.      LOOP AT T_DD03L ASSIGNING -TABNAME.     ENDLOOP.   ENDIF.  *=====================================================* * AT LINE-SELECTION                                   * *=====================================================* AT LINE-SELECTION.    IF REP_3 EQ ‘X’.     SET PARAMETER ID ‘DTB’ FIELD SY-LISEL+0(15).      CALL TRANSACTION ‘SE11’ AND SKIP FIRST SCREEN.   ENDIF.     Time for the pictures…  imageimage imageimage imageimage
To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

    1. Alvaro Tejada Galindo Post author
      Hi Naveen:

      Thanks for you comment -:) The tool is actually quite easy to use…As you can see in the blog, it’s divided in three sections…

      On the first, you can put two tables and see a list of the common fields between those tables, by using Fieldname and Rollname.

      On the second, you can put a table and see a list of all the indices of that table.

      Finally, on the third, you can put two fields and see a list with all views and tables where those two fields are repeated.

      Hope this make it clear for you -;)

      Greetings,

      Blag.

      (0) 
  1. Hongyan Shao
    This is a good tool.
    But there seems to be a place which could lead to confusion.

    The first option and the third option sometimes will lead to different result.
    As the first option, it is checking the ROLLNAME which is data element name. But for the third option, it is checking the field name instead.

    Give an example in CRM,
    -first option for table
    CRMD_ORDERADM_H and CRMD_ORDERADM_I
    result shows several fields including field ORDERADM_H_DUMMY, but this field does not exist in table CRMD_ORDERADM_I.
    it is listed, because this field has data element DUMMY which is the same as field ORDERADM_I_DUMMY in the second table.
    -thus if I use ORDERADM_H_DUMMY as one of the field in option 3, table CRMD_ORDERADM_I will not be listed, which seems to be correct.

    I am not so sure from the performance point of view, what is more important, the data element name or the field name?
    But at least the first and third option should be consistent, am I right?

    🙂

    (0) 
    1. Alvaro Tejada Galindo Post author
      Hongyan:

      Thanks for your comment -:)

      First options looks for shared fields between two tables…So looking for field name is not an option…For example Table A can have a field name EmployeeID which got rollname “EmployeeID” and Table B can have a field called “IDEmployee” with the same rollname…Looking with Fieldname, your not going to find the relation between them, because the field names are not the same…But rollnames are…

      Second options looks for fields that are actually repeated in two or more tables…Which for me, it’s not the same…

      Anyway…As always, you’re free to improved the source code and adapt it to your needs…The programs really worked well for my performance project -:)

      Still…Thanks for pointing out that “problem” -;)

      Greetings,

      Blag.

      (0) 

Leave a Reply