How to optimize select from BSAD

[Former Member]

Hi,

I 've problem selecting records from BSAD into my internal table ta_bsid in report with select statement.

I use secondary index for this select statement.

Number of records in BSAD ~3 mln. In BKPF ~2 mln.

How can I optimize this select statement?

Is there any tips how to get more memory space in Oracle for select statement with table BSAD or only for my report?

********************************************

DATA: BEGIN OF st_bsid OCCURS 0,

dmbtr LIKE bsid-dmbtr,

hkont LIKE bsid-hkont,

blart LIKE bsid-blart,

budat LIKE bsid-budat,

stblg LIKE bkpf-stblg,

shkzg LIKE bsid-shkzg,

belnr LIKE bsid-belnr,

bukrs LIKE bsis-bukrs,

gjahr LIKE bsid-gjahr,

zbd1t LIKE bsid-zbd1t,

zfbdt LIKE bsid-zfbdt,

zdate LIKE bsid-zfbdt,

zsign(1) TYPE c,

manst LIKE bsid-manst,

END OF st_bsid.

DATA: BEGIN OF ta_bsid OCCURS 0.

INCLUDE STRUCTURE st_bsid.

DATA: END OF ta_bsid.

**********************************************

Select statement:

SELECT bsad~bukrs

bsad~gjahr

bsad~belnr

bsad~budat

bsad~blart

bsad~shkzg

bsad~dmbtr

bsad~hkont

bsad~zfbdt

bsad~zbd1t

bsad~manst

bkpf~stblg

APPENDING CORRESPONDING FIELDS OF TABLE ta_bsid

FROM bsad INNER JOIN bkpf ON

bsad_{mandt = bkpf}mandt AND

bsad_{bukrs = bkpf}bukrs AND

bsad_{gjahr = bkpf}gjahr AND

bsad_{belnr = bkpf}belnr

WHERE bsad~mandt = sy-mandt AND

bsad~bukrs IN so_bukrs AND

( bsad~augdt = '00000000' OR

bsad~augdt > pa_date ) AND

bsad~budat <= pa_date AND

bsad~blart IN ta_blart AND

bsad~hkont IN ta_hkont AND

bsad~shkzg = 'S' AND

bkpf~stblg = ''.

***********************************************

Select options:

so_bukrs:

SIGN | OPTION| LOW |HIGH

I |EQ |SM00|

I |EQ |VM00|

I |EQ |KM00|

Internal Table ta_blart

Row SIGN OPTION LOW HIGH

1 I EQ 1A

2 I EQ 1B

3 I EQ 1C

4 I EQ 1D

5 I EQ 1E

6 I EQ 1F

7 I EQ 2A

8 I EQ 2B

9 I EQ 2C

10 I EQ 2D

11 I EQ 2E

12 I EQ 2F

13 I EQ 2G

14 I EQ 2H

15 I EQ 2I

16 I EQ 2J

17 I EQ 2K

18 I EQ 2L

19 I EQ 2M

20 I EQ 2N

21 I EQ 2O

22 I EQ 3A

23 I EQ 3B

24 I EQ 3C

25 I EQ 3D

26 I EQ 3E

27 I EQ 3F

28 I EQ 3G

29 I EQ 3H

30 I EQ 3S

31 I EQ 4A

32 I EQ 4B

33 I EQ A1

34 I EQ AA

35 I EQ AC

36 I EQ AF

37 I EQ AI

38 I EQ AN

39 I EQ DA

40 I EQ DD

41 I EQ DF

42 I EQ DG

43 I EQ DK

44 I EQ DM

45 I EQ DN

46 I EQ DP

47 I EQ DR

48 I EQ DS

49 I EQ EU

50 I EQ EX

51 I EQ GR

52 I EQ I1

53 I EQ I2

54 I EQ IN

55 I EQ IT

56 I EQ KA

57 I EQ KG

58 I EQ KN

59 I EQ KP

60 I EQ KZ

61 I EQ LD

62 I EQ LK

63 I EQ LN

64 I EQ LP

65 I EQ LR

66 I EQ LS

67 I EQ LT

68 I EQ LZ

69 I EQ M0

70 I EQ M1

71 I EQ M2

72 I EQ M3

73 I EQ M4

74 I EQ M5

75 I EQ M6

76 I EQ M7

77 I EQ M8

78 I EQ M9

79 I EQ MD

80 I EQ ML

81 I EQ OM

82 I EQ OR

83 I EQ P1

84 I EQ P2

85 I EQ P3

86 I EQ P4

87 I EQ PA

88 I EQ PR

89 I EQ RA

90 I EQ RE

91 I EQ RN

92 I EQ RV

93 I EQ SA

94 I EQ SB

95 I EQ SK

96 I EQ SU

97 I EQ TA

98 I EQ TB

99 I EQ UE

100 I EQ V1

101 I EQ V2

102 I EQ VI

103 I EQ VP

104 I EQ W0

105 I EQ W1

106 I EQ W2

107 I EQ W3

108 I EQ W4

109 I EQ W5

110 I EQ W6

111 I EQ W7

112 I EQ W8

113 I EQ W9

114 I EQ WA

115 I EQ WD

116 I EQ WE

117 I EQ WI

118 I EQ WL

119 I EQ WN

120 I EQ XA

121 I EQ XB

122 I EQ XC

123 I EQ XD

124 I EQ XE

125 I EQ XF

126 I EQ XG

127 I EQ XH

128 I EQ XI

129 I EQ XJ

130 I EQ XK

131 I EQ XL

132 I EQ XM

133 I EQ XN

134 I EQ XO

135 I EQ YA

136 I EQ YC

137 I EQ YD

138 I EQ YE

139 I EQ YF

140 I EQ YH

141 I EQ YS

142 I EQ ZA

143 I EQ ZB

144 I EQ ZC

145 I EQ ZD

146 I EQ ZE

147 I EQ ZF

148 I EQ ZP

149 I EQ ZR

150 I EQ ZS

151 I EQ ZV

Internal Table ta_hkont

Row SIGN OPTION LOW HIGH

1 I EQ 191208

2 I EQ 191418

3 I EQ 191518

4 I EQ 8191208

[Former Member]

Hi Gabtia,

You are basically selecting data from two tables(BSAD & BKPF) which are having huge amount of data. INNER JOIN is a good technique, but if the data is more then your internal table can't hold all at once unless you use 'PACKAGE SIZE' in the SELECT statement. The program may go to shortdump too.

Package Size can be used if you for example only want to finish processing a limited amount of data at a time due to lack of memory.

So Let me give you a small SELECT statement which uses 'PAKAGE SIZE'...

<b>SELECT <field1> <field2 ><...>

FROM <table1>

INTO TABLE <itab> PACKAGE SIZE 50

WHERE <condi1> <...> .</b>

<i>Please contact for more concerns...</i>

<i><b>Best Regards

Ali S</b></i>

[Former Member]

normally when ur selecting from BSAD or BSID or BSEG, it is advisable to write

select....

endselect.

So, in ur requirement...

select from bkpf.......

select from bsid/bsad ...........

Do the logic of moving etc....

endselect.

endselect.

[Former Member]

Hi Gabtia,

You are basically selecting data from two tables(BSAD & BKPF) which are having huge amount of data. INNER JOIN is a good technique, but if the data is more then your internal table can't hold all at once unless you use 'PACKAGE SIZE' in the SELECT statement. The program may go to shortdump too.

Package Size can be used if you for example only want to finish processing a limited amount of data at a time due to lack of memory.

So Let me give you a small SELECT statement which uses 'PAKAGE SIZE'...

<b>SELECT <field1> <field2 ><...>

FROM <table1>

INTO TABLE <itab> PACKAGE SIZE 50

WHERE <condi1> <...> .</b>

<i>Please contact for more concerns...</i>

<i><b>Best Regards

Ali S</b></i>

[Former Member]

Complex WHERE clauses can confuse the database so that it can't use the proper index. What I would do is remove all of the conditions in the WHERE clause except for BUKRS and HKONT. This should force the use of the primary key. Then remove unwanted data outside of the select. You may have to select more fields in order to do this, but that won't be a problem.

Rob

[Former Member]

1. you said you use secondary index, which one ? and which fields are included ?

2. sometimes it really helps if you remove OR condition... I mean sometimes 2 selects works faster than 1 select with OR. So, try to check if it works faster if you select with bsad_{augdt = '00000000' first, then with bsad}augdt > pa_date.

WHERE bsad~mandt = sy-mandt AND

bsad~bukrs IN so_bukrs AND

( bsad~augdt = '00000000' OR

bsad~augdt > pa_date )

3. How many recorsd with bkpf~stblg <> '' do you have ? if most of your records are with STBLG = '' .... then no need in this join at all, you can select from bkpf later and filter rare records with stblg <> ''.

4. Depending on what do you need all your records for - you may select using package size as it was suggested before, or you my just split your seelction/processign based on BLART, like - split your list of BLARTs and process several times. If you experience problems with insufficient memory as your internal table is too big - you'll need to use some sort of splitting either package-size or splitting by blart OR you may need to use field groups instead of internal tables if it's absolutely required to process all these records at once as field groups are stored in a file system rahter than SAP memory - see help (for example)http://help.sap.com/saphelp_erp2005vp/helpdata/en/9f/db9ede35c111d1829f0000e829fbfe/frameset.htm

[Former Member]

Thanks,

I think your answer is very helpful. I will try to split select statement into two parts and determine number of records for every where clause line.

I don't have any experience working with field groups and packages. I`will try.

Also I think maybe our SAP system have a little problem with ORACLE db administration. 3 mln records is not a big amount for DB. Select statements with BSAD works slower and slower. Have our ever seen such problems with SAP tables? How to resolve them if my select statement will not work faster?

[Former Member]

the table grows and inefficient select statements are going to run slower and slower and eventually - fail due to either too much data selected or due to a timeout.

If your program fails with timeout - you need to improve selection logic, if your program fails due to insufficient memory (like "NO_MORE_PAGING") - you either need to redesign your logic to process data in packages OR if you need to calculate some totals including past periods for example and the posting to these old periods is not allowed -> you can write a program in such a way that it will calculate totals once per period (after it's closed) and save these totals in your Z* table, then you can use these totals from closed periods + re-calculate data from current period only ... it will reduce the amount of data to be processed as well.

But again - all depends on what you really need from your program 🐵

[Former Member]

Hi,

1. My coworkers suggested me to use table ZTFISUPPLD with data from BSID, BSAD, BKPF.

2. Also, a decided to simplify select: to use blart IN ('KR', 'BR'...) and so on in place of blart in it_blart, there it_blart is selection option.

3. Removed some NOT words from where clause.

4. Used aggregate function SUM.

5. Created background job for my report. (Users generate my report once per month and selects last month data, so it will be nice to get generated file and not to wait until processing stops after some hours. Very bad, that I didn't know about circumstances how users will use my report earlier. )

Now my select statement looks like this:

CONCATENATE 'ztfisuppld~mandt = sy-mandt AND'

'ztfisuppld~bukrs IN' u_bukrs_st 'AND'

'ztfisuppld~hkont IN' u_hkont_st 'AND'

'ztfisuppld~blart IN' u_blart_st 'AND'

`ztfisuppld~shkzg = 'S' AND`

'ztfisuppld~manst IN' u_manst_st 'AND'

'ztfisuppld~budat <= u_date AND'

'ztfisuppld~NETDT < u_date AND'

`( ztfisuppld~augdt = '00000000' OR`

'ztfisuppld~augdt > u_date ) AND'

`ztfisuppld~stblg = ''`

INTO l_where_clause SEPARATED BY space.

SELECT SUM( ztfisuppld~dmbtr )

INTO c_sum

FROM ztfisuppld

WHERE (l_where_clause).

If those changes will not help me, I will split select into 2 parts at OR word.

Is it very bad to use commit work, if I get timeout? What is better to use SUM in select or sum records in internal table? Is it requares more memory space in ORACLE side?

Siarhei, I think packages will not help me, if I use SUM function. The problem is not in internal table. Internal table is big enough to process such number of records (~30 000). I think the problem is at ORACLE side: too little memory space avaible for my select statement to process. How to write select statement in order to get more space?

Posting to old periods is allowed, so I can't calculate sums once per period.

I need to make my program work faster. Also it will be better, to make other programs (including standard SAP programs as se11, fbl5n), which use BSAD, BKPF, BSID work faster. I can a little beet redesign my own program to work faster, but how to make other programs work faster too?

[Former Member]

Run ST05 (SQL Trace), enable trace-> run your program in a separate session on the same applicaiton server, disable trace, list trace. You'll see if your select uses any indexes or not.

I doubt the problem is with space on DB server 🐵 DB server is able to handle really complex requests, it just takes time.

You can ask your DBA forls to see the load on DB side when your program is being executed to see if any adjustments on DB side can be done...it may be beneficial for all progrmas which use these tables. But I would really focus on your program design to ensure you made everything possible to improve performance.

NOTE: the servers on produciton system are much more powerful usually than on your development system.

BTW, not sure why you put MANDT in WHERE clause.