We’ve built an exam system that has a pretty complex structure. Some background info on how exams are built:
– Exams (basic exam details, contains modules)
– Modules (basic module details, contains either sub-modules or questions)
– Questions (multiple choice or long answer)
When a student is sitting down to take an exam, they “activate” it, and the system creates a copy of the exam for them – this includes the exam, the modules and sub-modules, and a cross-section of the questions (randomly selected from a large question bank).
Since each exam is unique and we need to keep a history of exactly what they wrote, we need to make a full copy – not just store references to questions and modules.
The copy function is where we’re running into trouble – we’re using add_row and add_sub_row to add a large number of rows (100ish) that have 5-10 fields each. When we add each sub-row on its own, the system takes an average of about 3 seconds. When we add the main row with all of the sub-rows at once, it takes minutes for the whole structure to be added.
There are undoubtedly a huge number of queries running to add all of this data, but we’re struggling to find a way to do this more quickly.
We’ve disabled post revisions on that post type, and have disabled all other plugins to ensure that nothing is conflicting.
Any help would be appreciated! Thanks
Whether you add all of the rows using update_field(), or you add each row, the problem is the same.
ACF uses the built in WP function update_post_meta() or one of the variant functions that does the same thing. This function can only update a single custom field and it causes from 2 or 3 queries to be run each time it is called. First it sees if the meta_key/meta_value exists and then it inserts or updates depending on that. To be honest, I’m not sure what triggers the 3rd query and it does not always happen. In addition to this ACF adds a field key reference meta_key and this update causes another 2 or 3 queries to be run. So you have a total of 4 to 6 queries for every field updated.
This is a limitation in ACF, but the root cause is a limitation in WP due to the way it handles custom fields/meta values.
Unfortunately, there isn’t workable solution. SQL does allow inserting multiple rows to a table in a single query, but doing so causes other issues, like the meta cache not being updated correctly. The only real solution would be to construct custom queries for inserting the values with all the correct meta_key/meta_value pairs and then directly insert the values to the DB using SQL rather than allowing WP to handle the work.
Yeah, I kinda figured. Was hoping for some miraculous way around jamming a ton of data into WordPress…
We ended up changing the business logic on this one, and are now running the duplicate exam function every 10 minutes starting a few days before the exam date, so that all users will have their exams created before they show up. Not the best, but everyone will at least get instant access to their exams.
Thanks for the reply!
You must be logged in to reply to this topic.
Welcome to the Advanced Custom Fields community forum.
Browse through ideas, snippets of code, questions and answers between fellow ACF users
Helping others is a great way to earn karma, gain badges and help ACF development!
© 2022 Advanced Custom Fields.