Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance Issue

Former Member
0 Kudos

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.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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

23 REPLIES 23

Former Member
0 Kudos

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
0 Kudos

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_member188685
Active Contributor
0 Kudos

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
0 Kudos

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.

0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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

0 Kudos

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?

0 Kudos

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

0 Kudos

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.

0 Kudos

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.

0 Kudos

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

0 Kudos

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.

0 Kudos

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

0 Kudos

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.

0 Kudos

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

0 Kudos

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?

0 Kudos

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

0 Kudos

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

0 Kudos

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

0 Kudos

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

0 Kudos

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

Thanks for the concern.

Appreciate it.