topic Re: Multiple selection in Application Development and Automation Discussions

Multiple selection

Former Member — Wed, 04 Feb 2009 06:52:23 GMT

Hi,

I have a performance issue on the following select statement.

SELECT kunnr vbeln FROM vakpa

INTO TABLE t_vakpa

WHERE kunde IN s_kunnr

AND parvw = 'AG'

AND vkorg = p_vkorg

AND trvog IN r_trvog

AND vkbur IN s_vkbur

AND vtweg IN r_vtweg

AND auart IN r_auart.

This select statement is using the variant Z00( vkorg kunde kunnr ) from vakpa table.But the value for kunnr field is not passed in the above select statement.How does the optimiser select this index when value for kunnr field is not passed in where condition?In the selection screen range of values are passed for kunde field.

I have run the sql trace for this select statement in ST05.The number of estimated costs is 953.Instead of passing ranges for kunde field if we use multiple selection for this field,the no of estimated costs is 1.Can anyone suggest me how to improve the performance of this statement?

Re: Multiple selection

Former Member — Wed, 04 Feb 2009 08:21:44 GMT

i didnt got what you mean to ask exactly....

can you plz elaborate

thanks

mohit

Re: Multiple selection

ThomasZloch — Wed, 04 Feb 2009 08:31:43 GMT

I assume "variant Z00" = "index Z00".

Primary key: KUNDE, PARVW, VKORG etc.

Your Z00 index: VKORG, KUNDE, KUNNR

If you leave S_KUNNR blank and give a single value in P_VKORG, I would choose Z00 as well if I were an optimizer.

Problem is that just VKORG is probably not very selective. In the end, a full table scan is done on VAKPA. This table is only useful when you can specifiy one or few partner numbers.

Thomas

Re: Multiple selection

Former Member — Wed, 04 Feb 2009 08:32:40 GMT

Hi,

The value for kunde field is passed in the form of ranges in the above select statement.I think because of this it is taking long time for execution of this select statement.Instead if we use multiple selection I think the performance can be improved.If I am wrong please suggest me a correct way to improve the perfomance of this select statement.

Re: Multiple selection

Former Member — Wed, 04 Feb 2009 08:40:03 GMT

Hi Thomas,

I mean to say the kunnr field is not used in the where condition of the select statement whereas the other 2 fields(vkorg and kunde) were used.Then how can the optimiser select Z00 index?How can we improve the performance of this select statement?

Re: Multiple selection

ThomasZloch — Wed, 04 Feb 2009 08:50:01 GMT

The indexes are evaluated top down, so even if only the first of three fields is provided, this is better than if the first of many field is not provided.

But do I understand correctly that S_KUNNR actually contains selection values when you run the query? Which ones?

Thomas

Re: Multiple selection

Former Member — Wed, 04 Feb 2009 09:12:30 GMT

While running the query S_KUNNR has values 11536001 to 11563000.But if the values are through multiple selection the number of estimated costs is 1.I have checked this in ST05.

Re: Multiple selection

ThomasZloch — Wed, 04 Feb 2009 09:17:42 GMT

I'm not sure about the accuracy of these estimated costs. Maybe an expert in this matter could comment here.

If the range 11536001 to 11563000 represents a large fraction of the overall VAKPA entries, then the optimizer might decide that it is better to access via range scan for index Z00 rather than a full table scan for the primary index.

It might be worth a try to update the table statistics for VAKPA and see if this changes anything.

Thomas

Re: Multiple selection

Former Member — Wed, 04 Feb 2009 10:12:26 GMT

Hi Thomas,

What does index 'INDEX SKIP SCAN' mean in explain sql trace in ST05 transaction?

Re: Multiple selection

ThomasZloch — Wed, 04 Feb 2009 11:43:27 GMT

A perfect expression for the SAP Help, SDN or Google search box.

For example I found this interesting presentation on CBO cost calculation, now it's just a matter of understanding everything, and it's just a "high level overview"...

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d0eefeb8-f789-2a10-baab-f7690451bca1

Thomas