question Re: Optimization nifi delete in Support Questions

Optimization nifi delete

LejlaKM — Tue, 08 Feb 2022 10:49:37 GMT

Hello,

I have one big table in one database that I have to sink on destination.

Firs I have flow with incremental insert and it works fine. Than in other flow I have incremental delete by ID column.

I am trying to delete 3179958 records from table. I have one table on source and I have to do increment delete on destination. This is my flow.

I am sending every processor separate:

When I have much less records it all works fine. But now flow can't finished... Is there any other solution.

Thanks in advance

araujo — Tue, 08 Feb 2022 21:21:20 GMT

What's the underlying database? Could you please share the full configuration of the PutDatabaseRecord processor?

LejlaKM — Wed, 09 Feb 2022 07:10:14 GMT

Hello,

it is oracle database

araujo — Wed, 09 Feb 2022 08:57:22 GMT

Can you please provide the full configuration of PutDatabaseRecord?

araujo — Wed, 09 Feb 2022 10:46:05 GMT

Here's some guidelines that should help with performance:

On the database side, ensure that both tables (source and target) have a primary key enforced by an index to guarantee uniqueness and delete performance
GenerateTableFetch
- Columns to Return: specify only the primary key column (if the primary key is a composite key, specify the list of key columns separated by commas)
- Maximum-value Columns: If the primary key increases monotonically, list the primary key column in this property too. If not, you should look for an alternative column that increases monotonically to specify here. Without this performance can be terrible.
- Column for Value Partitioning: same as for "Maximum-value Columns"

Please let me know if this helps.

Regards,

André

LejlaKM — Wed, 09 Feb 2022 16:06:28 GMT

Hi,

thank you very very much...

Delete for 3179958 records works now 6 minutes