Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

ABAP Performance issue

nishantbansal91
Active Contributor
0 Kudos
1,136

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

1 ACCEPTED SOLUTION

Saha_Arpan
Explorer
0 Kudos
362

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.

9 REPLIES 9

Former Member
0 Kudos
362

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

0 Kudos
362

Dear Zhang,

Please find the below query that i have created.

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.                        "

and let me know how we can check the hint statement syntax. Below is the hint statement, i have used after select. Is there any way to validate the hint statement?

%_HINTS MSSQLNT '&max_blocking_factor 10&'.

Thanks

Nishant




Thanks and Regards,

Nishant Bansal.

0 Kudos
362

Actually i do not know what your those hints meaning, but If you specify hints incorrectly, ABAP syntax will ignores them but doesn't return a syntax error or runtime error.

0 Kudos
362

Dear Dengyong,

Is there any way to check the same in SAT T-Code


Thanks

Nishant Bansal

0 Kudos
362

The code looks OK. You are using the secondary index on material, but you should run an SQL trace (transactions ST05) to make sure it is being used. And how many entries are there in table ET_EBAN_MATNR?

If you are SELECTing many records, it's going to take time and there's not much you can do about that.

Rob

0 Kudos
362

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.

Saha_Arpan
Explorer
0 Kudos
363

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.

Former Member
0 Kudos
362

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

vikas_mulay2
Participant
0 Kudos
362

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.