topic Re: ABAP Performance issue in Application Development and Automation Discussions

ABAP Performance issue

nishantbansal91 — Wed, 20 May 2015 05:55:42 GMT

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

Re: ABAP Performance issue

Former Member — Wed, 20 May 2015 06:16:07 GMT

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

Re: ABAP Performance issue

Saha_Arpan — Wed, 20 May 2015 07:31:04 GMT

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.

Re: ABAP Performance issue

nishantbansal91 — Wed, 20 May 2015 07:39:49 GMT

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.

Re: ABAP Performance issue

Former Member — Wed, 20 May 2015 08:42:57 GMT

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.

Re: ABAP Performance issue

nishantbansal91 — Wed, 20 May 2015 11:06:54 GMT

Dear Dengyong,

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

Thanks

Nishant Bansal

Re: ABAP Performance issue

Former Member — Wed, 20 May 2015 11:27:25 GMT

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

Re: ABAP Performance issue

vikas_mulay2 — Wed, 20 May 2015 11:40:54 GMT

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.

Re: ABAP Performance issue

Former Member — Wed, 20 May 2015 13:12:43 GMT

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

Re: ABAP Performance issue

Former Member — Wed, 20 May 2015 14:07:01 GMT

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.