Skip to Content

ABAP Performance issue

Dear Team,

I have one doubt about the performance issue.

I have one select statement where i am using inner join and for all entries. There is no key field in the where condition.


In production system it contains around 100000 entries, so what is the best way to using this. Secondary index is already created.

Shall i break this statement into 2 statement's.

Another option i have used HINT statement  to change the database parameter value  %_HINTS MSSQLNT '&max_blocking_factor 10&' or

                                                                                                                              %_HINTS MSSQLNT '&prefer_join 0&'.

. But the performance is same in both cases.

Please suggest how i can proceed further.

Thanks

Nishant

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    May 20, 2015 at 07:31 AM

    Use join only (if the table is not buffered or pooled) with the secondary index only.

    If you do not have the full key set for that index, use blank range fields for the other fields to fool optimizer into selecting that index only.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 20, 2015 at 06:16 AM

    Hi Nishant,

    Since it`s already near 100000 entries in for all entries itab1,  then you can try directly select all entries by inner join into itab2, do not use for all entries.  After that, filter data by loop itab2 and read itab1. And always remember delete duplicate rows in itab1.


    regards,

    Archer

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Nishant Bansal

      USE FOR ALL ENTRIES only for table EKPO passing MATNR( as matnr is secondry index)

      then for all EBELN fetched, pass to EKKO and fetch data...as ebeln is primary key there.

      then merge it.

  • May 20, 2015 at 11:40 AM

    Hi Nishant,

    I would suggest you to use "For all entries" instead of the "Joins" that you are using...May be split the query if required.

    Now use secondary index since you do not have Primary key...Ensure that the Index is enforced by using HINT keyword. PS - Please ensure Index is also created  at DB by using 'Activate and Adjust Database'.

    HINT stmt syntax-

    For SQL DB-

    %_HINT MSSQLNT 'INDEX(table_name"table_name~sec_index_name")' in your select query.

    For Oracle DB-

    %_HINT ORACLE 'INDEX(table_name"table_name~sec_index_name")' in your select query.


    Now to check if your index is called rightly or not - check below link...

    http://wiki.scn.sap.com/wiki/display/profile/2007/09/19/Indexing+in+SAP+Tables


    I hope this will solve your query.

    Kindest Regards,

    Vikas Mulay.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 20, 2015 at 11:27 AM

    Hi

    try this way

    DATA   S_CURSOR TYPE CURSOR.

    SELECT a~ebeln

                  b~ebelp

                  b~matnr

                  b~werks

                  b~menge

               "   INTO TABLE it_tmpekpo

                  FROM ekko AS a

                  INNER JOIN ekpo AS b

                  ON b~ebeln = a~ebeln

                  FOR ALL ENTRIES IN it_eban_matnr

                  WHERE a~bsart IN lt_so_bsart                 

                    AND a~loekz =  space

                    AND a~aedat IN lt_so_aedat

                    AND a~reswk =  v_reswk

                    AND b~loekz =  space

                    AND b~matnr =  it_eban_matnr-matnr

                    AND b~elikz =  space

                    AND b~pstyp =  '7'

                    AND b~retpo =  space.            

    DO.

        FETCH NEXT CURSOR S_CURSOR APPENDING TABLE IT_TMPEKPO PACKAGE SIZE 500.

        IF SY-SUBRC <> 0.

          EXIT.

        ENDIF.

      ENDDO.

      CLOSE CURSOR S_CURSOR.

    it is increase the performance.

    Regards,

    Chandu

    Add comment
    10|10000 characters needed characters exceeded