Why are person merges so slow after upgrading to v14+?

Question

Originally posted by Unknown

After upgrading to v14, person merges have been taking up to a minute to process. Even for simple merges.

Answer 1

Originally posted by Unknown

This issue was introduced with this commit:
https://github.com/SparkDevNetwork/Rock/commit/26bbe609687584710f93826e98c1d053e48f495a

Since the [ValueAsPersonId] column of the AttributeValue table is no longer computed, the person merge will need to manually update any references to this column. (Image below is from the [dbo].[spCrm_PersonMerge] stored procedure):

Since this column is not indexed, it can take a long time for this step to complete. So to fix it, you will need to add this index by running the SQL statement below:

CREATE NONCLUSTERED INDEX [IX_9Embers_ValueAsPersonId] ON [dbo].[AttributeValue]
(
    [ValueAsPersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO