Skip to Content
[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).

InfoPackage settings BEFORE the parameter's change to 30000
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)
image
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
image
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.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Anonymous
    Vitaliy, nice job documenting your thought process. You also might want to consider turning on number range buffering, specifically for the dimensions that have Stat ID, Session ID, Navigation ID, etc. Number range buffering might be able to speed up your loads.
    (0) 
    1. Jay Roble
      Number Range buffering Solved our slow loads for >DS 0TCT_DS02 >IS 0TCT_IS02 >Cube 0TCT_C02

      PROBLEM: Running Slow 20 min per Dpack, Doing a commint after each number range insert & SID insert & reading through 3GB of Log Buffer.

      SOLUTION:
      We changed the buffering on these 3 master Data objects number table from none to 100.
      – 0TCTSTEPUID – BIM0019332
      – 0TCTTIMSTMP – BIM0008361
      – 0TCTSESUID – BIM0008382

      Reason:
      When you think of it, there is no master data you can preload for Timestamp & almos every timestamp is unique, so it needs to look up a SID for every record.

      Jay

      (0) 
    2. Jay Roble
      (0) 
  2. A B
    Hi Vitaliy,

    With my experience, I have always noticed that it takes longer time for larger data packets as compared to smaller ones. The reason being every data packet is stored in temp tables as well.

    Smaller data packets are always quicker than big ones and deleting/creating indexes is a good option to do for loading data into cubes! it helps avoid DB errors.

    Thanks hope it helps!

    (0) 
    1. Witalij Rudnicki Post author
      Thank you for your comment. I should agree with you. Especially that if we run load in 3 packages at one time, then required temp space is increasing by 3 times delta.
      Regards,
      Vitaliy
      (0) 

Leave a Reply