Performance Issue

[Former Member]

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.

[Former Member]

hi

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

[Former Member]

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

[Former Member]

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

[Former Member]

Hi

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

Hi

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

[Former Member]

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?

[Former Member]

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

[Clemenss]

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,

C.

[Former Member]

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.

ENDLOOP.

[suresh_datti]

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

[Former Member]

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.

[suresh_datti]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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?

[suresh_datti]

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

[Former Member]

Hello ,

To improve the performance .

Delete the duplicate entries in the table I_TAB1 comapring the company code, Docuemnt and year and then use the table to fetch the entries from the table bkpf .

Try using this way .

i_tab2[] = i_tab1[] .

sort i_tab2 by pcode docnum fyear .

delete adjacent duplicates from i_tab2 comparing pcode docnum fyear .

SELECT BUKRS BELNR GJAHR STBLG FROM BKPF INTO TABLE BKPF_TAB

FOR ALL ENTRIES IN I_TAB2

WHERE BUKRS = I_TAB2-PCODE

AND BELNR = I_TAB2-DOCNUM

AND GJAHR = I_TAB2-FYEAR.

SORT BKPF_TAB BY bukrs BELNR gjahr .

• DELETE ADJACENT DUPLICATES FROM BKPF_TAB.

LOOP AT i_tab1.

READ TABLE BKPF_TAB WITH KEY bukrs = i_tab1-pcode

BELNR = I_TAB1-DOCNUM

gjahr = i_tab1-fyear

BINARY SEARCH.

IF SY-SUBRC = 0.

DELETE I_TAB1 WHERE DOCNUM = BKPF_TAB-STBLG.

ENDIF.

ENDLOOP.

Let me know if any questions

[Former Member]

Vijay Kumar,

It did work. Thanks a lot. When I used your logic the performance increased.

Thank You Very Much.

I did award the points to all of you for sharing your opinions.

Appreciate it.!!!!

[Former Member]

DN - I know you've marked this as closed, but I think there are still problems with the logic. The results of:

LOOP AT i_tab1.
...
  DELETE I_TAB1 WHERE DOCNUM = BKPF_TAB-STBLG.
...
ENDLOOP.

may not be what you expect. I'm not sure what the value of sy-tabix will be after either the delete or the endloop. In general, it's best to get the logic correct and then work on performance.

Rob

[Former Member]

You are absolutely right Rob. I did change my logic.

Thanks for the concern.

Appreciate it.