Performance Question :

[Former Member]

Dear All,

I have a quick performance question. I am extracting all invoices (VBRK) from 2000 in ABAP program. which is taking long time.

In my select query where condition, I am passing only FKART (Billing type).

My doubt, Is it better if I pass Sales Org, Distribution channel also in my where clause. Will it improve my performance ..even though these 2 fields are not key fields in this particular table.

and my general doubt is .. is it helpful to pass non-key fields in where clause of select statement.

Thank you.

[Former Member]

If it will reduce the amount of data returned, it shoulkd help.

Rob

[Former Member]

Thank you All for your inputs..

Rob, I am thinking, if we pass non-key fields in where clause, it will actually take more time, as it needs to search for the particular criteria, other wise ..it will retrieve everything and should be little faster .. (again this is my guess not confidant)..

Brad, I knew the performance will be dependent on several other factors, but keeping everything idle or testing under same criteria, which of the select statement is good..

.

Select * from vbrk into table i_vbrk  where fkart = 'XXX' . 

or 

Select * from vbrk into table i_vbrk where fkart = 'xxx'  and 
                                                                   vkorg = 'zzz'' and 
                                                                   vtweg = 'ddd'. 

please note all 3 fields in where clause are non-fields and neither are secondary indexes.

bottom line question :: whether select statement will perform good .. if we pass many fields in where clause.

[Former Member]

I think it's time you actually try it out and see.

You have to run each SELECT multiple times and take the lowest time from each to do a comparison.

Rob

[brad_bohn]

Without being able to analyze it and not knowing the data distribution, I'd say neither one is very good. Given no secondary indexes outside of standard, what you've got is a table scan in both cases. The data transport may (or may not) be better on the second due to fewer rows returned (and thus less packets transported). I would add some fields to the where clause (FKDAT?) and add a secondary index that's appropriate so that at least you could trigger a bounded range scan instead of a full table scan.

[former_member585060]

Hi,

Try to fetch all the Billing documents from table VRKPA by providing FKART, it has other field like VKORG & VTWEG as primary key fields along with FKART. Based on the above table entires get the entries from VBRK, this may improve the performance.

Just have a look at the SAP Note 185530 - Performance: Customer developments in SD

Regards

Bala Krishna

[Former Member]

Aha, the "real" answer...

Just have a look at the SAP Note 185530 - Performance: Customer developments in SD

.

Use of this SAP Note should be MANDATORY for anyone who reports from SD...

Note also that index table VRKPA contains GJAHR, which may be useful to you for selecting by year.

[brad_bohn]

Try to fetch all the Billing documents from table VRKPA by providing FKART, it has other field like VKORG & VTWEG as primary key fields along with FKART

Sorry, but how do you figure that's better??? With no LEADING fields specified, you're still in a scan mode. It makes no difference that some fields are specified. Not to mention the fact that the use of that table is highly dependent on configuration. If you haven't configured anything to populate OR you've configured every partner function, you're way worse off than before...

[brad_bohn]

is it helpful to pass non-key fields in where clause of select statement

Well, that depends on the secondary indexes and the data distribution in your system, and of course, it also affects data transport...

[Former Member]

hi, just check if it affects the retrival process then only it can improve the performance.

[Former Member]

Hi,

When you have this kind of doubt, you can use SE30 to measure the runtime of your SELECTs.

Go to SE30 -> Tips & Tricks.

Use both sides to write you selection. For example:

In the left side write:

data: t_vbrk type table of vbrk.
select * 
from vbrk 
into table t_vbrk 
where FKART = 'F2B'.

And in the right side:

data: t_vbrk type table of vbrk.
select * 
from vbrk 
into table t_vbrk 
where FKART = 'F2B'
  and VKORG = '0082'.

Then press button "Measure Runtime".

So you will have the comparison that you need.

Regards,

Kate

[Former Member]

It is always better to give the primary fields in the table and then get it into the ABAP memory.

Once query is executed, then better to delete the entries not required.