Question Re: Approach on loading multiple data sources sequentially within one job in Technology Q&A

Approach on loading multiple data sources sequentially within one job

Former Member — Wed, 03 Feb 2016 22:05:59 GMT

Hi there,

I'm working on a job which need to load four datasource by order and any one of them failed, the job should still continually go forward to the end.

In order to do that, I use a series of workflows which connected sequentially. Within each of workflow, there is one dataflow to load one data source to the target table. The target table is composed by those four datasource, so, for each of dataflow, I have to use Table_Comparison to make sure the fields are filled based on the primary key. For this approach, it satisfies on those requirements, however, for each of workflow, they are processing for a while. The whole chain takes too much time. Does anyone have idea on how to improve on the performance or any other strategy to handle this case? Thanks a lot!

The screenshot for each of the layer as below

WF chain:

Within each of WF,

Within each of DF

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Thu, 04 Feb 2016 03:45:15 GMT

Hi Cindy,

Please refer the below document to optimize BODS job

Thanks,

Surya B.

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Thu, 04 Feb 2016 08:27:57 GMT

Can you let me know what is the count of the Target table for all the data sources. If Amount of data is not too huge then try using 'Cached Comparison Table ' in Table Comparison.

Performance is okay as long as the amount of data is not too huge.

Regards

Arun Sasi

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Thu, 04 Feb 2016 14:22:43 GMT

You can also try to use sorted input in TC (remember to sort the data before the TC )

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Thu, 04 Feb 2016 19:29:43 GMT

Hi Allan,

Thanks for reply.Already applied sorted. But it doesn't help:(

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Thu, 04 Feb 2016 19:32:24 GMT

Hi Arun,

Thanks for the response. The records is more than 300K. I understand 'Cashed Comparson Table' is supposed to be used in a dataset much less than that. Is that right?

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Fri, 05 Feb 2016 09:56:52 GMT

Yeah Cindy!!

Better not to use Cached table comparison method in this case.. Do you have any lookups in your job. Also try creating indexes on the target table if possible. If there are more updates than inserts then Indexes play a very important role in performance tuning.

Regards

Arun Sasi

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Wed, 10 Feb 2016 15:28:18 GMT

1. make 2 separate flows, the first flow is Source table >Query> target table, and the second is with TC. By doing so the first flow will be pushed down to DB, thus no Job server is involved in calculations.

2. How do you to optimize TC transform? This can not be pushed down to the DB, thus local resources are used. It depends on Source and target table, if both are properly indexed according to your Comparison options, then performance should be fine. The best bet here would be to avoid TC transform and simpy use script instead, which compares your 2 tables, seeing that there is no SCD2 needed, then this is very easy to maintain.

Custom scripts are always faster and better than predefined transformations.

Re: Approach on loading multiple data sources sequentially within one job

former_member187605 — Wed, 10 Feb 2016 16:43:37 GMT

Check for a discussion on optimal settings for Table_Comparison.

Re: Approach on loading multiple data sources sequentially within one job

former_member187605 — Wed, 10 Feb 2016 16:45:26 GMT

This is definitely not true! I have literally hundreds of examples with transforms largely outperforming any script.

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Wed, 10 Feb 2016 18:44:10 GMT

Hi Dirk,

Actually I have already checked the doc you mentioned above. I ordered the source based on Primary key and then chose the sorted input in T_C. However, it did not help. My data set is not as huge as 1million. In fact, it's just 300000 roughly. I have no idea why the T_C took so long.

Thanks for your help!

Cindy

Re: Approach on loading multiple data sources sequentially within one job

former_member187605 — Wed, 10 Feb 2016 20:41:28 GMT

I am not convinced the TC is the culprit.

Is the sort pushed down to the source? If not, is is performed by DS, which may be time-consuming.
How much faster is the dataflow without the TC? The extraction process might be the bottleneck.

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Wed, 10 Feb 2016 20:43:39 GMT

it depends on who did the scripting, right ? BODS is good in general, but when it comes to more compex stuff, then as you well might know, we might hit the limitations of graphical tools - BODS, SSIS, Informatica..

Re: Approach on loading multiple data sources sequentially within one job

former_member187605 — Wed, 10 Feb 2016 20:52:54 GMT

IMHO, it rather depends on who does the DS design .

DS is not a graphical tool at all, only DS Designer is. At runtime, "real" code is generated and executed. DS has been optimised for generating very efficient code, not always so easy to produce manually.

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Wed, 10 Feb 2016 22:36:15 GMT

Hi Dirk,

How the sort pushed down to the source? I check the source by "View data" and found that the source data already sorted by one of PK. They way I implemented is Query-> Order by -> two PKs.And in the T_C, I put the two PKs on the left, and other fields on the right as the compare columns . Is this the correct way?

Re: Approach on loading multiple data sources sequentially within one job

former_member187605 — Thu, 11 Feb 2016 09:40:59 GMT

That seems like the right approach indeed. In fact, that's how it always works for me with larger data volumes.

You mentioned your input is 300K records. Could you post some extra absolute figures?

execution time of every data flow
time it takes to load the first set of data without Table_Comparison

Also, can you remove the temp table from your data flow? That might be another bottleneck.

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Thu, 11 Feb 2016 16:32:24 GMT

Hi Dirk,

I tested the load without T_C. Apparently it went much faster than I used T_C. I also removed the temp table. With T_C, it did not help a lot.

I checked some docs, (actually a lot) many people mentioned index the fields, however, I haven't find more details on how to do it. Would you pl give me some hints on index? Thanks so much!

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Thu, 11 Feb 2016 16:53:59 GMT

I also attached one of the screenshot on the log, it has been running for more than one hour. The second WF is still processing. It's like running forever.

Re: Approach on loading multiple data sources sequentially within one job

former_member187605 — Fri, 12 Feb 2016 11:38:31 GMT

From my document on Table_Comparison:

Sorted Input:

Often the most efficient solution when dealing with large data sources, because DS reads the comparison table only once. This option can only be selected when it is guaranteed that the incoming data are sorted in exactly the same order as the primary key in the comparison table. In most cases incoming data must be pre-sorted, e.g. using a Query transform with an Order-by (that may be pushed down to the underlying database), to take advantage of this functionality.

From the log, it seems:

You don't have a primary key in the comparison table. Therefore DS has to sort it, first, caching all in memory, a very time-consuming process.
Your source is a content extractor, isn't it? And it doesn't produce the data in the correct order. Therefore DS has to perform another sort operation.

Stage your input data in a database table with the correct primary key (you can use the Data_Transfer transform to that extent) and make sure your comparsion table has the same primary key column(s). You're job will termiate in seconds.

Re: Approach on loading multiple data sources sequentially within one job

Former Member — Fri, 12 Feb 2016 20:38:04 GMT

Hi Dirk,

Really appreciated your help. The reason I did not put primary key in the conparison table is that one of data source include NULL value in one field of PK. So, I removed the PK in the target table, and use 'Identify key' as the PK. Now, I just took out that datasource and put the PK back on the comparison table.

In addition, I used a stage table to sort the datasource and after that, use the sorted stage table do T_C. However, I got error message on it. It seems like 'Order by' does not work during the first DF. The stage table is still not sorted. Please look at the below .In order to solve this error, I have to put another Query in front of T_C ( Order by one more time). The error has gone. But the no improvement on the performance compare with the old solution. Would you know where I did wrong? Thanks again!