How to improve performance of this select statement (between a large table and view)?

[former_member195355]

Hiya,

I have the following select that takes ages to complete:

select     a~head_no

               b~vality_to

appending table lt_itab up to 1 rows from crm_ace_refkey

as a inner join v_acedsoh as b on

b~ref_key = a~ref_key
where a~head_no = ls_kaufn-kaufn.

The crm_ace_refkey table has around 9 million rows and view v_acedsoh has around 20 million rows.

I'm a bit stumped on how anything could be improved so any advice would be great!

Thanks in advance!

[rajkumarnarasimman]

Hi Robert,

Here we are getting single row from that table, in that case, I hope it is not required to use the INNER JOIN concept.

Kindly split the select conditions and check the performance.

select     a~head_no
               a~ref_key

into (l_headno, l_refkey) up to 1 rows from crm_ace_refkey
where head_no = ls_kaufn-kaufn.

select   b~vality_to
from v_acedsoh 
where ref_key = l_refkey up to 1 rows.

Regards

Rajkumar Narasimman

[che_eky]

Robert,

Your main selection is based on crm_ace_refkey-head_no = ls_kaufn-kaufn but the table field head_no is only part of a key. This will result in a full table scan, that is probably why it is taking so long.

It probably does not help that you are then joining view v_acedsoh again on a non key field.

Do you not have any more key or index fields that you could use instead?

Che

[former_member195355]

Hi both,

Thanks for your help.

Table crm_ace_refkey has an index for head_no, so I don't think that's the issue. As you mention it's probably the join with the v_acedsoh view that is causing the slow performance...

I've managed to find a couple of extra fields to use in the select and these seem to help.

Would you see any problems with using the following code - it certainly runs much faster:

select    a~head_no

               b~vality_to

appending table lt_itab up to 1 rows from crm_ace_refkey

as a inner join v_acedsoh as b on

b~ref_key = a~ref_key
where a~head_no = ls_kaufn-kaufn.
and  b~comp   = p_comp
and  b~bukrs  = p_bukrs.

[che_eky]

Hi Robert,

In my system there is no index on CRM_ACE_REFKEY-HEAD_NO only on CRM_ACE_REFKEY-REF_KEY. Ideally you would want to use CRM_ACE_REFKEY-LOGSYS and CRM_ACE_REFKEY-PROCESS_TYPE.

If this is not possible then what you propose will work, as you say it is already faster than what you had.

I am not sure if this will make any difference but it's worth a try:

select a~head_no b~vality_to appending table lt_xref3 up to 1 rows from
crm_ace_refkey as a inner join v_acedsoh as b on

b~comp    = p_comp
and  b~bukrs   = p_bukrs

and b~ref_key = a~ref_key
where a~head_no = p_kaufn.

Che

[former_member195355]

Thanks Che,

I think the index on head-no for CRM_ACE_REFKEY is a Z index created by us - I'll need to check in the morning...

I'm not sure your code would work, as both comp and bukrs need to exist in both crm_ace_refkey and v_acedsoh whereas they just exist in just v_acedsoh

Thanks for taking the time to offer advice.

[che_eky]

No they only need exist in v_acedsoh. Try it next time.

[former_member195355]

Hi Che,

Yes, you are right! The statement does work.

However, and I don't understand why, it is slower than the solution I wrote:

However it is still faster than the previous logic - so I'll mark your answer as correct.

Thanks again