Skip to end of metadataGo to start of metadata

Although the Key Generation Transform actually does execute a SQL I put it into the Streamline category because it executes one and only one SQL at the start of the transform and this is a 'select max(key) from table'. In almost all cases this key will be the primary key column which is indexed. So to find a max() the database has to walk to the last element in the key which is an operation done in no-time. Based on this starting value, the transform increases the value by one for each row with OP code Insert or Normal and puts the value into the 'Generated Key Column'.

Throughput

237'000 rows/sec

CPU

100%

Memory

none

Disk

none

DoP

singularization point

Transform Usage


Key Generation Transformation is used to generate artificial keys for new rows in a table. The transform looks up the maximum existing key value of the surrogate key column from the table and uses it as the starting value to generate new keys for new rows in the input dataset.

Transform Settings


Use Of Key Generation Transform In Bods 2

To test it, we had to rename the column from DI_ROW_ID to KEY_ID which is the primary key name of another table.

Use of key generation transform in bods 2

Use Of Key Generation Transform In Bods 1

The execution time for above flow without the key generation was 84 secs, so the overhead for the query is about 20 secs. The execution time with Key Generation Transform added as well was 422 seconds which is quite surprising.

Anyway, no memory is consumed, only CPU and the throughput is still at excellent 237'000 rows per second.


Attachment
Job_Key_Generation_Transform.zip (4.98 KB)

Key Generation Transform In Bods


Key Generation In Bods


Coments are closed
Scroll to top