topic Re: Bseg data Reading slow in Application Development and Automation Discussions

Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:12:41 GMT

I have done some programs for g/l balance frm bseg table ,its very slow to retriving records,even i declared proper where conditions in select query...

advise fast retrive...

any thing like indexing the oracle table ??? or any performance programing ?

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:20:02 GMT

Hi prakash,

1. If everything is fine,

then try secondary indexes.

2. try it from se11 itself.

3. It will IMPROVE the performance

DRASTICALLY !

regards,

amit m.

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:23:43 GMT

Hi Prakash,

Could you please specify what sort of queries you have used on the BSEG table.

Thanks & Regards,

Yogeshwari Agashe.

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:26:45 GMT

since BSEG is a big table in application server ..try to access it for minimum number of times..try to fetch the data at a time from the table and put it in some internal tables(presentation server) and use these tables for further operations..

reward if useful...

Re: Bseg data Reading slow

andreas_mann3 — Tue, 21 Feb 2006 12:27:51 GMT

Hi,

for G/L use table bsas/bsad if possible

otherwise try to use tables GLT0 or Cosp for balances.

If you need single items, start your selection with

bkpf and indicated fields / key-fields

Andreas

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:30:51 GMT

check this link if it helps u.

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:31:48 GMT

SELECT * FROM bkpf INTO CORRESPONDING FIELDS OF t_bkpf WHERE gjahr in ( p_gjahr,wa_gjahr)AND budat IN

p_budat AND blart EQ 'RE'.

SELECT * FROM bseg INTO CORRESPONDING FIELDS OF t_bseg WHERE belnr EQ t_bkpf-belnr AND

gjahr EQ t_bkpf-gjahr AND

hkont EQ p_hkont AND

ebeln NE space AND

augbl EQ space AND

bschl IN ('86','96','40','50') AND

bustw EQ 'RE01'.

t_po-low = t_bseg-ebeln .

t_po-sign = 'I'.

t_po-option = 'EQ'.

APPEND t_po.

APPEND t_bseg.

MOVE-CORRESPONDING t_bseg TO t_re.

APPEND t_re.

ENDSELECT.

CLEAR t_bseg.

SORT t_po BY low.

DELETE ADJACENT DUPLICATES FROM t_po.

SELECT * FROM bseg INTO CORRESPONDING FIELDS OF wa_bdc

WHERE

hkont NE p_hkont AND

gjahr EQ p_gjahr AND

ebeln IN t_po AND

bschl IN

('89','99','86','96','40','50') AND

augbl EQ space AND

bustw EQ 'WE01'.

MOVE-CORRESPONDING wa_bdc TO t_bseg.

APPEND t_bseg.

ENDSELECT.

pls advise any problem with it ??

Re: Bseg data Reading slow

andreas_mann3 — Tue, 21 Feb 2006 12:36:24 GMT

Hi,

you <b>must</b> fill fields:

<b> bukrs, belnr, gjahr</b> in where clause of bseg

othw. you get performance problem

2nd tip: never use select * but select (fld)

or select f1 f2 f3..

pls reward point for useful answers

Andreas

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:40:01 GMT

Hi,

Check with your basis if the stistics of those tables are updated, this cause peformance problems.

Hope this helps,

Gabriel

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:40:38 GMT

HI prakash

you have to use key fields like burks,belnr etc for selecting rows from bseg becoz these are the fields which have indexes.

one more advice you can use tables like BSIS/BSAS->customer data for open/close items

BSIK/BSAK -->vendor data for open/close items

or try

BSID/BSAD->secondary index for g/L account

regards

kishore

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:48:03 GMT

Hi,

Try to use the following VIEWS

V_VBSEGD

V_VBSEGK

V_VBSEGS.

Check for other views also.

Or otherwise create secondary index.

Thanks & Regards,

Venkat Ramanan

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:48:05 GMT

Hi Prakash

1. dont use nested selects.

2.dont use Select and Endselect.

3.Dont use CORRESPONDING fields

my be this help.

1. try to use select single on the table bkpf by giving all the key fields in the where condition then have a "is not initial" check then you can do a select on bseg.

hope this will help you to improve your speed in getting data.

regards

Lakshmikanth.

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 12:49:13 GMT

Hi,

Try to use the following VIEWS

V_VBSEGD

V_VBSEGK

V_VBSEGS.

Check for other views also.

Or otherwise create secondary index.

Thanks & Regards,

Venkat Ramanan

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 15:12:58 GMT

First - BSEG is a cluster table and I don't think you can create an index on it. Second - for the first two selects, you can:


REPORT ztest MESSAGE-ID zc.

TABLES: bkpf, bseg.

******* new ********
TABLES: t001, bsis, bsas.
******* new ********

DATA: BEGIN OF t_bkpf OCCURS 0.
        INCLUDE STRUCTURE bkpf.
DATA: END   OF t_bkpf.

DATA: BEGIN OF t_bseg OCCURS 0.
        INCLUDE STRUCTURE bseg.
DATA: END   OF t_bseg.

DATA: BEGIN OF t_re OCCURS 0.
        INCLUDE STRUCTURE bseg.
DATA: END   OF t_re.

DATA: BEGIN OF wa_bdc OCCURS 0.
        INCLUDE STRUCTURE bseg.
DATA: END   OF wa_bdc.

******* new ********
DATA: BEGIN OF doc_key OCCURS 0,
        bukrs LIKE bseg-bukrs,
        belnr LIKE bseg-belnr,
        gjahr LIKE bseg-gjahr,
      END   OF doc_key.
******* new ********

DATA: p_hkont LIKE bseg-hkont.

RANGES: p_gjahr  FOR bkpf-gjahr,
        wa_gjahr FOR bkpf-gjahr,
        p_budat  FOR bkpf-budat,
        t_po     FOR bseg-ebeln,
        r_bukrs  FOR t001-bukrs.

******* new ********
r_bukrs-option = 'EQ'.
r_bukrs-sign   = 'I'.
SELECT bukrs FROM t001 INTO r_bukrs-low
  WHERE spras = sy-langu.
  APPEND r_bukrs.
ENDSELECT.
******* new ********

SELECT * FROM bkpf INTO CORRESPONDING FIELDS OF t_bkpf
******* new ********
WHERE bukrs IN r_bukrs AND
      bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND
******* new ********
      gjahr IN (p_gjahr, wa_gjahr) AND
      budat IN p_budat AND
      blart EQ 'RE'.
  SELECT * FROM bseg INTO CORRESPONDING FIELDS OF t_bseg
******* new ********
   WHERE bukrs EQ t_bkpf-bukrs AND
******* new ********
         belnr EQ t_bkpf-belnr AND
         gjahr EQ t_bkpf-gjahr AND
         hkont EQ p_hkont AND
         ebeln NE space AND
         augbl EQ space AND
         bschl IN ('86','96','40','50') AND
         bustw EQ 'RE01'.
    t_po-low = t_bseg-ebeln .
    t_po-sign = 'I'.
    t_po-option = 'EQ'.
    APPEND t_po.
    APPEND t_bseg.
    MOVE-CORRESPONDING t_bseg TO t_re.
    APPEND t_re.
  ENDSELECT.
ENDSELECT.

I haven't looked at how the last select can be speeded up.

Rob

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 16:22:29 GMT

Prakash n Rob,

Suggestion: It will be good to populate only required fields from BKPF and BSEG. This will help a lot in enhancing performance as BSEG is a cluster table.

Also we can create INDEX on cluster tables.

ashish

Message was edited by: Ashish Gundawar

Re: Bseg data Reading slow

Former Member — Tue, 21 Feb 2006 17:11:08 GMT

This is the final code I get. Try it out:


REPORT ztest MESSAGE-ID zc.

TABLES: bkpf, bseg.

******* new ********
TABLES: t001, ekbe.
******* new ********

DATA: BEGIN OF t_bkpf OCCURS 0.
        INCLUDE STRUCTURE bkpf.
DATA: END   OF t_bkpf.

DATA: BEGIN OF t_bseg OCCURS 0.
        INCLUDE STRUCTURE bseg.
DATA: END   OF t_bseg.

DATA: BEGIN OF t_re OCCURS 0.
        INCLUDE STRUCTURE bseg.
DATA: END   OF t_re.

DATA: BEGIN OF wa_bdc OCCURS 0.
        INCLUDE STRUCTURE bseg.
DATA: END   OF wa_bdc.

******* new ********
DATA: BEGIN OF doc_key OCCURS 0,
        bukrs LIKE bseg-bukrs,
        belnr LIKE bseg-belnr,
        gjahr LIKE bseg-gjahr,
      END   OF doc_key.
DATA: BEGIN OF ir_key OCCURS 0,
        belnr LIKE bseg-belnr,
        gjahr LIKE bseg-gjahr,
      END   OF ir_key.
******* new ********

DATA: p_hkont        LIKE bseg-hkont,
      v_reference    LIKE bkpf-awtyp,                   "REFERENCE KEY
      v_objectkey    LIKE bkpf-awkey.                   "OBJECT KEY

RANGES: p_gjahr  FOR bkpf-gjahr,
        wa_gjahr FOR bkpf-gjahr,
        p_budat  FOR bkpf-budat,
        t_po     FOR bseg-ebeln,
        r_bukrs  FOR t001-bukrs.

******* new ********
r_bukrs-option = 'EQ'.
r_bukrs-sign   = 'I'.
SELECT bukrs FROM t001 INTO r_bukrs-low
  WHERE spras = sy-langu.
  APPEND r_bukrs.
ENDSELECT.
******* new ********

SELECT * FROM bkpf INTO CORRESPONDING FIELDS OF t_bkpf
******* new ********
WHERE bukrs IN r_bukrs AND
      bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND
******* new ********
      gjahr IN (p_gjahr, wa_gjahr) AND
      budat IN p_budat AND
      blart EQ 'RE'.
  SELECT * FROM bseg INTO CORRESPONDING FIELDS OF t_bseg
******* new ********
   WHERE bukrs EQ t_bkpf-bukrs AND
******* new ********
         belnr EQ t_bkpf-belnr AND
         gjahr EQ t_bkpf-gjahr AND
         hkont EQ p_hkont AND
         ebeln NE space AND
         augbl EQ space AND
         bschl IN ('86','96','40','50') AND
         bustw EQ 'RE01'.
    t_po-low = t_bseg-ebeln .
    t_po-sign = 'I'.
    t_po-option = 'EQ'.
    APPEND t_po.
    APPEND t_bseg.
    MOVE-CORRESPONDING t_bseg TO t_re.
    APPEND t_re.
  ENDSELECT.
ENDSELECT.

CLEAR t_bseg.
SORT t_po BY low.
DELETE ADJACENT DUPLICATES FROM t_po.

******* new ********
* From the PO history, get the material document for the IR
SELECT belnr gjahr FROM  ekbe
  INTO CORRESPONDING FIELDS OF TABLE ir_key
       WHERE  ebeln  IN t_po
       AND    gjahr  IN p_gjahr
       AND    vgabe  = '2'.
* Now get the accounting document for the material document
LOOP AT ir_key.
  v_objectkey+00(10) = ir_key-belnr.
  v_objectkey+10(10) = ir_key-gjahr. "BELNR+YEAR
  v_reference        = 'MKPF'.
ENDLOOP.
SELECT bukrs belnr gjahr FROM  bkpf
  APPENDING CORRESPONDING FIELDS OF TABLE doc_key
  WHERE  awtyp  = v_reference
  AND    awkey  = v_reference.
******* new ********

SELECT * FROM bseg INTO CORRESPONDING FIELDS OF wa_bdc
  FOR ALL ENTRIES IN doc_key
  WHERE bukrs = doc_key-bukrs AND
        belnr = doc_key-belnr AND
        gjahr = doc_key-gjahr AND
        hkont NE p_hkont AND
        ebeln IN t_po AND
        bschl IN ('89','99','86','96','40','50') AND
        augbl EQ space AND
        bustw EQ 'WE01'.
  MOVE-CORRESPONDING wa_bdc TO t_bseg.
  APPEND t_bseg.
ENDSELECT.

The other suggestions about nested selects, select/endselect, into corresponding, and retrieving only the fields you need are all good. I haven't done this to simplify the coding. I don't think you can use scondary index tables (BSIS and BSAS) because you are checking a not equal condition on HKONT.

There are a lot of other conditions in the selects that I haven't looked at. Maybe they should be done outside of the selects. I don't know. I also haven't looked at the logic

In 4.6C you cannot create an index on a cluster table. Since the database doesn't know the fields in the cluster, I don't see how it could be possible. (Maybe in other RDBMS??) In any event, it's a bad idea to create an index on an SAP table to speed up one report. There is overhead in updating the index each time the table is updated. With a little extra coding, you can almost always find a way to use existing indices.

Rob

I modified the code somewhat. The original code uses p_gjahr as both a select-option and a parameter. I changed it so that it is a select option.

Message was edited by: Rob Burbank

Re: Bseg data Reading slow

Former Member — Wed, 22 Feb 2006 04:36:16 GMT

hi,

better you use ldb (logical database) create ldb and insert the tables you need data from in the hierarchy how you want to fetch data .

if you need more detail you can send me mail at sap.manish@gmail.com i will send you all the details .this way you can solve the problem.