tTeradataSCD component performance is slow

Problem Description

While using tTeradataSCD components in a Job, the number of records being processed is very low (five to seven records per second) compared to tTeradataOutput components.

 

Solution (best practices)

In comparison to a single insert or update, an SCD Type 2 operation comparison is more "expensive" in terms of time required.

 

Inserts are generally fast no matter the database size, but updates are not fast. Update operations can slow down significantly as a database grows. Specifically, as the number of rows of the table being updated grows, especially when the database is not optimized (for example, it is not indexed properly, doesn't have enough RAM, no statistics are being run regularly, and so on.)

 

When the test is executed, running 1k update statements against a table of 100k, 1M, and 20M records to see how much the update slows down, you will noticed that running database operations in parallel on a hardware-constrained machine can really slow things down. The t<DB>Output components allow commitEvery() and useBatchSizeOf() settings. That type of functionality doesn't exist for the SCD components. Instead, there are Use memory saving mode and Debug options on the SCD component, which can help you to improve the performance.

 

You can also disable the Debug mode of tracing to capture while executing the Job, as it needs to write more logging for the Job and for each row being processed.

Version history
Revision #:
3 of 3
Last update:
‎09-29-2018 12:17 AM
Updated by: