The 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. A full list of posts on this topic can be found here.
The retrieval of enrolled contacts
To determine the enrollments that should be processed for a Marketing Automation Plan, Sitecore uses the ActivityEnrollments_GetTimedOut stored procedure, that queries the ActivityEnrollments table. This query selects all records where the TimeoutDate is smaller than the current date, and has the following issues:
No check if the Automation Plan is active
It does not check if the plan is active or not on query time, resulting in retrieving records that it does not need to process. A plan can be inactive due to it being deleted (note; records in the database are NEVER cleaned up, however they *should* be moved to a different table), or that a start or end date is configured.
Public reference: 417042, broken in 9.0+, fixed in 10.1
No limit and sorting on TimeoutDate
The query itself has no limit, so it always returns ALL records that need to be processed. It does not sort on TimeoutDate (or even sort on anything), so records are not returned in any guaranteed order, meaning that newer records can be processed before the older records and makes it impossible to add a select top X to the query.
Public reference: 353642, broken in 9.0+, fixed in 10.1
There’s no available index to use
Altough the query explicitly retrieves all records after a specific TimeoutDate, there’s no index placed on this column, so a full table scan occurs every time the query is executed.
Public reference: 411079, broken in 9.0+, fixed in 10.1