topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Thu, 15 Dec 2005 18:13:17 GMT

Hi,

I am using the following code and its taking a long time to run.Does anyone know how can i increase the performance?

*Get the values from the table BKPF

SELECT SINGLE * FROM BKPF INTO XBKPF

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

Re: Performance Issue

Former Member — Thu, 15 Dec 2005 18:18:35 GMT

try makin use of the following

if not it_main[] is initial.

select * from <tab_name> for all entries in it_first

where bukrs eq it_first-bukrs....

.........

............

endif.

this should help ur cause....

plz reward points if it helps by clicking on star!!

Regards

Gunjan

Re: Performance Issue

Former Member — Thu, 15 Dec 2005 18:19:47 GMT

Hi,

Are you using the select statement in a loop.

if yes its better first select the data from BKPF for all entries in i_tab with all the key fields.

then later used read statement in your loop.

if its a only once select without loop then i think its ok because your passing all the key fields of BKPF.

Thanks

Rajeev

Re: Performance Issue

Former Member — Thu, 15 Dec 2005 18:25:12 GMT

select the fields which are required to you...

*Get the values from the table BKPF

SELECT SINGLE <b>abc def ghi...</b> FROM BKPF INTO XBKPF

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

regards

vijay

assuming abc,def,ghi are fields of BKPF...

Re: Performance Issue

Former Member — Thu, 15 Dec 2005 18:26:13 GMT

I assume you are looping in the internal table I_TAB1. Instead of looping you may use for all entries after checking the table. This should work faster.

if not I_TAB1[] is initial.

SELECT * FROM BKPF INTO table XBKPF

for all entries in I_tab1

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR

endif.

this might help.

Re: Performance Issue

Former Member — Thu, 15 Dec 2005 18:31:58 GMT

IT seems to be you wrote SELECT with in LOOP. if so, remove it inside loop & use FOR ALL ENTRIES to fetch ITAB related record from BKPF.

& always remember that DONT use SELECT * ,this always leads more processing time. so its adviced to write what fields you want in SELECT statement.

still if you are encountering the same problem, paste your total code (not partly) ,so that we can look into that & advice you better.

thanks

srikanth

Re: Performance Issue

Former Member — Thu, 15 Dec 2005 18:35:35 GMT

BKPF is a very large table in a production system (assuming that you have been running SAP for a while now).

Make sure that your DBAs have "run stats" on the table recently. This is a re-generation of all of the indexes (in your case, the primary key/index).

If this is not done regularly, your table indexes become "stale" and the DB system's SQL optimizer ("Oracle Optimizer", in my company's case) can choose the wrong index to access records or even start to read the DB table sequentially.

Re: Performance Issue

Former Member — Thu, 15 Dec 2005 20:52:24 GMT

The select is ok. You could as the others have suggested, use for all entries, but I suspect your problem is due to either a large volume of data or lies somewhere else. Have you done a performance trace?

Rob

Re: Performance Issue

Former Member — Fri, 16 Dec 2005 04:06:25 GMT

remember one thing

if u use select single u need use where condition specifing all the primary keys of the database table.

eg. dbtabl has f1,f2,f3 as primary key.

u should use

select single * from dbtabl into corres fields of table itab where f1 = ... and f2 = ... and f3 = ... .

<b>since u use all primary key u cant use select single with for all entries search.Even if it gives the result it ll match for the last record of the itab</b>

Also

in ur stmt

SELECT SINGLE * FROM BKPF INTO XBKPF

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

burks, belnr and gjahr should be the primary key fields.

u use

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

it will check for the last record of itab1.

get back for more queries

Re: Performance Issue

Former Member — Fri, 16 Dec 2005 22:47:04 GMT

Actually can i use like this ?

SELECT SINGLE * FROM BKPF INTO XBKPF

for all entries in i_tab1

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

loop at i_tab1.

......

endloop.

or do I have to use read table using binary search?

Re: Performance Issue

Former Member — Sat, 17 Dec 2005 08:49:02 GMT

Hi DN

U can use like this

loop at i_tab1.

SELECT SINGLE * FROM BKPF INTO table XBKPF

for all entries in i_tab1

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

endloop.

I think u should use table key word in ur select stmt.

regards

vijay

Re: Performance Issue

Clemenss — Sat, 17 Dec 2005 11:48:41 GMT

Hi,

don't get confused: Select SINGLE is for a single record, so FOR ALL ENTRIES is not appropriate.

1. get the document keys into I_TAB1 - any performance issue here?

2. idenrify the fields in BKPF you really need.

3. select those fields only

SELECT BUKRS BELNR GJAHR ...

INTO CORRESPONDING FIELDS OF TABLE xbkpf

FROM BKPF FOR ALL ENTRIES IN I_TAB1

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

Hope it helps,

Re: Performance Issue

Former Member — Thu, 22 Dec 2005 20:45:22 GMT

Hi,

Actually I need to delete the Documents from my internal table whose documents has already been reversed.

Below is my code and can anyone let me know if i need to change my code to increase the performance?

LOOP AT I_TAB1.

SELECT BUKRS BELNR GJAHR STBLG FROM BKPF INTO TABLE BKPF_TAB

FOR ALL ENTRIES IN I_TAB1

WHERE BUKRS = I_TAB1-PCODE

AND BELNR = I_TAB1-DOCNUM

AND GJAHR = I_TAB1-FYEAR.

SORT BKPF_TAB BY BELNR.
DELETE ADJACENT DUPLICATES FROM BKPF_TAB.

LOOP AT BKPF_TAB.

READ TABLE BKPF_TAB WITH KEY BELNR = I_TAB1-DOCNUM BINARY SEARCH.

IF SY-SUBRC = 0.

DELETE I_TAB1 WHERE DOCNUM = BKPF_TAB-STBLG.

ENDIF.

ENDLOOP.

Re: Performance Issue

suresh_datti — Thu, 22 Dec 2005 20:50:31 GMT

Hi DN N,

Remove the LOOP AT I_TAB1 & its ENDLOOP statements. When you are using for all entries in i_tab1, you do not need to open the loop. You just have to ensure that it has entries.

ie check not i_tab1 is initial.

regards,

Suresh Datti

Re: Performance Issue

Former Member — Thu, 22 Dec 2005 21:04:31 GMT

Suresh,

I need to use loop endloop for i_tab1 because i have to read the interal table.

when i removed the internal table then the value is just being blank for itab1-stblg.

Re: Performance Issue

suresh_datti — Thu, 22 Dec 2005 21:08:21 GMT

Hi DN N,

You don't need to open the loop before the select..

make the following chnage

LOOP AT I_TAB1.

READ TABLE BKPF_TAB WITH KEY BELNR = I_TAB1-DOCNUM BINARY SEARCH.

IF SY-SUBRC = 0.

DELETE I_TAB1 WHERE DOCNUM = BKPF_TAB-STBLG.

ENDIF.

ENDLOOP.

Regards,

Suresh Datti

Re: Performance Issue

Former Member — Thu, 22 Dec 2005 21:15:30 GMT

DN - I think you're trying to:


data: itab_index like sy-tabix.
SELECT BUKRS BELNR GJAHR STBLG FROM BKPF INTO TABLE BKPF_TAB
FOR ALL ENTRIES IN I_TAB1
WHERE BUKRS = I_TAB1-PCODE
AND BELNR = I_TAB1-DOCNUM
AND GJAHR = I_TAB1-FYEAR.

* SORT BKPF_TAB BY BELNR.
* DELETE ADJACENT DUPLICATES FROM BKPF_TAB.

LOOP AT i_tab_1.

READ TABLE BKPF_TAB WITH KEY BELNR = I_TAB1-DOCNUM BINARY SEARCH.
itab_index - sy-tabix.

IF SY-SUBRC = 0.
DELETE I_TAB1 index itab_index.
ENDIF.

ENDLOOP.

Re: Performance Issue

Former Member — Thu, 22 Dec 2005 21:42:15 GMT

OK - I think I figured out what you are trying to do. First add a field to i_tab1. It's one character and called 'rev'. then:


data: itab_index like sy-tabix.
SELECT BUKRS BELNR GJAHR STBLG FROM BKPF INTO TABLE BKPF_TAB
FOR ALL ENTRIES IN I_TAB1
WHERE BUKRS = I_TAB1-PCODE
AND BELNR = I_TAB1-DOCNUM
AND GJAHR = I_TAB1-FYEAR.
 
SORT BKPF_TAB BY BELNR.
DELETE ADJACENT DUPLICATES FROM BKPF_TAB.
 
LOOP AT i_tab_1.
 
READ TABLE BKPF_TAB WITH KEY BELNR = I_TAB1-DOCNUM BINARY SEARCH.
itab_index = sy-tabix.
 
IF SY-SUBRC = 0.
if bkpf_tab-STBLG = 'X'.
I_TAB1-rev = 'X'.
modify i_tab1 index itab_index.
ENDIF.
 
ENDLOOP.

delete i_tab where rev = 'X'.

I don't have access to R3 so you have to check this.

Rob

Re: Performance Issue

Former Member — Thu, 22 Dec 2005 22:03:04 GMT

Thanks Rob.

I think I must not use binary search. The reason is if you have 8 entries, its starting to read from fourth entry.

Any thoughts?

Re: Performance Issue

suresh_datti — Thu, 22 Dec 2005 22:06:26 GMT

Hi DN N,

To use BINARY SEARCH you have to sort the itab by the key you are reading the table with.. in this case ,

SORT BKPF_TAB by BELNR should precede the READE statement.

Regards,

Suresh Datti