DS 4.1 - Table comparison deletes incomplete?

[Former Member]

Hi

I've noticed something about the table comparison transform in DS 4.1 (v14.1.1.392) and was wondering if anyone has experienced similar behavior.

Lets say there are 1.000.000 rows in the source table and 1.000.000 in the target.

When comparing both tables, while only looking at inserts and deletes, there are:

• 500.000 rows that exist in the source, but not in the target (=inserts)
• 500.000 rows that exist in the target, but not in the source (= deletes)

General DF structure in DS:

• source table
• query transform with an order by on the key column
• table comparison
  • sorted input
  • detect deleted rows checked
• target table

When running the data flow, it completes without any errors but only a couple of hundred deletes seem to have been processed.

When running the data flow again, the same thing happens. Another couple of hundred rows are deleted.

This has happened in multiple DF's. Is this behavior intended?

I've tried changing the DF structure slightly by splitting the deletes/inserts using map operations and bringing them back together at the end of the flow with a merge transform. This doesn't seem to change a thing.

When writing the deletes to a template table, I see the same couple of hundred rows in the output.

When checking "Run as a separate process" in the table comparison transform, all deletes are processed in 1 run.

How big of an impact could it have on the job server if multiple DF's with table comparisons are run in parallel as separate processes?

Thanks

Dwight

[aasavaribhave · Accepted solution]

If TC is run as separate process - for each DF that has TC additional al_engine starts, this is not exactly bad - depends on system resources and you may need to test and run different number of DF in parallel to check what kind of load the system can handle well. Do you use multiple loaders?

Also, given this is very initial 14.1 - this could be one of the TC related issues fixed in DS 4.2 SP2 or DOP. Set the DOP to 1.  Based on your tests - is detect deleted rows - detecting all the rows that were supposed to be deleted? What is DF dop or Global DOP set to? Also consider an upgrade to DS.

[Former Member · Accepted solution]

Do you use multiple loaders?

No, number of loaders is set to 1 in the target table

Based on your tests - is detect deleted rows - detecting all the rows that were supposed to be deleted? 

Not as far as I can tell. When mapping deletes to inserts and writing them to a target table, I'm not getting all rows that are supposed to be deleted in the target table.

What is DF dop or Global DOP set to? 

The DoP is set to default.

Also, given this is very initial 14.1 - this could be one of the TC related issues fixed in DS 4.2 SP2 or DOP. ... Also consider an upgrade to DS.

This is definitely something we should look into. Other issues, like extremely slow Designer start up, might also be resolved by upgrading (and perhaps a central repo cleanup).