Performance issue

[Former Member]

hi all.

i am using KEPH table for fetching sale order cost. but it is taking lot of time and report is giving time out error.. Can anyboby tel me any alternative table for it or how to increase the performance of this table or is there any BAPI available for sale order costing?.

pls help me. its very urgent. I wil reward u points.

[Former Member]

Hello Shuba,

Please, show me your select-statement. FYI: BAPIs are not intended for a query.

Thanks,

Heinz

[Former Member]

hi..

my code is,

LOOP AT IT_SALE.

SELECT VBELN POSNR KALNR_KEKO FROM KANZ APPENDING CORRESPONDING FIELDS OF TABLE IT_KANZ

WHERE VBELN = IT_SALE-VBELN

AND POSNR = IT_SALE-POSNR.

ENDLOOP.

LOOP AT IT_KANZ.

SELECT KALNR KST001 KST005 KST007 KST009 KST011 KST013 KST015 KST017 KST021 KST023 FROM KEPH

APPENDING CORRESPONDING FIELDS OF TABLE IT_KEPH

WHERE KALNR = IT_KANZ-KALNR_KEKO

AND KKZST <> 'X'

AND TVERS = '1'

AND BWVAR = 'ZT2'

AND KEART = 'H'.

ENDLOOP.

[Former Member]

This is wrong Code................

[Former Member]

ok fine. could pls give me the correct code?

[Former Member]

hi...

plz note....never ever in ur life use a select stmt inside a loop.....its coz if u do it, u r hittin de DB for that many entries in the loop. so plz avoid such stmts if u want very gud perfomance for ur program...

as 4 ur code....de alternative i can give u is....

SELECT VBELN POSNR KALNR_KEKO

FROM KANZ

into TABLE IT_KANZ

for all entries in IT_SALE

WHERE VBELN = IT_SALE-VBELN

AND POSNR = IT_SALE-POSNR.

in this code u r entirely avoiding the loop stmt.

and in dis code,u will find that u r hittin the DB server onli once.

likewise do the same 4 the other loop stmt also.

reward points if found useful

Regards

Winnie

[Former Member]

Hi Shuba,

BAPI is not good for most of the report as it deals mostly with a single business object.

In this case you need to use SELECT for all entries instead of select inside loop. If you put loop inside select it will make a database connection (which us very costly operation) for each loop pass.

Select for all entries internally retrieve all the data for every records in the driver table w/o individual database connection.

Do an initial check for the driver table (here it_sale). For more better result you can sort it based on the key on which data retrival will be done. Also can eliminate duplicate rows (better to use a temop table as)

Data: l_it_sale like it_sale.

l_it_sale[] = it_sale[].

sort l_it_sale by vbeln posnr.

delete adjacent duplicates from l_it_sale comparing vbeln posnr.

• now use l_it_sale as the driver table

if l_it_sale[] is not initial.

select....

for all entries in l_it_sale

WHERE VBELN = l_IT_SALE-VBELN

AND POSNR = l_IT_SALE-POSNR.

endif.

[Former Member]

1)

Your code :

LOOP AT IT_SALE.

SELECT VBELN POSNR KALNR_KEKO FROM KANZ APPENDING CORRESPONDING FIELDS OF TABLE IT_KANZ

WHERE VBELN = IT_SALE-VBELN

AND POSNR = IT_SALE-POSNR.

ENDLOOP.

Replace with.

if not it_sale is initial.

select VBELN POSNR KALNR_KEKO FROM KANZ

INTO TABLE IT_KANZ

for all entries in it_sale

WHERE VBELN = IT_SALE-VBELN.

endif.

LOOP AT IT_SALE.

read table it_kanz with key vbeln = IT_SALE-VBELN.

endloop.

Your code :

LOOP AT IT_KANZ.

SELECT KALNR KST001 KST005 KST007 KST009 KST011 KST013 KST015 KST017 KST021 KST023 FROM KEPH

APPENDING CORRESPONDING FIELDS OF TABLE IT_KEPH

WHERE KALNR = IT_KANZ-KALNR_KEKO

AND KKZST 'X'

AND TVERS = '1'

AND BWVAR = 'ZT2'

AND KEART = 'H'.

ENDLOOP.

Replace with

if not it_kanz is initial.

SELECT KALNR KST001 KST005 KST007 KST009 KST011 KST013 KST015 KST017 KST021 KST023 FROM KEPH

into TABLE IT_KEPH

for all entries in it_kanz

WHERE KALNR = IT_KANZ-KALNR_KEKO

AND KKZST 'X'

AND TVERS = '1'

AND BWVAR = 'ZT2'

AND KEART = 'H'.

endif.

LOOP AT IT_KANZ.

read table it_keph with key KALNR = IT_KANZ-KALNR_KEKO

AND KKZST 'X'

AND TVERS = '1'

AND BWVAR = 'ZT2'

AND KEART = 'H'.

endloop.

Note : Make sure the internal table which you are using in FOR ALL ENTRIES must not be blank.