Sitecore Marketing Automation has serveral performance issues when trying to use it with a large number of contacts, which causes heavy database usage, or causing the plans to not function at all. In the upcoming blog posts about this subject I’ll share some tips on how to improve the performance. The upcoming time i’ll be blogging some more about this topic, and a full list of posts on this topic can be found here.
Adding contacts to an automation plan
Within a Sitecore Automation Plan, you have several ways to enroll contacts. Whatever route you take leads to Sitecore adding a record to the AutomationPool table in the MarketingAutomation database. The stored procedure AutomationPool_Add is used to add a contact to this table.
The stored procedure uses a MERGE query to insert or update the records required for the Marketing Automation Plan. The benefit of the MERGE query is that it’s an easy way to either update or insert records, however a big drawback is that is always does a full table scan (which means that it has to iterate over each record). This will not be a problem when you have several records in your MarketingAutomation table, but the bigger your table gets, the longer the MERGE query will take and the more resources it will need.
As the execution time of the AutomationPool_Add stored procedure increases when the AutomationPool table gets bigger, it will take longer to add contacts to a plan, but even worse; the MERGE query will lock the table causing other operations to slow down as well. You can scale up your database so the queries will complete in time, however this costs money and you would have to keep scaling as long as the AutomationPool grows.
I’ve shared my findings with Sitecore which eventually lead to an optimized AutomationPool_Add that does not add records using a MERGE but using seperate INSERT/UPDATE statements. You can download the version for Sitecore 9.1.0 here, or ask for an updated version of the stored procedure while referencing public ref #336990.
The AutomationPool_Checkin stored procedure also uses a MERGE statement and may benefit from the same rewrite as the AutomationPool_Add procedure.
Affected versions: Sitecore 9.0 – 10.1.1 (and probably upwards)