Data Flow Long Run

[neilpadilla]

Hi Guys,

Good day!

I am currently having a performance issue regarding with extraction of a job.
My job extracts an average of 215,000 row count for every run weekly. The extraction takes up to 3 to 4 hours long compared to the usual duration of 2 hours. As per checking of the trace log, the long run is occurring in the Data Flow.

Data flow structure:
     SQL (Database Type Oracle 10g) -> Table (Microsoft SQL Server 2008)

The current configuration of the Source and Target in the designer is as follows:

SQL
     Database type: Oracle 10g
     Array fetch size: 1000

Table Target
     Database type: Microsoft SQL Server 2008
     Rows per Commit: 1000
     Number of loaders: 1

With this, I would like to ask on how can I optimize the performance of the job where I can reduce the extraction time.

Thanks,

[md_fasi]

Hi  Neil,

I suggest you to contact system administrator.Some times virtual memory occupied more memory than there will be performance issue.

I recommend analyse and  increase virtual memory size.

Regards

[neilpadilla]

Hi Rahman,

Thanks for your suggestion.

I have now contacted my system administrator for the memory usage.

[former_member186897]

Is this dataflow getting full pushdown? If not, then you can try making it full push down. This shall drastically improve the performance.

By the way, are you using any complex logic in this dataflow? How it is taking 2 hours? Share more details so that we can exactly tell you where is the problem.

[neilpadilla]

Hi Ansari,

Thanks for your response.

Sorry I am new with Data Services, I'm not sure if this is using a full push down or not.

This is how the structure of the dataflow:

In the [SQL] script, it gets data from 12 source tables. Target table has 27 columns.

[former_member189153]

Hi Neil,

Please refer the below guide

http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_perf_opt_en.pdf

[Former Member]

Hi,

Increase Rows per Commit  and   Number of loaders

Regards,

Manoj.