I am investigating performance problems on a target Postgres database. Most of the dml is UPDATE statements; updating a few select columns. As a constraint of the business requirements; we have to have the task in Transaction apply mode.
With logging turned up; I ran the following on the source:
UPDATE dbo.my_table
SET field_1 = 10
WHERE
key_1 = 'A' AND
key_2 = 'B' AND
key_3 = 'C';
In the qlik replicate logs; the update came through as:
UPDATE dbo.my_dest_table
SET key_1 = ?,
key_2 = ?,
key_3 = ?,
field_1 = ?,
field_2 = ?,
field_3 = ?
WHERE
key_1 = ? AND
key_2 = ? AND
key_3 = ?;
This means that qlik is updating columns that it doesn't need to be; and more importantly it is updating the keys. I'm not sure if the Postgres compiler is smart enough to work to know that no action is required; or it will try modifying the index on the keys; adding to performance overhead.
Is there a way to make qlik replicate update only columns that are necessary? i.e.
UPDATE dbo.my_dest_table
SET field_1 = ?
WHERE
key_1 = ? AND
key_2 = ? AND
key_3 = ?;
