05-21-2009 10:46 AM
Hi All,
In one of my Program, i am using SELECT query with BSEG table, which is leading to performance issue while generating the report.
I have optimised the select query in all possible ways to reduce the execution time.
Still the report is taking almost 1 minute time to execute this select statment.
if not it_wbs1[] is initial.
select bukrs belnr gjahr dmbtr hkont projk from BSEG
into corresponding fields of table it_bseg
for all entries in it_wbs1
where bukrs = c_bukrs "1000
and gjahr = V_YEAR2
and shkzg = c_shkzg "H
and projk = it_wbs1-wbs_in
and hkont between c_hkont1 and c_hkont2. "'0001101001' and '0001999999'.
endif.
I heard that instead of using BSEG directly, we need to create a database view of BSEG with limited fields (Which are necessary for this select statement) and the database view should be queried instead of BSEG.
Is it worthful to reduce the execution time?
Or Any other idea to fine tune the Program in this regard?
Regards
Pavan
Moderator message - Moved to the correct forum
Edited by: Rob Burbank on May 21, 2009 9:37 AM
05-21-2009 3:27 PM
Please read Rob Burbank's blog [Quickly Retrieving FI document Data from BSEG|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7692] [original link is broken] [original link is broken] [original link is broken];
Rob, can you please create a sticky note asking everybody with the BSEG performance question to first read your blog?
Actually there is already a note [Please Read before Posting in the Performance and Tuning Forum Updated|;
May be you can add the BSEG blog link there?
Thanks.
Edited by: Edward Pelyavskyy on May 21, 2009 4:30 PM
05-21-2009 10:49 AM
Hi,
If possible try to use the complete primary key in the where clause, it will considerably reduce the runtime for this query.
Regards,
Mansi.
05-21-2009 10:52 AM
Hi,
Try LIke this.
if not it_wbs1[] is initial.
select bukrs belnr gjahr dmbtr hkont projk from BSEG
into table it_bseg
for all entries in it_wbs1
where bukrs = c_bukrs "1000
and gjahr = V_YEAR2
and shkzg = c_shkzg "H
and hkont between c_hkont1 and c_hkont2. "'0001101001' and '0001999999'.
and projk = it_wbs1-wbs_in
endif.
Also chk with SE30 & ST05.
Thanks & Reagrds,
Anagha DEshmukh
05-21-2009 11:01 AM
Hi ,
Remove the INTO CORRESPONDING FIELDS OF from the select quiery ,
because it is a very costly statement .
So first create a field string containing the fields which you want to select,
then create the table of that field string type. Now use the table with out
INTO CORRESPONDING FIELDS OF ...
Check the code -
DATA : BEGIN OF fs_bseg,
bukrs TYPE bseg-bukrs,
belnr TYPE bseg-belnr,
gjahr TYPE bseg-gjahr,
dmbtr TYPE bseg-dmbtr,
hkont TYPE bseg-hkont,
projk TYPE bseg-projk,
END OF fs_bseg,
it_bseg LIKE TABLE OF fs_bseg.
IF NOT it_wbs1[] IS INITIAL.
SELECT bukrs belnr gjahr dmbtr hkont projk FROM bseg into
table it_bseg " INTO CORRESPONDING is removed
FOR ALL ENTRIES IN it_wbs1
WHERE . . . .
.
.
.
ENDIF.
Regards
Pinaki
05-21-2009 11:07 AM
Hey why are u using BSEG table,get data from BSIS and BSAS instead
05-21-2009 11:09 AM
Hi,
Try using the PACKAGE SIZE. Please read out the below and the sample code:
What's the purpose of using PACKAGE SIZE in select statement?
To ease out risk in selecting of huge volume of records from the standard table,
the PACKAGE SIZE <No.> are used.
Where <No.> determines, how many records need to be selected at once.
In the following example, on every select, only 1000 records are being fetched and put into the new
internal table. This improves the performance by fetching over huge volume of records at one select.
*Sample Program
REPORT ZRAMANI_PKG.
DATA: I_VBAK LIKE VBAK OCCURS 0 WITH HEADER LINE.
DATA: I_VBAK_tmp LIKE VBAK OCCURS 0 WITH HEADER LINE.
DATA: V_LINES TYPE I.
SELECT *
FROM VBAK
INTO TABLE I_VBAK_tmp
* This statement brings only 1000 new records on every select & put into a
temporary internal table
PACKAGE SIZE 1000.
* Here above 1000 new records are dumped into a new internal table
APPEND LINES OF I_VBAK_TMP TO I_VBAK.
ENDSELECT.
IF SY-SUBRC = 0.
DESCRIBE TABLE I_VBAK LINES V_LINES.
WRITE: / 'Total Lines of VBAK:- ' , V_LINES.
ULINE.
SORT I_VBAK BY VBELN.
LOOP AT I_VBAK.
WRITE: / SY-TABIX, I_VBAK-VBELN.
ENDLOOP.
ENDIF.
Hope the above would give you more light on the Package Size also, for easing out to the
Performance issues.
Regards,
RamaniN
05-21-2009 11:14 AM
Create an index for table BSEG on the fields: bukrs, gjahr, shkzg, projk, hkont.
Best regards Jack
05-21-2009 2:45 PM
>
> Create an index for table BSEG on the fields: bukrs, gjahr, shkzg, projk, hkont.
Please research before answering - BSEG is a cluster table.
Rob
05-21-2009 12:04 PM
Hi ,
What I wrote in my last post that is correct .
You can check that by the GET RUN TIME field w_time..
DATA : w_time TYPE i.
GET RUN TIME FIELD w_time1.
Like the above way check the runtime of yours code and changed code
as suggested by me, You will understand.
Example - -
DATA : w_time TYPE i,
w_time1 TYPE i,
w_time2 TYPE i.
DATA : t_itab LIKE TABLE OF spfli,
BEGIN OF fs_itab,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
END OF fs_itab,
t_itab1 LIKE TABLE OF fs_itab.
GET RUN TIME FIELD w_time.
SELECT carrid
connid
FROM spfli INTO CORRESPONDING FIELDS OF TABLE t_itab.
GET RUN TIME FIELD w_time1.
SELECT carrid
connid
FROM spfli INTO TABLE t_itab.
GET RUN TIME FIELD w_time2.
WRITE / : w_time , w_time1 , w_time2.
Here the difference between w_time and w_time1 is the time taken for the
First select query (Where INTO CORRESPONDING is used) .
The difference between w_time1 and w_time2 is the time taken for the second
select query (where no CORRESPONDING is used) .
By comparing these difference you will understand
Regards
Pinaki
05-21-2009 12:34 PM
As BSEG is a cluster table, querying such table might lead to performance issues if proper input criteria is not given.
BSEG is accounting document segment table. So before writing a select query for BSEG, it is better to write select query for BKPF (accounting document header table) and fetch the required belnr (document number).
Based on the entries of belnr obtained from bkpf and other input parameters, write a select query for bseg using for all entries in bkpf-belnr.
This will help in better performance rather then directly writing query for bseg table.
05-21-2009 3:27 PM
Please read Rob Burbank's blog [Quickly Retrieving FI document Data from BSEG|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7692] [original link is broken] [original link is broken] [original link is broken];
Rob, can you please create a sticky note asking everybody with the BSEG performance question to first read your blog?
Actually there is already a note [Please Read before Posting in the Performance and Tuning Forum Updated|;
May be you can add the BSEG blog link there?
Thanks.
Edited by: Edward Pelyavskyy on May 21, 2009 4:30 PM
05-22-2009 1:11 PM
Try to use Index tables..
for this requirement fetch from table BSIS (Secondary Index table for G/L Accounts) will be much faster.
Cheers