Email Greeting Update Job performance issue
Recently tried to update all the email_greeting's on a large number of contacts. I ran into significantly long queries (10+ secs), that locked
civicrm_contact table for updates! This caused very slow operation on production site and in a some cases timeouts.
- Cleared all the
- Swapped in a new Default Email Greeting ID
- Ran the
ct=Individual gt=email_greeting force=0 limit=1000
Very poor performance on job run:
- Very slow
- Locks table for updates.
Make it fast!
Tracked down the code to here: https://lab.civicrm.org/dev/core/-/blob/master/CRM/Contact/BAO/Contact/Utils.php#L1010
Ran an EXPLAIN on this SQL query and it is a table scan. Mystery explained.
I applied a tweak to the query that appears to fix the issue (at least for my paramters). Indeed I stole the idea from lower down in that very function.
WHERE id IN (" . implode(',', $allContactIds) . ") (after the huge CASE statement) to the main update query appears to remove the table scan and prevent the table locking.
Not sure this is a decent general solution, but just wanted to offer it!