Database optimisations

The following indexes will be added to the Care Partner database during the upgrade to v2023.2:

• IX_OrganisationId ON [dbo].[AuditRecords] ([OrganisationId],[Date]);
• IX_OpenedByStaffMemberId ON [dbo].[FormInstances] ([OpenedByStaffMemberId],[Status]) INCLUDE ([DateAssessed], [InvolvementId], [FormDefinitionId], [ServiceUserId], [IsSensitive]);
• IX_FormInstanceItemsFormDefModified ON dbo.FormInstanceItems (FormInstanceId, FormDefinitionItemId, DateModified DESC) INCLUDE (IsKeyItem, IsAlert);
• IX_FormDefinitionAnswerSetId ON dbo.FormDefinitionEnumeratedAnswers (FormDefinitionAnswerSetId, IsTrigger);”);
• IX_CentreOfCareId ON [dbo].[Involvements] ([CentreOfCareId], [Status]) INCLUDE ([ServiceUserId]);
• IX_IsArchived ON [dbo].[People] ([IsArchived]) INCLUDE ([CurrentPersonDetailId], [CurrentServiceUserDetailId]);

A number of indexes have been retired in favour of the above in accordance to best practice.