topic Re: Performance issue with table access-BSEG in Application Development and Automation Discussions

Performance issue with table access-BSEG

Former Member — Wed, 27 Jan 2016 02:10:15 GMT

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

Re: Performance issue with table access-BSEG

Former Member — Wed, 27 Jan 2016 02:25:19 GMT

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

Re: Performance issue with table access-BSEG

Former Member — Wed, 27 Jan 2016 03:36:41 GMT

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...

Re: Performance issue with table access-BSEG

kiran_k8 — Wed, 27 Jan 2016 04:26:47 GMT

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.

Re: Performance issue with table access-BSEG

praveenboss — Wed, 27 Jan 2016 06:08:18 GMT

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.

Re: Performance issue with table access-BSEG

naren_patel — Wed, 27 Jan 2016 07:00:45 GMT

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.

Re: Performance issue with table access-BSEG

RaymondGiuseppi — Wed, 27 Jan 2016 07:03:39 GMT

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

Re: Performance issue with table access-BSEG

kiran_k8 — Wed, 27 Jan 2016 07:56:03 GMT

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.

Re: Performance issue with table access-BSEG

Former Member — Wed, 27 Jan 2016 16:10:24 GMT

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

Re: Performance issue with table access-BSEG

Ryan-Crosby — Wed, 27 Jan 2016 19:36:03 GMT

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

Re: Performance issue with table access-BSEG

bbalci — Wed, 27 Jan 2016 20:11:52 GMT

Yes ..

That's what I was about to write.

Re: Performance issue with table access-BSEG

Jelena_Perfiljeva — Wed, 27 Jan 2016 21:12:41 GMT

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.