Table Comparison - Isolating the bottleneck

[Former Member]

Hi,

Data Services: 14.2.4.760

Repository: DB2 9.5

Staging database: DB2 9.5

Were having performance issue with one of our data flow with change data capture.

1. Historical table (DB2) is being loaded into a persistent cache table

2. We extract same set of data from BW and load it to a staging table (DB2)

3. Then we compare the data from the staging table against the persistent cache

After migrating to a new server there seemed to be a performance degradation on this specific data flow. How will I be able to know how fast DS read data and perform comparison? DS application and data baseare on a separate server, historical table has index.

Before

After

PS: Dirk Venken

[former_member187605]

Which Comparion method () are you using?

[Former Member]

Hi Mr. Dirk,

Thanks, I run through those documents earlier. Were are using sorted input. I'll append other information which may be helpful. Hope you could help us out. Thanks!

[former_member187605]

At first sight, the sort takes twice as much time as before. How do your original and new servers compare?

Can't you push down the sort operation to the database?

[Former Member]

Hi Mr. Dirk,

Yes I did try pushing it down to the database level but the TCRdr is what is taking longer. The throughput is too low. Specifications of our new servers are much higher (CPU, memory) than the previous so I needed to determine which is really causing the performance issue (could it be database parameters/configuration etc). In reference to your response to other thread, can you share how I will be able to gauge below:

There's no theoretical limit. In your case, total elapsed time will depend on 3 parameters:

1/. how fast can your database sort 10 million rows?

2/. how fast can DS read out 10 + 20 million rows?

3/. the number of comparison columns

For now we'd opted to performing optimization since the same process is working fine in the current production server. Optimization is a last resort but I am already doing it, in hopes to find some clues. (By the way, thanks for your articles )

Thank you in advance. It is much appreciated.

[former_member187605]

Your case is different, because the sort is done in DS memory and not pushed to the underlying database.

If you change the DOP (default = 2) setting in the data flow properties, you'd see a change in elapsed time. Increase it to 4, and your flow might run almost twice as fast.

Obviously, that does not explain the difference between both servers.