Why are transaction-based workflows sooooo slow?

[dave-csc]

Hello,

I'm experimenting with the Make this workflow/pipeline transactional flag in workflows and pipelines.

However, I noticed that the same workflow/pipeline can take much more time to execute if this flag is enabled. I mean, VERY MUCH more time: up to 60 times compared to an execution without transaction control!

I would like to know if there is any strategy to get a better performance: this flag is very useful to keep a database in a consistent state if the workflow fails, but it can't be acceptable to make it run for 3 hours rather than 3 minutes...

Any suggestion is welcome, thanks for your support :)

[hansva]

I will have to throw the question back at you...
What is making it slow? which transforms are impacted? I fear your table outputs and database operation will be the issue.
And that is a database problem and not a Hop problem. The only thing we do is, we don't commit until everything is done.

[dave-csc]

Hello @hansva,

I experienced this even with probably the simplest pipeline, that just inputs (Table input) from an Oracle Database and outputs (Table output) on a PostgreSQL one on AWS, and nothing else.

Without transactions, last execution of this pipeline took about 54 seconds to truncate the output table and copy 186660 records.
When testing with transactions, the same pipeline had taken over 40 minutes (and I had to undeploy it...).

Replying to dma***@***.com (via mailing list): unfortunately I don't always have a "primary key" available. But even when there is and use it in transforms like Database lookup or Insert / Update, execution times dilate when using transactions...

[bamaer]

would this be something you could add to a ticket with a reproduction path setup in e.g. docker compose? we can do this ourselves, but you know where the problems are, and it would save us time that we can spend on the actual problem investigation.

[dave-csc]

I try to set up a use case as similar as possible to my experience (but i don't use Docker so there will be some differences), thanks