topic Re: Performance issue on a select statement in Application Development and Automation Discussions

Performance issue on a select statement

matteo_montalto — Wed, 18 Feb 2009 12:44:27 GMT

Hi all @ SAPforums and thanks for your attention,

the task is quite simple: given a Purchase Requisition number and position (banfn, bnfpo) I have to check if a contract with the same PR as source exists in the EKPO table.

In order to check for it, I simply typed the following select:

SELECT SINGLE * FROM EKPO INTO wa_checkekpo

WHERE bstyp EQ 'K'

AND banfn EQ l_eban-banfn

AND bnfpo EQ l_eban-bnfpo.

This kind of query is quite consuming (more than three seconds in my process) due to the fact that banfn and bnfpo don't belong to a key for the table.

Any idea/workaround that can lead to better performance? Please note I'm not interested in retrieving the contract number (KONNR), it's sufficient to know it exists.

Re: Performance issue on a select statement

Former Member — Wed, 18 Feb 2009 12:55:30 GMT

Try this :

Do not use select * if you just want to check existence of the record. Use a single variable to store teh count.


SELECT count(*) FROM EKPO INTO lv_count
WHERE 
matnr = l_eban-matnr
and  bstyp EQ 'K'
AND banfn EQ l_eban-banfn
AND bnfpo EQ l_eban-bnfpo.
if lv_count > 0.
endif.

Also use the material number if possible, so it uses the correct index, since you have it in the eban table.

regards,

Advait

Edited by: Advait Gode on Feb 18, 2009 1:55 PM

Re: Performance issue on a select statement

HermannGahm — Wed, 18 Feb 2009 15:12:02 GMT

Hi,

> Do not use select * if you just want to check existence of the record.

so far so good.

> Use a single variable to store teh count.

But why should we count the records if we just want to know if a key exists in

the db or not? change the second half of the recommendation to:

Use a single field of the used index for the select list to check (sy-subrc or result

of that field) if the record exists or not.

Kind regards,

Hermann

Re: Performance issue on a select statement

Former Member — Wed, 18 Feb 2009 15:20:29 GMT

Hi!

You can create an additional index for the given fields, using SE11 transaction. They will consume some extra storage place, but oh well... But a specialized index will greatly improve the performace...

Regards

Tamá

Re: Performance issue on a select statement

Former Member — Thu, 19 Feb 2009 04:38:52 GMT

try to provide MATNR WERKS along with

WHERE bstyp EQ 'K'
AND banfn EQ l_eban-banfn
AND bnfpo EQ l_eban-bnfpo.

Cheers