06-29-2007 6:16 AM
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 BSISHKONT = SKB1SAKNR
JOIN SKAT ON BSISHKONT = SKATSAKNR
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.
06-29-2007 6:21 AM
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
06-29-2007 6:22 AM
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!
06-29-2007 6:23 AM
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.
06-29-2007 6:26 AM
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
06-29-2007 6:27 AM
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
06-29-2007 7:19 AM
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