<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class=""><div class=""><br class=""></div>Hi Peter,<div class=""><br class=""></div><div class="">I’m not familiar with the spreadsheet importer, but it sounds like you’ve done the analysis to be in a position to fix it. :)</div><div class=""><br class=""></div><div class="">Here’s a commit for the “run tonight” feature implemented in a plugin:</div><div class=""><a href="https://gitlab.gaiaresources.com.au/qsa/as_runcorn/-/commit/8a2bd97" class="">https://gitlab.gaiaresources.com.au/qsa/as_runcorn/-/commit/8a2bd97</a></div><div class=""><br class=""></div><div class="">It was refined in a few subsequent commits (4369dd0, 800ac1c, 5f1207e).</div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">Cheers,</div><div class="">James</div><div class=""><br class=""></div><div class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Jun 6, 2021, at 6:02 PM, Peter Heiner <<a href="mailto:ph448@cam.ac.uk" class="">ph448@cam.ac.uk</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="">James Bullen wrote on 2021-06-06 11:35:03:<br class=""><br class=""><blockquote type="cite" class="">Bulk update operations usually run in database transactions. This is great because it guarantees that the database will be in a consistent state when the job completes - if it succeeds then all of the updates are applied, if it fails then none of the updates are applied.<br class=""><br class="">The price for this guarantee is that any affected records are locked until the job completes.<br class=""></blockquote><br class="">Thanks for your response. I generally agree with your points but have mine to make.<br class="">One is that the consistency unit here need not be the entire job, it is sufficient to isolate a single line, and I would normally expect the database to cope fine with such a workload.<br class="">Secondly, if I read my database output correctly, in this case the problem is not a wholly isolated bulk update but unnecessary table scans occurring for each line that make the database hold a lock for several seconds instead of milliseconds. I've yet to understand the purpose of the subnote_metadata table and why the delete is taking place, but we did notice that the query tried IDs that were not in the database.<br class="">In any case, it should be possible to unroll the subquery or provide optimiser hints for the bottleneck to go away.<br class=""><br class=""><blockquote type="cite" class="">We recently implemented the ability to submit import jobs to be run after hours for a client to work around this.<br class=""></blockquote><br class="">We were thinking of a similar workaround, do you have anything you could share with us?<br class=""><br class="">Thanks,<br class="">p<br class="">_______________________________________________<br class="">Archivesspace_Users_Group mailing list<br class=""><a href="mailto:Archivesspace_Users_Group@lyralists.lyrasis.org" class="">Archivesspace_Users_Group@lyralists.lyrasis.org</a><br class="">http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group<br class=""></div></div></blockquote></div><br class=""></div></body></html>