Performance Testing

dataservices job design
1

Hi,

I have tried to load 3Gb of data and below is the log message

An extract from the DI log below:
(11.7) 10-19-09 20:24:57 (2996:3180) DATAFLOW: Cache statistics for data flow <TR_DIR_FACT&#8203;> are not available to be used for optimization and need to be collected before they can be used.
(11.7) 10-19-09 20:24:57 (2996:3180) DATAFLOW: Data flow <TR_DIR_FACT&#8203;> using PAGEABLE Cache with <1522 MB> buffer pool.
(11.7) 10-20-09 08:26:48 (2996:3180) DATAFLOW: Data flow <TR_DIR_FACT&#8203;> is completed successfully.

This DATAFLOW took almost 12 hours to complete with about 2Gb of data for DIR datapoint.

Can anyone please help me how to fine tune the above dataflow?

Regards,
Vebu.

vebu2005 :uk: (BOB member since 2006-05-11)

2

Hmm…

Well the information that you provided hardly helps us to answer your question.

There may be number of bottleneck, which can impact performance.

How many tables are you joining at the source, what is Source/Target DB.

Is SQL being pushed down to the Source DB ?

If its a simple load, why not use API bulk load?

In one of my design with just one source and 2 look_up function around 6 million rows, took around 4 minutes to load using API bulk load method on Oracle DB.

There are many post in the forum, that can help you with optimization technique !

data_guy :us: (BOB member since 2006-08-19)

3

Hi,

Many Thanks for your quick replay.

I will see the various questions and let me try from my side.

Regards,
Vebu.

vebu2005 :uk: (BOB member since 2006-05-11)

4

This is what we will do with your dataflow to analyze it:
http://wiki.sdn.sap.com/wiki/display/BOBJ/Identify+a+Bottleneck+in+a+Dataflow

Just a quick question:

  • do you use lookup_ext() in row-by-row mode?
  • do you have a Table Comparison in row-by-row?

Nothing wrong with that, but the database needs an index to support that.

Werner Daehn :de: (BOB member since 2004-12-17)

5

Hi,

Yes, Around some 7 to 8 lookup_ext with Pre_Load_Cache has present and apart from that an Push down SQL query and some decode function are present.

Is there any way to fine tune the lookup_ext function ? I guess Pre_Load_Cache is the optimized one,but of curisoity i am asking…

Regards,
Vebu.

vebu2005 :uk: (BOB member since 2006-05-11)

6

Yes they were around 8 to 10 indexes present in the target table.

vebu2005 :uk: (BOB member since 2006-05-11)

7

Nothing obvious then. We would need to go through above exercise. So post a screenshot of the dataflow, open the monitor log in notepad and post the first 40 lines, 40 lines from the middle and the last 40 lines here please.

Werner Daehn :de: (BOB member since 2004-12-17)