BSEG/RFBLG Performance

[Former Member]

Hi ,

After taking the trace ,i found database time is high due to access to few tables like BSEG which is a cluster table of RFBLG.

sql statement

SELECT

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO" , "TIMESTMP" , "PAGELG" , "VARDATA"

FROM

"RFBLG"

WHERE

"MANDT" = :A0 AND "BUKRS" = :A1 AND "BELNR" = :A2 AND "GJAHR" = :A3

ORDER BY

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO"

Abap code

SELECT *

FROM BSEG

WHERE BUKRS = S_BSET-BUKRS

AND BELNR = S_BSET-BELNR

AND GJAHR = S_BSET-GJAHR

AND KOART = 'K' .

Can you please guide me this. How do i further tune this . As these are standard tables.Im also not sure why is the abap code and sql statement is different? is it because of cluster table concept??Please help.

Thanks

[Former Member]

Hi ,

After taking the trace ,i found database time is high due to access to few tables like BSEG which is a cluster table of RFBLG.

sql statement

SELECT

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO" , "TIMESTMP" , "PAGELG" , "VARDATA"

FROM

"RFBLG"

WHERE

"MANDT" = :A0 AND "BUKRS" = :A1 AND "BELNR" = :A2 AND "GJAHR" = :A3

ORDER BY

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO"

Abap code

SELECT *

FROM BSEG

WHERE BUKRS = S_BSET-BUKRS

AND BELNR = S_BSET-BELNR

AND GJAHR = S_BSET-GJAHR

AND KOART = 'K' .

Can you please guide me this. How do i further tune this . As these are standard tables.Im also not sure why is the abap code and sql statement is different? is it because of cluster table concept??Please help.

Thanks

[Former Member]

Rule#1: NEVER do a SELECT * on BSEG.

Search the forum for the rest.

pk

[Former Member]

But can you also tell me ..why abap statement is different from sql statment in this case?

[Former Member]

Hi

It's very strange the performace are so low for reading the BSEG table, you're using the main key so I think it's correct your select, try to delete KOART in where condition.

Kishan

Rule#1: NEVER do a SELECT * on BSEG.

Why this strange rule?

If the main key fields (BUKRS, BELNR and GJAHR) are available there's no reason not to do a select with BSEG, it could use the table index (BSID, BSAD, BSIK.......) but only if the customer, vendor, g/l code are available

Max

[christine_evans]

BSEG contains 600+ fields, most of which you will never need and a large number of which will be empty anyway. It may not have a great effect on performance, but it is sensible to restrict the fields brought back to those you want to use.

I don't understand why your query is slow either, since you are specifying the key. Your S_BSET fields aren't blank are they?

The ABAP open SQL that you write and the real SQL displayed in the trace file will always be a bit different since SAP will translate the one into the other. In this situation, they will be even more different because you are selecting from a cluster table.

[ThomasZloch]

I assume this select is inside an outer loop. What do you consider "high database time"? Is the overall runtime acceptable or not? If not, run an SE30 trace, sort it by net time descending and tackle the most problematic statements first.

In general,

Thomas

[Former Member]

Hi ,

Thanks for all you replies.

Yes this is outside loop. I have taken both abap trace and performance trace already. I found the database time is almost 85 percent of total response time in which this statement was almost equal to that. Thats why it is expensive.

[Former Member]

Hi

That's depends on how many rows are in the main internal table S_BSET, you should check the time for a single selection of BSEG.

Perhaps the single selection takes few time, but there are a very large number of record in S_BSET, that means a very large number of selection of BSEG.

Now you should try to sort the S_BSET table for the fields used to read BSEG:

SORT S_BSET BY BUKRS BELNR GJAHR.

LOOP AT S_BSET.
   IF S_BSET-BUKRS <> BSEG-BUKRS OR
       S_BSET-BELNR <> BSEG-BELNR OR
       S_BSET-GJAHR <> BSEG-GJAHR.
    SELECT * FROM BSEG WHERE ......
    ENDSELECT.
  ENDIF.
ENDLOOP.

In this way you can limit the accesses to BSEG

Max

[Former Member]

Hi ,

I have similar issue acccess to BSAK,BKPF,BSIK also.

Can i implement the same code change for these things also.

For example: BSAK i have code as show below.

SELECT *

FROM BSAK

WHERE BUKRS = REC_ITAB-YBUKRS

AND LIFNR = REC_ITAB-YLIFNR

AND GJAHR = REC_ITAB-YGJAHR

AND BELNR = REC_ITAB-YBELNR .

MDMBTR = MDMBTR + BSAK-DMBTR .

ENDSELECT .

[Former Member]

I wouldn't do a SELECT * on any table let alone BSEG, for the basic reason that there's never a need to fetch all the fields from a table. Even if the performance is only marginally better without a SELECT * I still would prefer to take only the necessary fields, since even that amounts to something in the end when your data selection keeps piling up.

Moreover, with only the necessary fields in the internal table, it makes viewing data easier while debugging. Trivial reason, but pretty useful, since you dont have to waste time scrolling left and right...

pk

[ThomasZloch]

Just to back you up (not that you'd need it), it can make quite a big difference in workload (= runtime) whether all or only the required columns are being transferred from the database to application server.

However what is often misconceived is that when using SELECT * FROM <dbtab> in conjunction with INTO CORRESPONDING FIELDS OF TABLE <itab>, then only the columns declared in <itab> are being transferred, and not all columns of table <dbtab>. This is my observation in newer releases, not sure about the distant past.

Thomas

[Former Member]

Hi all,

Thanks for all your replies. But my question is only removing select * and specifying the fields in all the queries will help improving the performance to major extent? I mean is this the only solution for this. Or anything else prossible to further improve it.Just wanted to know as iam not an abaper.so thought will get clarify from you all.

[Former Member]

>

> Hi all,
> 
> Thanks for all your replies. But my question  is only removing select * and specifying the fields in all the queries will help improving the performance to major extent? I mean is this the only solution for this. Or anything else prossible to further improve it.Just wanted to know as iam not an abaper.so thought will get clarify from you all.

It will not. The problem is either that you are SELECTing a large number of records in the loop or elswhere. Have you run a performance trace (ST05) or runtime analysis (SE30) to make sure where the problem lies?

Rob

[Former Member]

Hi

It depends on what the report needs to do, it's not easy to say that having so few informations.

I can say if the report starts from BSET data and needs to have item data, the BSEG selection is only solution.

So it can use some tricks (as those explained in the previous answers), but It can't replace BSEG selection with another one.

Now probably it can try to improve the performace of whole report, but it needs to know its logic flow, so the ABAP code.

I think the performance problem (in your case) can be limitated to BSEG selection only, because you can delete it, so probably the program should be rewritten in order to improve the performance.

And you need to consider how many data (documents) have to be analyzed, because if the reports has to extract a very large number of data, you need to accept the report takes very long timefor elaboration

Max