Database Delete working too slow

Dear All, please help me to improve my workflow. I created a loop for different countries. It pull out some data and write it inside the database. As loops continue to the next cycle it must empty the database. I use database delete to erase the data. However it takes several hours for the node to clear the data. I don’t understand, why it takes so long?
Will appreciate your help.
Thank you.

Hi @DeMytrych -

Could you try increasing the batch size in the Advanced tab of the Database Delete node from 1 to 100, or even 1000?

Scott, hi. Thank you for your reply. Actually I’m changing this parameter for already several days, trying to make it work faster however it still takes a few hours. However there were several cases where it worked very fast. I cannot figure out what influences the speed? I was trying to set batches to 2, then 2000 then 500000 and 1000000.

Perhaps it would be faster to drop and recreate the table, until you figure out why the delete is taking so long?

1 Like

Hi there!

What database are you using? Do you have many Where columns?

Check this out as there is lot of reasons why DELETE statement can be slow: https://stackoverflow.com/questions/10901299/delete-statement-in-sql-is-very-slow

I was using Update node and when I had double column type in where cause it was awfully slow. Converting it to int solved my problem. And I believe I was adding indices where they were missing…

Br,
Ivan

1 Like

Thank you!