topic Re: Query performance in Application Development and Automation Discussions

Query performance

Former Member — Sun, 29 Mar 2009 20:17:01 GMT

Hi Gurus,

please can you tell me how to speed up the performance of a query where I used a custom field (NUMEROBOLLA) with a search coded as below:

clear NUMEROBOLLA.

select single XABLN from LIKP into NUMEROBOLLA

where vbeln = CONSEGNA.

CONSEGNA is another custom field where I retrive SD Delivery number.

Thank you very much

Kind Regards

Andrea

Re: Query performance

Former Member — Mon, 30 Mar 2009 01:24:51 GMT

HI,

If you are writing the SELECT Statement in the LOOP..ENDLOOP statement then it can be a performance issue. You can this select statement outside the Loop using SELECT ..FOR ALL ENTRIES and read the table for the XABLN in the Loop.

Re: Query performance

Former Member — Mon, 30 Mar 2009 04:31:15 GMT

Hi:

To avoid this type of dilemna, i would advice to use function module like C1CE_LIKP_READ

Regards

Shashi

Re: Query performance

Former Member — Mon, 30 Mar 2009 07:41:14 GMT

Avoid using select single in loop.

If possible try to use all the key fields in where condition.

for eg.

CONSTANTS: c_vbeln TYPE vbfa-vbeln VALUE IS INITIAL,

c_posnn TYPE vbfa-posnn VALUE IS INITIAL,

c_vbtyp_n TYPE vbfa-vbtyp_n VALUE IS INITIAL,

CASE1:

SELECT vbelv posnv vbeln posnn vbtyp_n rfmng meins

INTO TABLE i_vbfa FROM vbfa

WHERE vbelv IN so_vbeln

AND posnv IN so_posnv.

CASE2:

SELECT vbelv posnv vbeln posnn vbtyp_n rfmng meins

INTO TABLE i_vbfa FROM vbfa

WHERE vbelv IN so_vbeln

AND posnv IN so_posnv

AND vbeln GE c_vbeln

AND posnn GE c_posnn

AND vbtyp_n GE c_vbtyp_n.

This will definitely improve the performance to a considerable extent.

Edited by: harsh bhalla on Mar 30, 2009 1:11 PM

Re: Query performance

ThomasZloch — Mon, 30 Mar 2009 10:55:30 GMT

VBELN is the primary key of LIKP, so there is no problem with your query.

Thomas

Re: Query performance

former_member131774 — Tue, 31 Mar 2009 08:30:59 GMT

You use key field in the search so this sql does not have problem.

But you need to check if this kind of sql is used inside the LOOP.

Also you need to check NUMEROBOLLA is the same type of XABLN.

Re: Query performance

Former Member — Thu, 02 Apr 2009 09:21:27 GMT

Please try to include some more conditions in where .

This will provide slight improvement in performance.

Also , if possible replace Select single from Database table to Read statement on the internal table.

Re: Query performance

valter_oliveira — Sat, 04 Apr 2009 09:35:29 GMT

"Please try to include some more conditions in where. This will provide slight improvement in performance."

How can someone say this things? Why don't you answer only if you know what you are saying?

And how can you give points to this? Please read what Thomas said ... that's your correct answer.

Regards and good luck!

Valter Oliveira.