Bseg performance issue

[Former Member]

I am having performance issue with the below select statement.

Select belnr dmbtr hkont koart zlsch from bseg into table it_bseg for all entries in it_bkpf where belnr =it_bkpf-belnr and koart eq 'K'.

Please suggest how can I improve the performance issue of this statement

[TammyPowlas]

Hi - what are you trying to achieve?

It may be faster to access the index tables such as BSIK or even the logical database (we use BRM for financial documents)

[michael_piesche]

You will have to read the db with partial primary key or use/create indexes for your key. (Currently you dont.)

Do an ST05 SQL trace analysis before and after changes of your coding in dev, quality and prod, to see whether for the db access keys were used, or whether a sequential search (searching the entire db instead of by keys) was performed:

• https://blogs.sap.com/2007/09/05/the-sql-trace-st05-quick-and-easy/
• https://blogs.sap.com/2014/02/11/code-inspector-integration-into-sql-trace-of-st05/

Right now, your best option is to add the Company Code (BUKRS) into your select statement.

• If it is just one you need to consider, that is perfect
  DATA yourbukr TYPE bukrs. " set single bukrs
• If it is multiple, use a range object with Include Equals options
  DATA yourbukrs TYPE RANGE OF bukrs. " set multiple bukrs, e.g. sign=I,option=EQ,low=yourbukr
  (definitly no exclusions, if patterns or ranges, they need to make use of the key or partial key and restrict the values very good and ).
• But even if you have to add all available Company Codes in the range object, it would still perform better than just having the Document Number (BELNR), as it will take use of a key search instead of a sequential search.

You might have to consider changing the "FOR ALL ENTRIES IN it_bkpf" into a range statement.

• e.g. "DATA yourbelnrs TYPE RANGE OF belnr_d" and fill with "sign=I,option=EQ,low=yourbelnr" from IT_BKPF.
• But than you can run into a too many restrictions dump, depending on the amount of restrictions and the setting of your system for restrictions (e.g. > 10'000)

If you want to tweak it even more, you need to add the Fiscal Year (GJAHR). I assume adding the Number of Line Item (BUZEI) is not helpful, as there would be too many values to consider. Not useful, if you add restriction(s) that are going to add/include all possible BUZEI.

Here are the Key components of BSEG.

BUKRS	BUKRS	CHAR	4	0	0	Company Code
BELNR	BELNR_D	CHAR	10	0	0	Accounting Document Number
GJAHR	GJAHR	NUMC	4	0	0	Fiscal Year
BUZEI	BUZEI	NUMC	3	0	0	Number of Line Item Within Accounting Document

This is how your coding would look like:

" necessary to tweak performance
DATA yourbukr TYPE bukrs. " set single bukrs
DATA yourbukrs TYPE RANGE OF bukrs. " set multiple bukrs, e.g. sign=I,option=EQ,low=yourbukr
" optional to tweak performance a little more
" Range IN yourbelnrs instead of FOR ALL ENTRIES IN it_bkpf
DATA yourbelnrs TYPE RANGE OF belnr_d. " set multiple belnr, e.g. sign=I,option=EQ,low=yourbelnr from IT_BKPF
" Selecting gjahr also
DATA yourgjahr TYPE gjahr. " set single gjahr
DATA yourgjahrs TYPE RANGE OF gjahr. " set multiple gjahr, e.g. sign=I,option=EQ,low=yourgjahr

" Option A) with FOR ALL ENTRIES IN it_bkpf
SELECT belnr dmbtr hkont koart zlsch 
FROM bseg INTO TABLE it_bseg 
FOR ALL ENTRIES IN it_bkpf 
WHERE BUKRS = yourbukr   " necessary for performance, if it's multiple, use range IN yourbukrs 
  AND belnr = it_bkpf-belnr 
* AND gjahr = yourgjahr  " optional, and if it's multipe, use range IN yourgjahrs
  AND koart eq 'K'.

" Option B) with range IN yourbelnrs instead of FOR ALL ENTRIES IN it_bkpf
SELECT belnr dmbtr hkont koart zlsch 
FROM bseg INTO TABLE it_bseg 
WHERE BUKRS = yourbukr    " necessary for performance, if it's multiple, use range IN yourbukrs 
  AND belnr IN yourbelnrs " not possible if there are too many restrictions (e.g. > 10'000)
* AND gjahr = yourgjahr   " optional, and if it's multipe, use range IN yourgjahrs
  AND koart eq 'K'.

[michael_piesche]

sap_amit1111, please let me know, if this is helping you, or whether there are still issues with your problem.

[venkateswaran_k]

Hi

If you are specific to select data where koart eq 'K',

It is better to use BSIK and BSAK tables (which are already indexed).

This will be giving faster results.

Regards,

Venkat

[Sandra_Rossi]

You say "BSIK and BSAK tables (which are already indexed)."

I guess you mean implicitly that they already have an index with first indexed column BELNR.

(it's meaningless to only say that a table is indexed - i.e. say indexed by what columns)