My program is dealing with a deep nested object. Here is an illustration of this nested model :
- ParentObject HasMany ChildObject1 ~ 30 records
- ChildObject1 HasMany ChildObject2 ~ 40 records
- ChildObject2 HasMany ChildObject3 ~ 15 records
- ChildObject2 HasMany ChildObject4 ~ 10 records
To have an efficient app, I have decided to split the forms used to record this data (1 form per childObject1). I also use caching and then needs to update ChildObject1 'updated_at' field everytime the ChildObject2,3,4 are updated. For this reason every childObject 'belongs_to' relation has the 'touch' option set to true.
Then, with a small server, performance are not so bad (max 1s to save data).
But once everything is recorded, I also need to duplicate the parentObject with all is childObjects. No problem to duplicate it and build the same parentObject but when I save the object, the transaction is very long.
I looked to the server log and I saw that objects are inserted one-by-one. I also saw that after each insert, the parent 'updated_at' field is updated (due to 'touch: true' option). It results in 30000 inserts more 60000 updates, 90000 writing queries in the database (and each object can have 3 to 6 fields...)!
Normally, 'save' method is natively using ActiveRecord::Base.transaction. Here it doesn't happened. I tried to remove the 'touch: true' option, it's exactly the same, inserts are done one-by-one.
So my questions are :
- I thought that transactions can be applied to nested object like explain here, Am I misunderstanding something ?
- Is it an example of what shouldn't be done through ActiveRecord ?
- Is it possible to only do one final update of parents object with 'touch:true' option ? (SOLVED : SEE ANSWER BELOW)
- Normally, is it a big work to write 90000 rows in database at once ? Maybe the puma server or the pg DB are simply bad configured ?
Thanks by advance for your help. If there's no solution, I will automate this work by night...
I solved a first part of the problem with https://github.com/godaddy/activerecord-delay_touching
This gem delayed the "touch" update at the end of the batch. It's cleaner with this !
But I still have problems with the transactions. I still don't know if I can insert all the data in one single query for each table.