JOIN Between BSIS and SKB1 - Rewars will be awarded.

[adnanmaqbool]

Dear Guys

I am developing a query to join BSIS and SKB1 tables and wan to run it for the whole financial year.

But this query is taking 5 to 7 hours to run. Is there any alternative to increase the performance. Queris are given below.

*selection to get OPEN ITEM data in to FTAB.

SELECT * FROM BSIS

INNER JOIN SKB1 ON BSIS_{HKONT = SKB1}SAKNR

JOIN SKAT ON BSIS_{HKONT = SKAT}SAKNR

INTO CORRESPONDING FIELDS OF TABLE FTAB

WHERE BSIS~HKONT IN GLACCT

AND BSIS~BUKRS = COMPY

AND BSIS~BUDAT IN DOCDT.

• MOVE 'OPEN' TO FTAB-OPENCLR.

• APPEND FTAB.

LOOP AT FTAB.

MOVE 'OPEN' TO FTAB-OPENCLR.

MODIFY FTAB.

ENDLOOP.

**selection to get CLEARED ITEM data in to FTAB.

SELECT * FROM BSAS

INNER JOIN SKB1 ON BSAS~HKONT = SKB1~SAKNR

JOIN SKAT ON BSAS~HKONT = SKAT~SAKNR

INTO CORRESPONDING FIELDS OF TABLE FTAB

WHERE BSAS~HKONT IN GLACCT

AND BSAS~BUKRS = COMPY

AND BSAS~BUDAT IN DOCDT.

• MOVE 'CLEARED' TO FTAB-OPENCLR.

• APPEND FTAB.

*

• ENDSELECT.

LOOP AT FTAB WHERE OPENCLR EQ ' '.

MOVE 'CLEARED' TO FTAB-OPENCLR.

MODIFY FTAB.

ENDLOOP.

**end

**selection to get OPEN ITEM data in to ITAB0.

SELECT HKONT AUGBL BUDAT DMBTR SHKZG

• UMSKZ

FROM BSIS

INTO CORRESPONDING FIELDS OF TABLE ITAB0

WHERE HKONT IN GLACCT

AND BUKRS = COMPY

AND BUDAT BETWEEN DAT1 AND DAT2.

**selection to get cleard ITEM data in to ITAB0.

SELECT HKONT AUGBL BUDAT DMBTR SHKZG

• UMSKZ

FROM BSAS

INTO CORRESPONDING FIELDS OF TABLE ITAB0

WHERE HKONT IN GLACCT

AND BUKRS = COMPY

AND BUDAT BETWEEN DAT1 AND DAT2.

[Former Member]

hi,

using <b>into corresponding fields of</b> ll degrade the performance

so create internal table with required fileds..

create views for table taking the required fields and remove the join condition...

this ll help..

Regards,

viji

[Former Member]

Hi Adnan,

Use secondary index of BSIS~1. What is your selection criteria? Can you low down it? Are you searching all Company codes and what is your date range?

You can have FOR ALL ENTRIES

sekect entried from BSIS

and then select entries from SKB1 for all entries SAKNR condition.

Can you get more selection criteria?

Reward if useful!

[Former Member]

HI,

Like this:

REPORT ztest NO STANDARD PAGE HEADING LINE-SIZE 80 MESSAGE-ID 00.

TABLES: bkpf, bseg, bsis.

DATA: bkpf_int TYPE TABLE OF bkpf WITH HEADER LINE,

bseg_int TYPE TABLE OF bseg WITH HEADER LINE,

bsis_int TYPE TABLE OF bsis WITH HEADER LINE,

BEGIN OF i_indata1 OCCURS 0,

t_pdate TYPE bkpf-budat,

t_kioid TYPE bseg-zuonr,

END OF i_indata1.

SELECT bukrs belnr gjahr budat

FROM bkpf INTO CORRESPONDING FIELDS OF TABLE bkpf_int

FOR ALL ENTRIES IN i_indata1

WHERE bukrs = 'BP01' AND

bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND

gjahr = sy-datum+0(4) AND

budat = i_indata1-t_pdate.

SELECT bukrs belnr gjahr buzei zuonr wrbtr shkzg

FROM bseg INTO CORRESPONDING FIELDS OF TABLE bseg_int

FOR ALL ENTRIES IN bkpf_int

WHERE bukrs = bkpf_int-bukrs AND

belnr = bkpf_int-belnr AND

gjahr = bkpf_int-gjahr AND

koart = 'S'. "GL Accounts

SORT: bkpf_int BY bukrs belnr gjahr,

bseg_int BY bukrs belnr gjahr buzei,

i_indata1 BY t_kioid.

LOOP AT bseg_int.

READ TABLE i_indata1 WITH KEY

t_kioid = bseg_int-zuonr

BINARY SEARCH.

CHECK sy-subrc = 0.

READ TABLE bkpf_int WITH KEY

bukrs = bseg_int-bukrs

belnr = bseg_int-belnr

gjahr = bseg_int-gjahr

BINARY SEARCH.

CHECK sy-subrc = 0.

MOVE: bkpf_int-budat TO bsis_int-budat,

bseg_int-zuonr TO bsis_int-zuonr,

bseg_int-wrbtr TO bsis_int-wrbtr,

bseg_int-shkzg TO bsis_int-shkzg.

APPEND bsis_int.

ENDLOOP.

kishi.

[Former Member]

Hi Adnan,

The BSIS table always contain the huge data. If you are selecting data using this it will definately take that much time, what you can try is remove the join and use FOR ALL ENTRIES instead.

Regards,

Atish

[Former Member]

I would suggest a few changes in approach.... obviously I don't know the volume of data at your site, but I have seen BSIS & BSAS get very large plus they contain many columns. Therefore I would:

- Only include the field names you want in your select i.e. those appearing in FTAB.

- Do the selections from BSIS and BSAS into internal tables, then get the other SKB1 & SKAT data afterwards using buffered lookups because many of the values will repeat i.e. not all GL accounts are open-item managed.

- Put the BUKRS first (and check SQL trace that index is being used).

- If you must join, make sure you have all the key fields e.g. SPRAS + KTOPL + SAKNR for SKAT and BUKRS + SAKNR for SKB1 (otherwise you could go table scanning for each join...)

I'll post an example later.

Message was edited by:

Jonathan Coleman

[Former Member]

Here's a sample that might help... it needs further work (e.g. no BSAS yet) and, as the comments suggest, with a production program I'd use additional local buffered lookups for the master data to reduce the app server workload even with tables that have record buffering themselves.

report zlocal_jc_bsis_select.

tables:
  bsis,   "Accounting: Secondary Index for G/L Accounts
  bsas,   "Accounting: Secondary Index for G/L Accounts (Cleared Items)
  skat,   "G/L Account Master Record (Chart of Accounts: Description)
  skb1,   "G/L account master (company code)
  t001.   "Company Codes

data:
  begin of gs_data,
    hkont               like bsis-hkont,
    augbl               like bsis-augbl,
    budat               like bsis-budat,
    dmbtr               like bsis-dmbtr,
    shkzg               like bsis-shkzg,
    txt20               like skat-txt20,
    begru               like skb1-begru,
    openclr(10)         type c,
  end of gs_data,
  gt_data               like gs_data occurs 0.

*======================================================================
* Selection screen
*======================================================================
parameters:
  p_bukrs               like bsis-bukrs memory id buk.

select-options:
  s_hkont               for bsis-hkont,
  s_budat               for bsis-budat.

*======================================================================
* Mainline
*======================================================================
start-of-selection.
  perform gt_data_fill.

*&---------------------------------------------------------------------*
*&      Form  gt_data_fill
*&---------------------------------------------------------------------*
form gt_data_fill.
*
* Clear out ur data array
*
  data:
    ls_data             like gs_data,
    lt_data             like gs_data occurs 0.

  clear: gt_data, gt_data[].
*
* Get the chart of account for the company code for SKAT lookups
*
  select single ktopl into t001-ktopl
    from t001
    where bukrs = p_bukrs.
*
* Only select the fields you need from Open Items
*
  perform write_time  "output log for performance
    using
      'Start BSIS select'.

  select hkont augbl budat dmbtr shkzg
    into corresponding fields of table lt_data
    from bsis
    where bukrs = p_bukrs
    and   hkont in s_hkont
    and   budat in s_budat.

  perform write_time  "output log for performance
    using
      'End BSIS select'.
*
* Fill in any blanks
*
  loop at lt_data into ls_data.

* Status
    ls_data-openclr = 'Open'.

* GL Account description
*   Note: Could tweak this more with local buffered lookups
    select single txt20 into ls_data-txt20
      from skat  "single buffered table
      where spras = sy-langu
      and   ktopl = t001-ktopl
      and   saknr = ls_data-hkont.

* GL Master Data info
*   Note: Could tweak this more with local buffered lookups
    select single begru into ls_data-begru
      from skb1  "single buffered table
      where bukrs = p_bukrs
      and   saknr = ls_data-hkont.

    append ls_data to gt_data.

  endloop.

  perform write_time  "output log for performance
    using
      'End BSIS fill blanks'.
* etc etc

endform.                    "gt_data_fill

*&---------------------------------------------------------------------*
*&      Form  write_time
*&---------------------------------------------------------------------*
form write_time
  using
    i_msg               type any.
* Write out a message for performance debugging...
  get time.
  write: / sy-datum, sy-uzeit, i_msg.
endform.                    "write_time