origam.com

Audit log screen - very slow page load

Is there any possibility to speed up the Audit log screen load? It takes really long time to load several records.

The reason it is slow is because of a too complex query that loads related changes.

I propose splitting the audit view into two sections - master with the current entity changes and a detail with related entities changes. And make it lazy loaded. This way it would not need to load all the changes in one complex query that is forced to do a table fullscan.

@koki Could you test it?

Actually I got following error on Master 2021.2.0.2369

image

Have you updated the “Audit” package?

Yes, the Audit package is updated from Master 2021.2.0.2369 model.

The error appeared, when I tried Audit on screen form with 50 000 rows. When I tried it on form with 20 000 rows, the Audit screen is shown but it takes loooong time. So there is no speed up with updated Audit package.

I’ve analyzed the underlying SQL commands. At the moment audit uses this:

DECLARE @OrigamDataAuditLog_parRefParentRecordId AS UniqueIdentifier;

SET @OrigamDataAuditLog_parRefParentRecordId = NULL


-----------------------------------------------------------------
-- OrigamDataAuditLog
-----------------------------------------------------------------
SELECT
	[OrigamDataAuditLog].[OldValue] AS [OldValue],
	[OrigamDataAuditLog].[refColumnId] AS [refColumnId],
	[OrigamDataAuditLog].[NewValueId] AS [NewValueId],
	[OrigamDataAuditLog].[NewValue] AS [NewValue],
	[OrigamDataAuditLog].[OldValueId] AS [OldValueId],
	[OrigamDataAuditLog].[ActionType] AS [ActionType],
	[OrigamDataAuditLog].[SecondReferenceKey] AS [SecondReferenceKey],
	[OrigamDataAuditLog].[refParentRecordEntityId] AS [refParentRecordEntityId],
	[OrigamDataAuditLog].[refParentRecordId] AS [refParentRecordId],
	[OrigamDataAuditLog].[RecordUpdated] AS [RecordUpdated],
	[OrigamDataAuditLog].[RecordCreatedServer] AS [RecordCreatedServer],
	[OrigamDataAuditLog].[Id] AS [Id],
	[OrigamDataAuditLog].[RecordUpdatedServer] AS [RecordUpdatedServer],
	[OrigamDataAuditLog].[RecordCreatedBy] AS [RecordCreatedBy],
	[OrigamDataAuditLog].[RecordCreated] AS [RecordCreated],
	[OrigamDataAuditLog].[RecordUpdatedBy] AS [RecordUpdatedBy]
FROM
	[OrigamDataAuditLog] AS [OrigamDataAuditLog]
WHERE  (
	 (( ([OrigamDataAuditLog].[refParentRecordId] = @OrigamDataAuditLog_parRefParentRecordId) OR  ([OrigamDataAuditLog].[SecondReferenceKey] = @OrigamDataAuditLog_parRefParentRecordId))));

OrigamDataAuditLog_parRefParentRecordId is the id of the record we’re auditing. Despite having indexes in place, they’re ignored and SQL server runs table scan.

After slight adjustment of the query, the indices start being used:

DECLARE @OrigamDataAuditLog_parRefParentRecordId AS UniqueIdentifier;

SET @OrigamDataAuditLog_parRefParentRecordId = '2462eecb-5191-4059-b71f-da784ed1ce8e'


-----------------------------------------------------------------
-- OrigamDataAuditLog
-----------------------------------------------------------------
SELECT
	[OrigamDataAuditLog].[OldValue] AS [OldValue],
	[OrigamDataAuditLog].[refColumnId] AS [refColumnId],
	[OrigamDataAuditLog].[NewValueId] AS [NewValueId],
	[OrigamDataAuditLog].[NewValue] AS [NewValue],
	[OrigamDataAuditLog].[OldValueId] AS [OldValueId],
	[OrigamDataAuditLog].[ActionType] AS [ActionType],
	[OrigamDataAuditLog].[SecondReferenceKey] AS [SecondReferenceKey],
	[OrigamDataAuditLog].[refParentRecordEntityId] AS [refParentRecordEntityId],
	[OrigamDataAuditLog].[refParentRecordId] AS [refParentRecordId],
	[OrigamDataAuditLog].[RecordUpdated] AS [RecordUpdated],
	[OrigamDataAuditLog].[RecordCreatedServer] AS [RecordCreatedServer],
	[OrigamDataAuditLog].[Id] AS [Id],
	[OrigamDataAuditLog].[RecordUpdatedServer] AS [RecordUpdatedServer],
	[OrigamDataAuditLog].[RecordCreatedBy] AS [RecordCreatedBy],
	[OrigamDataAuditLog].[RecordCreated] AS [RecordCreated],
	[OrigamDataAuditLog].[RecordUpdatedBy] AS [RecordUpdatedBy]
FROM
	[OrigamDataAuditLog] AS [OrigamDataAuditLog]
WHERE 
[OrigamDataAuditLog].[refParentRecordId] = @OrigamDataAuditLog_parRefParentRecordId 
OR  (([OrigamDataAuditLog].[SecondReferenceKey] = @OrigamDataAuditLog_parRefParentRecordId)
	 and (OrigamDataAuditLog.ActionType = 8))

@koki Can you confirm performance gain?

@washi There is really performance gain for the second query. The first query run time for specific refParentRecordId was 3:53. The second query run time was 22 seconds. Great!
Please, could you implement it into the Audit package?
Thank you!

22 seconds seems still very high to me. Are there many records being returned?

There are over 82 million rows in DataAuditLog and the query returned 96 rows.

Then I would not accept 22 seconds as good. Returning 96 records must be in milliseconds. Can you please post your actual query plan?

When I try it today for the same or different refParentRecordId the query times are: 5 sec current query, 2 sec new query. The query execution plan is:

That is also very strange as the old query was using a table scan, which in your case should take ages. 2 seconds would be acceptable, of course.

I think that an old query (before including the deleted items) should be used as a point of reference for the performance comparison.

DECLARE @OrigamDataAuditLog_parRefParentRecordId AS UniqueIdentifier;

SET @OrigamDataAuditLog_parRefParentRecordId = '2462eecb-5191-4059-b71f-da784ed1ce8e'


-----------------------------------------------------------------
-- OrigamDataAuditLog
-----------------------------------------------------------------
SELECT
	[OrigamDataAuditLog].[OldValue] AS [OldValue],
	[OrigamDataAuditLog].[refColumnId] AS [refColumnId],
	[OrigamDataAuditLog].[NewValueId] AS [NewValueId],
	[OrigamDataAuditLog].[NewValue] AS [NewValue],
	[OrigamDataAuditLog].[OldValueId] AS [OldValueId],
	[OrigamDataAuditLog].[ActionType] AS [ActionType],
	[OrigamDataAuditLog].[SecondReferenceKey] AS [SecondReferenceKey],
	[OrigamDataAuditLog].[refParentRecordEntityId] AS [refParentRecordEntityId],
	[OrigamDataAuditLog].[refParentRecordId] AS [refParentRecordId],
	[OrigamDataAuditLog].[RecordUpdated] AS [RecordUpdated],
	[OrigamDataAuditLog].[RecordCreatedServer] AS [RecordCreatedServer],
	[OrigamDataAuditLog].[Id] AS [Id],
	[OrigamDataAuditLog].[RecordUpdatedServer] AS [RecordUpdatedServer],
	[OrigamDataAuditLog].[RecordCreatedBy] AS [RecordCreatedBy],
	[OrigamDataAuditLog].[RecordCreated] AS [RecordCreated],
	[OrigamDataAuditLog].[RecordUpdatedBy] AS [RecordUpdatedBy]
FROM
	[OrigamDataAuditLog] AS [OrigamDataAuditLog]
WHERE 
[OrigamDataAuditLog].[refParentRecordId] = @OrigamDataAuditLog_parRefParentRecordId

@koki The adjustments are available in model.