Improve performance: SELECT statement in large table based on non-key field

[Former Member]

Hello ABAP gurus,

I am writing a program that requires selecting from table BKPF. This table contains over 32 million records on my system. How would you propose to select records that are not in the key and is not part of key in any of the table indexes? Any advice would be greatly appreciated.

Rae Ellen Woytowiez

[Former Member]

Hello ABAP gurus,

I am writing a program that requires selecting from table BKPF. This table contains over 32 million records on my system. How would you propose to select records that are not in the key and is not part of key in any of the table indexes? Any advice would be greatly appreciated.

Rae Ellen Woytowiez

[HermannGahm]

Hi,

depens on several things e.g.

how often you have to run that report

what timeframe you have to get it done

how much ressources are available in that time frame in that you have to run it

incase your selection is not based on indexed fields i would probably only

take care that a full table scan is used in order get efficient I/O combined with

low impact on the database cache.

Only in case this is not fast enough i would think about

parallelization on DB or application server level (depends on above things).

Kind regards,

Hermann

[Former Member]

What is the field and what would the WHERE clause look like for that field? Are there any other fileds in the WHERE?

Some problems have no good solution. It may be best to run this in the background.

Rob

[Former Member]

Hello Rob,

I am extracting using a select statement from table BKPF. The field in the where clause that I basing my selection is XBLNR. Since the primary key does not begin with XBLNR and there is no secondary index that begins with XBLNR the SELECT statement takes a long time. Do you have any suggestion as to how I can write this SELECT statement without it taking a long time to process?

Thanks!

Rae Ellen Woytowiez

[Former Member]

XBLNR may not be the first field of an index, but it is used in index BKPF~1. Assuming you are only looking at normal documents, BSTAT will be a space and maybe you can put the company code you need in the SELECT as well. In any event, have a look at [Using an Index When You Don't Have all of the Fields|/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields]

Rob

[Former Member]

Thanks Rob,

I read your blog. The performance of my SELECT statement improved greatly based on the advice you gave me!

Rae Ellen Woytowiez

[Former Member]

Hi,

Since data is more and If you are confused about using Key Fields or Index.

It's better to use Parallel Processing Concept.

So LUW will also work fast and no deadlock will occur.

Affable

Arbind

[Former Member]

Hello Arbind,

Can you explain further what you mean by using parallel processing? Can you give me a code example?

Thanks!

Rae Ellen Woytowiez

[Former Member]

Hi,

if you want to add key fields in your select statement then declare your key variable as constant with initial value and then use it in your select statement with condition GE.

Ex:-

Constant v_matnr type mara-matnr value is INITIAL.

select matnr 
from makt
where matnr GE v_matnr
and maktx like '%GENERIC%'.

so here you can trigger primary index even when you don't have any condition for your key field.

Rgds/Abhi

[Former Member]

> so here you can trigger primary index even when you don't have any condition for your key field.

one could guess that this works ... however it does not. You should not forget that there is a database interface

in the SAP system, this inserts the value of bukrs variable and knows that a

      
  LIKE '%'
  GE '0000'

is no restriction. Therfore this condition is deleted and not sent to the database, the DB will not take the primary key.

Solution for the question is actually simple:

Either it is executed rarely, then it will need some time (execute in the night or in parallel)

or it is executed so often that performance matters, then an index table is necessary.

!!! THERE IS NO FREE LUNCH or no perpetuum mobile !!!

Siegfried

[Former Member]

With seven secondary indices (in ECC 6.0) as well as primary key, there's no way to utilize ANY of the indices? And, no way to get a set of values for primary key before reading BKPF?

[Former Member]

sometimes solutions are found faster, if you get more information right from the start:

and instead of

* Use these selects in comparison
  r_bukrs-option = 'EQ'.
  r_bukrs-sign   = 'I'.
  SELECT bukrs
    FROM t001
    INTO r_bukrs-low.
    APPEND r_bukrs.
  ENDSELECT.

  SELECT bukrs belnr gjahr blart budat
    FROM  bkpf
    INTO  TABLE bkpf_int
    WHERE bukrs IN r_bukrs
    AND   belnr = p_belnr
    AND   gjahr = p_gjahr.

I would recommend

  SELECT bukrs belnr gjahr blart budat
    FROM  bkpf
    INTO  TABLE bkpf_int
    WHERE bukrs IN  ( SELECT bukrs FROM t001 )
    AND   belnr = p_belnr
    AND   gjahr = p_gjahr.

it is a bit faster and much easier to program.

Siegfried