BW Fine Tuning on Netweaver BW 730 – Part 1
Standard DSO activation is always a performance bottleneck step among BW data loading steps. In this blog, I will provide how DSO activation be improved on BW730 new features.
First let go through DSO activation logic to understand DSO activation flow.
3 DSO tables are involved for DSO activation process.
Active Table => /BIC/A####00 (#### is DSO technical name, e.x. ZDSOTEST1)
Change Log Table =>/BIC/B<10 gen. digits>
Active Queue Table => /BIC/A####40 (#### is DSO technical name, e.x. ZDSOTEST1)
DSO activation job will use one batch job to fetch records from DSO activation queue table (/BIC/####40) package by package. The DataPackage size is depended on the specified DSO activation DataPacakge size defined in transaction RSODSO_SETTINGS. We can call this job as “main job”. Once the main job get records from /BIC/A####40, it will trigger several child jobs to write the records to active data table (/BIC/A####00) and change log table (/BIC/B<10 gen. digits>). We call such writing job as “child job”.
In BW701 DSO activation is suffering performance bottleneck with single record lookup from DSO active table. I use BW test environment for both BW701 and BW730 as below:
BW 701 vs BW730 test environment:
BW Test Environment
Support Package Level
BW 7.0 EhP1 SP08
Also used worst DSO activation scenario as below:
- Load 1 million records to DSO object as delta request, which have no overlap with previous delta init request.
- Activate the single 1 million record delta request in both BW701 and BW730 environment with same DSO activation settings.
a. DataPackage Size: 50,000
b. Parallel Degree: 5
c. Run Mode: Batch Mode
d. No SID generation during activation
e. No unique record
f. Total volume: 1 million records
(* sorry for my poor testing environment, there are only10 background processes available)
Here is improvement on DSO activation in BW730 framework with same 1 million records volume.
In general DSO activation job performance improved 43% in BW730 test environment.
DSO activation runtime reduced from 309 seconds to 176 seconds. The main difference is on each single child writing job runtime. Each single child job average runtime reduced from 49 seconds to 22 seconds.
ABAP trace of single child job is captured for both BW701 and BW730 running. First let see the famous mass single select in BW701 child job.
74% of child runtime is spent on 50000 single select lookup calling on DSO active table. Then let see how DSO activation child job works in BW730.
50,000 single select disappear!!! Now SAP developers replace 50000 single select with only one select lookup calling on DSO active table. The main change on BW730 DSO activation is that “select single”ABAP codes are replaced with “select….for all entries” as I captured in generated DSO program.
Above ABAP code change reduced DSO active table lookup runtime reduce from 37 seconds to 10 seconds. In sequence the whole DSO activation job runtime benefit from this major logic changes in BW730.
I was told in SAP SDN blog “Performance Improvements for DataStore Objects” that DSO activation job can get 10% ~ 30% improvement in BW730.
My small comparison is based on single DSO activation job scenario. With more DSO object activation job running in parallel on real SAP customer environments, the DSO activation performance would have certain decrease. But without any DSO setting changes, DSO activation should be able to decreases by 10-30% for sure.
However this is not the end of tuning story for BW730 DSO activation job. In “part 2” I will provide more setting change tips based on my additional DSO activation tests in BW730 environment.