EF-Core: Save one by one or bulk?
this code is part of a method that gets called approximately 1000 times, additionally this code is in a loop that runs approximately 10 times. Meaning this code gets executed 10000 times in one round (approximately). A lot of this will be executed in parallel (all Tasks returned by this method are in a Task.WhenAll(...)).
Now I feel like CPU-Usage is too high sometimes and the issue is that sometimes the Database (postgres) even times out due to a lot of concurrency.
To solve timeouts I made a custom context (threadsafe context) that has locks on all operations (savechanges etc.) to prevent concurrency on Database level (even though postgres supports it but times out if I use too much concurrency, so I may lose performance here already). Right now all of these method calls use the same context, i am not creating a new context for each method call
I could also do the SaveChangesAsync after the Task.WhenAll, since the context is change tracking all the property changes (user.LastProcessed = ...), so a single save after all methods are done would work too. However I do not like this solution as it heavily relies on the fact that all methods DO exit at some point, if just one method call does never exit for whatever reason, then I lose data by not saving it.
How would you approach this?
1 Reply
I would never call SaveChanges inside the loop - that's going to be an absolute kill, unless this is just a non-time sensitive satellite process outside of your application - only then would it be worth considering. If this is being executed in application code and hitting those resouces and you can't do anything about that - I'd just be less pessimistic about failing and run this in a transaction with adequate logging, doing the save changes after. Better to have nothing than not knowing what you've got in your database.