Performance issue with table access-BSEG

[Former Member]

Hi,

I have written below the select query.

REFRESH i_bseg.

   SELECT * from bseg INTO TABLE i_bseg

     WHERE bukrs =  i_parct-bukrs

       AND belnr <> i_parct-belnr

       AND augbl =  i_parct-belnr

       AND auggj =  i_parct-gjahr.

   CHECK NOT i_bseg[] IS INITIAL.

   SELECT * from bseg APPENDING TABLE i_bseg

     WHERE bukrs = i_parct-bukrs

       AND belnr = i_parct-belnr

       and gjahr = i_parct-gjahr

       AND augbl = SPACE.

If I run the report then the first select is taking too much time, I understand since its and cluster table so backend RFBLG~0 will be used. Please find below the execution plan for the query.

2 TABLE ACCESS BY INDEX ROWID RFBLG

  ( Estim. Costs = 262.205 , Estim. #Rows = 317.601 )

  Estim. CPU-Costs = 2.035.079.874 Estim. IO-Costs = 261.961

    1 INDEX RANGE SCAN RFBLG~0

      ( Estim. Costs = 2.385 , Estim. #Rows = 317.601 )

      Search Columns: 2

      Estim. CPU-Costs = 95.504.949 Estim. IO-Costs = 2.374

      Access Predicates Filter Predicates

I tried with many forums and table BSIS can be used...can we create index on BSIS(BUKRS, AUGBL and GJAHR) and select the BELNR again use for all entries in BSEG?

Please advise.

Regards,

A Vadamalai

[Former Member]

Why Not select in BSEG using only the primary keys you provided? And then manipulate the appending of entries via loop?

[Former Member]

Hi,

Have you seen my where clause?

     WHERE bukrs =  i_parct-bukrs

       AND belnr <> i_parct-belnr

       AND augbl =  i_parct-belnr

       AND auggj =  i_parct-gjahr.

Primary index will not be used right? since I used '<>'.

Experts, please advise...

[kiran_k8]

Hi,

Besides checking on usage of Primary Keys, you can also explore using "Open Cursor/Close Cusor".

There are a couple of discussions in SCN on usage of NE in the where clause some saying it will affect the performance and some saying it doesn't matter.But I didn't find any documentation from SAP on this topic.I prefer a safer approach and avoid using NE in the where clause.

K.Kiran.

[praveenboss]

Hi Vadamalai,

                       BSEG Table Taking long time to fetching data in internal table.

                       option are limited , you can restrict time using.

                       primary key in selected field

                       (Don't use * , give field name, select only usable  fields )

                       giving right where condition's  .

Thanks .

praveen.

[naren_patel]

Hi Vadamalai,

Option 1 :

                    Instead of going to BSEG table . You can use Secondary index tables : like BSAK, BSIS, BSAD, BSID.

Option 2:

                  First Go to BKPF table. Get your required Accounting document nos. then take that all accounting                                document no and financial year in range table, and Pass the Accounting document no and Financial year                    to BSEG table . Then Delete records which you don't  need using DELETE statement.

Thanks,

Naren.

[RaymondGiuseppi]

You could try to select from one (or two) of the secondary index tables, then read missing fields from BKPF/BSEG using primary keys already selected.

Hint: Secondary index tables are transparent tables (so index, JOIN allowed) named as BS[I,A][S,K,D,M] with A for cleared items, I for not cleared items, S for general ledger, K for vendor (Kreditor), D for customer (Debitor) and M for material.

In your first select, you look for cleared items (AUGBL, etc), so you may consider BSAS or BSE_CLR.

NB: BSEG is cluster table, so no JOIN and only access with partial primary key (first keys of RFBLG) respecting sequence can be optimized, also the data is not stored in row, so using or not "*" wont't reduce charge on database server, only reduce volumn of data stored on Application server (after the hidden "import" data execution from database extract)

Regards,

Raymond

[kiran_k8]

Raymond,

" BS[I,A][S,K,D,M] with A for cleared items, I for not cleared items, S for general ledger, K for vendor (Kreditor), D for customer (Debitor) and M for material."

Liked the way you had put the above.Easy to read and remember.Thanks.

K.Kiran.

[Ryan-Crosby]

Hi,

To elaborate only slightly further on what Raymond has said - based on your WHERE clause you are asking the system to conduct a full table scan to identify rows which match because of the structure:

If you need fields that do not exist in the secondary tables then you would need to identify first and then select solely by primary key.

Regards,

Ryan Crosby

[Former Member]

Hi,

Have you tried this :

SELECT * from bseg INTO TABLE i_bseg

     WHERE bukrs =  i_parct-bukrs

           AND augbl =  i_parct-belnr

           AND auggj =  i_parct-gjahr.

   CHECK NOT i_bseg[] IS INITIAL.

      delete i_bseg where belnr = i_parct-belnr.

Regards,

Pallavi

[bbalci]

Yes ..

That's what I was about to write.

[Jelena_Perfiljeva]

I would suggest to clarify what you are trying to achieve exactly and what's the deal with that i_parct.

It seems like you are very much wrapped up in the technical details of one problem here and might not seeing that a completely different solution could exist. And we can't help without understanding what you're doing exactly.

The index tables (such as BSIS) are helpful when you can take advantage of their index, i.e. when you have a customer / vendor / GL account. Without that you're digging through the whole accounting history, which doesn't seem to make a lot of sense and makes one wonder what the specific business requirement is.