Tuning of data load on example of BW Statistics - Episode 2 - Further Analysis

[Vitaliy-R]

[2007-02-02]
As I mentioned at the end of my Tuning of data load on example of BW Statistics - Episode 1 - Analysis in the series, as a first step to play (and to learn) I had changed "Maximum size of a data packet in kByte" from 20 000(on screenshot below) to 30 000 (default for this Source System).

This changed number of records processed in one package from 34843 to 50000 (default max number of records), but did not help with execution time - it even grew from 3h 5m (246 135 recs in 8 packages) to 3h 45m (257 219 recs in 6 packages).

Interesting, although the number of processed packages has reduced from 8 packages to 6 packages, the overall update time increased... Sorry, no smart explanation from my side. Btw, this InfoPackage for 0BWTC_C02 is executing alone; no other InfoPackages for BW Statistics are executed in parallel (of course, there might be some other activities happening in the system).

But there is another interesting question that aroused - how package size is calculated? The max package size was 20000kB. I checked in the PSA table that the length of the structure is 576B (I assume key fields, like Request Nr, Package Nr and Record Nr are added after extraction, so not taken not taken into calculation)

Accordingly to my calculations, package size in records should be equal 20000kB * 1024 / 576B = 35555 records. But as mentioned above it is 34843; unless there are still some technical fields, like Record Nr, exist in internal transfer structure.

[2007-02-03]
Next step for me was to check number of records already existing in the cubes. Here are sizes of cubes' fact tables:
C02 - 34 469 689 records
C03 - 111 588 664 records

Probably I should do some old data deletion; cube contains all data since initialization on 21.11.2002... And data compression... But these are separate tasks, not related to the investigation of the original root cause, I think.

Let's check indexes then

Indexes seem to be ok, but Statistics might be outdated. Let's refresh them then.

It took 1h 30min to refresh the cube's Statistics. Now they are green on Performance tab. Will see tomorrow if this has any impact on the time of processing.

[2007-02-04]
Previous activity - refresh of cube's Statistics - did not improve update time at all. Nice try.

It took 4h 25m to load 313 117 records in 7 packages today.

So as next step to play I check "Delete InfoCube indexes before each data load and then refresh" now and will check next Sunday how this will affect update time.