Impact of Changing Data Package Size with DTP

[Former Member]

Hi All,

We have delta dtp to load data from DSO to infocube. Default data package size with dtp is 50,000 records.

Due to huge no of data, internal table memory space is used and data loading get fails.

Then we changed the data package size to 10,000, which executes the data load successfully.

DTP with package size of 50,000 took 40 minutes to execute and failed, but DTP with package size of 10,000 took 15 minutes (for same amount of data).

Please find below my questions:

Why a DTP with bigger size of packet runs longer than a DTP with lower packet size ?

Also by reducing the standard data package size 50,000 to 10,000, will it impact any other data loading?

Thanks

[Former Member]

Follow this post. It might help.

[Former Member]

if you go to request monitor of your dtp and check detail tab, you will find the time taken for each activity like, filter, routine, write to fact table etc. you can analyse from those details as to which step took more time.

[Former Member]

Hi Sri,

If your DTP is taking more time then check your transformation .

1.Transformation with Routines always take more time so you if you want to reduce the time of execution then routine should be optimized for good performance .

2.Also check if you have filter at DTP level .Due to filters DTP takes long time .If same data get filtered at routine level it take much lesser time .

3.If you cannot change routine then you can set semantic keys at your DTP .The package data will be sorted as per semantic keys and thus it may be helpful at routine level for fast processing.

4.Your routine is getting failed due to internal table memory space so check if you have select statement in routine without FOR ALL ENTRIES IN RESULT_PACKAGE or SOURCE_PACKAGE line .if you will use this It will reduce record count .

5.Wherever possible delete duplicate records and if possible filter useless data at start routine itself .

6.Refresh internal table if data no longer needed .If your tables are global then data will be present at every routine level so refreshing will help to reduce size.

7.The maximum memory that can be occupied by an internal table (including its internal administration) is 2 gigabytes. A more realistic figure is up to 500 megabytes.

8.Also check no of jobs running that time .May be you have lots of jobs active at the same time so memory availability will be less and DTP may get failed .

***

Why a DTP with bigger size of packet runs longer than a DTP with lower packet size ?

*Start and end routine works at package level so routine run for each package one by one .By default package have sorted data based on keys (non unique keys (characteristics )of source or target) and by setting semantic keys you can change this order.So Package having more data will take more time in processing then package have lesser data .

by reducing the standard data package size 50,000 to 10,000, will it impact any other data loading?

It will only impact running of that load .but yes if lots of other loads are running simultaneously then server can allocate more space to them .So better before reducing package size just check whether it is helpful in routine performance (start and end ) or increasing overhead .

Hope these points will be helpful .

Regards,

Jaya Tiwari

[Former Member]

hi sriarunprian,

Obviously, having a bigger data package will reduce the number of package but processing of each of the package will take longer. Changing the data package size in DTP will only affect that specific dtp but not any other, if you load other providers, you will still see 50K. If you change the data package size globally, then that would affect other providers.

thanks.

Wond

[former_member184494]

The DTP package size should be set depending on the type of load - if you have a lot of lookups - then a smaller data packet sixe might be faster...

Lets say you have a select statement which goes like this .

Select * from /bi0/pmaterial nto table itab for all entres in data_package where data_package-material = material.

The larger your data packet - the longer this will take and the larger your itab will be...

I would suggest that you keep the default fily high ( 50,000 is kind of a nice number ) and then reduce / increase the same depending on the data load and this would be a trial and error approach and with some possible science thrown into it for analzng the code!!