topic Re: Select statement Performence issue in Application Development and Automation Discussions

Select statement Performence issue

Former Member — Sat, 03 Dec 2011 19:56:40 GMT

Hi I am having performence issues with below code and select statement, please help me.

TYPES : BEGIN OF T_EXIDV,

EXIDV TYPE EXIDV,

END OF T_EXIDV.

TYPES : BEGIN OF T_VEKP,

VENUM TYPE VENUM,

EXIDV TYPE EXIDV,

END OF T_VEKP.

DATA : IT_EXIDV TYPE TABLE OF T_EXIDV.

DATA : IT_VEKP TYPE TABLE OF T_VEKP.

*Get all HU's in LQUA table

SELECT LENUM FROM LQUA INTO TABLE IT_EXIDV.

IF SY-SUBRC = 0.

SORT IT_EXIDV BY EXIDV.

*Delete adjacent duplicates

DELETE ADJACENT DUPLICATES FROM IT_EXIDV.

DELETE TABLE IT_EXIDV WITH TABLE KEY EXIDV = ''.

*Get all entries from VEKP table

SELECT VENUM EXIDV FROM VEKP INTO TABLE IT_VEKP

FOR ALL ENTRIES IN IT_EXIDV

WHERE EXIDV = IT_EXIDV-EXIDV.

In above select statement (highlighted in bold) is taking very very long time to get input into table IT_VEKP. Well IT_EXIDV internal table has 200,000 records.

Thanks

Re: Select statement Performence issue

romanweise — Sat, 03 Dec 2011 22:43:52 GMT

The statement does not look very good. FOR ALL ENTRIES IN is automatically packaging the SQL statement to restrict its size. On most systems the default package size is 5 so the FOR ALL ENTRIES IN statement is sending a DB request for every 5 table entries in IT_EXIDV resulting in 40.000 selects. This should explain the horrible performance. In addition the field you use is not the primary key. It might be that the DB fails to grab the existing secondary index on this field which is VEKP~C.

So check the explain for this statement. It should show if the system is using the correct index. If not updating database statistics or hard definition of the index to use might bring some improvement. Furthermore tailoring the package size might bring some effect. Overall I fear that in the end the performance improvement might not be significant.

So an alternative path to get the records with less select and perhaps transfer of processing from the database to the abap runtime might be needed. How many records does VEKP table has? How many record does the select get from the database? Can you get an set of database records which includes the records you actually need with other attributes?

Rgds.

Roman

Re: Select statement Performence issue

deepak_dhamat — Sun, 04 Dec 2011 04:32:44 GMT

Hi ,

TYPES : BEGIN OF T_EXIDV,
  LGNUM  type lqua-LGNUM,
LQNUM    type  lqua-lqnum ,
LENUM    type lqua-lenum ,
MATNR    type  lqua-matnr ,
*EXIDV TYPE EXIDV,
END OF T_EXIDV.


TYPES : BEGIN OF T_VEKP,
VENUM TYPE VENUM,
EXIDV TYPE EXIDV,
END OF T_VEKP.

DATA : IT_EXIDV TYPE TABLE OF T_EXIDV.
DATA : IT_VEKP TYPE TABLE OF T_VEKP.

*Get all HU's in LQUA table
SELECT lgnum lqnum LENUM matnr
  FROM LQUA
INTO CORRESPONDING FIELDS OF TABLE  IT_EXIDV.

if it_exidv[] is  not   INITIAL .

*Get all entries from VEKP table
SELECT VENUM EXIDV
  FROM VEKP
INTO CORRESPONDING FIELDS OF TABLE IT_VEKP
where venum = vekp~venum    .
endif.

" You can use in above manner .

but i did/nt understant where you find EXIDV IN LQUA table ...

According to your logic => there is no field exidv

*Get all HU's in LQUA table
SELECT LENUM FROM LQUA INTO TABLE IT_EXIDV.
IF SY-SUBRC = 0.
SORT IT_EXIDV BY EXIDV.
*Delete adjacent duplicates
DELETE ADJACENT DUPLICATES FROM IT_EXIDV.
DELETE TABLE IT_EXIDV WITH TABLE KEY EXIDV = ''.
*Get all entries from VEKP table
SELECT VENUM EXIDV FROM VEKP INTO TABLE IT_VEKP
FOR ALL ENTRIES IN IT_EXIDV
WHERE EXIDV = IT_EXIDV-EXIDV.

regards

Deepak.

Re: Select statement Performence issue

Former Member — Mon, 05 Dec 2011 19:27:05 GMT

Hi i have resloved it by myself by creating binary search and secondary indexes on tables.