topic Re: Optimising code: to select by keys only? in Application Development and Automation Discussions

Optimising code: to select by keys only?

Former Member — Mon, 24 Mar 2008 02:34:00 GMT

Hi guys, can anyone tell me if it's better to..

1. select all records with selection that are key fields?

2. select all records with selections from non key fields?

I can't get reliable results due to table buffering. currently, the 2nd way works faster for me. See below.

What are you opinions? Is there a more efficient way?

p.s. I'm doing it this way as I have determined that joining VBAK (sales header) with VBAP (sales item), with the selection parameters I desire is less efficient. Hence, I'm getting VBAK first before getting the item details

#1 -----------------------------------------------------------

SELECT vbak~vbeln

vbak_{lifsk vbak}faksk

vbuk~lfstk

INTO CORRESPONDING FIELDS OF TABLE gt_sohdr FROM vbak

INNER JOIN vbuk ON vbuk_{vbeln = vbak}vbeln

WHERE vbak~auart IN s_auart

AND vbak~vkorg IN s_vkorg.

DELETE gt_sohdr WHERE lfstk NE 'A'. "hdr: delv stat

DELETE gt_sohdr WHERE lifsk NE ''. "delv block

DELETE gt_sohdr WHERE faksk NE ''. "bill block

#2 -----------------------------------------------------------

SELECT vbak~vbeln

vbak_{lifsk vbak}faksk

vbuk~lfstk

INTO CORRESPONDING FIELDS OF TABLE gt_sohdr FROM vbak

INNER JOIN vbuk ON vbuk_{vbeln = vbak}vbeln

WHERE vbak~auart IN s_auart

AND vbak~vkorg IN s_vkorg

AND lfstk EQ 'A'

AND lifsk EQ ''

AND faksk EQ ''.

Re: Optimising code: to select by keys only?

Former Member — Mon, 24 Mar 2008 09:26:57 GMT

Hi,

Try this WB2_V_VBAK_VBAP2 standard view table.

Rgds

Vijay

Re: Optimising code: to select by keys only?

Former Member — Mon, 24 Mar 2008 09:40:06 GMT

To select by rpimary key would be mush faster and efficient

since the primary keys will have unique values

the data operation will be fast

also try to use the index of the table if possible

Regards

Shiva

Re: Optimising code: to select by keys only?

Former Member — Mon, 24 Mar 2008 09:43:34 GMT

Hmm.. I can't find that view, but here's a view that looks like a join of VBAK and VBUK

view: VBAKUK

Re: Optimising code: to select by keys only?

Former Member — Mon, 24 Mar 2008 16:56:31 GMT

Hi Wong,

The first options is definitely a better option as you can select the entries by the help of primary keys. You will have much less data after the proper filtration of data.

Thanks and Regards,

Samantak.

Rewards points for useful answers.

Re: Optimising code: to select by keys only?

Former Member — Tue, 25 Mar 2008 06:36:02 GMT

Hi,

Option 1 ( select all records with selection that are key fields) is definitely better. But in addition to that if you have more condition (might be non-key) it will perform better.

That is why query 2 is performing better. You have used join, so table bffering has no role

In #1 & #2 the main condition is same

WHERE vbak~auart IN s_auart

AND vbak~vkorg IN s_vkorg.

But in #2 you have specified some more condition - so it is performing better.

Please close the thread if it is fixed

Re: Optimising code: to select by keys only?

Former Member — Tue, 25 Mar 2008 09:00:39 GMT

Your second option is not a recommended one. You should not read more than you actually need.

In principle, it should not happen that the addition of further select criteria deteriorates the performance. However, in joins the following can happen:

First option starts with the conditions on vbak and joins vbuk, which gives good performance.

Second options, the optimizer thinks that the options on vbuk are more selective and starts on vbuk. In fact this is less effective, so the performance becomes worse.

+ You should try to add a hint which tells the database the start with the index on vbak

(database dependent9

+ Here a FOR ALL ENTRIES could be a better option, read on vbak first, add the other information with FOR ALL ENTRIES.

+ I am not sure whether a view will help, it is nothing different than a join

Siegfried

Re: Optimising code: to select by keys only?

Former Member — Wed, 26 Mar 2008 01:12:47 GMT

Ok, thanks for the inputs, everyone.

My findings:

1. Fetching VBAK without selection parameters, then filter, then fetching VBUK is not efficient.

2. A view appears slightly faster or gives the same performance. It could also be my imagination.

3. Adding more selection parameters in a JOIN does not deteoriate the performance.

To try:

4. Get VBAK with selection parameters, filter, then fetch VBUK

That's enough testing for me. I should get back to more productive work. Thank you all.