2015 May 20 6:55 AM
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
2015 May 20 8: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.
2015 May 20 7: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
2015 May 20 8:39 AM
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.
2015 May 20 9:42 AM
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.
2015 May 20 12:06 PM
Dear Dengyong,
Is there any way to check the same in SAT T-Code
Thanks
Nishant Bansal
2015 May 20 2:12 PM
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
2015 May 20 3:07 PM
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.
2015 May 20 8: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.
2015 May 20 12:27 PM
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
2015 May 20 12:40 PM
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.