Slow audit history view for entity

One of clients complained that audit history view for a specific account takes a long time (about sixty seconds) to load. For most of other entities it worked fast. I armed with sql profiler to check what's happening there.
The audit history for entity loaded very fast. But then until the interface renders results there were bunch of heavy queries each of which took about 5-10 seconds to load. Here is query example:

Select top 1 attributemask,changedata from AuditBase (NOLOCK) where
AuditID IN (
Select AuditID  from AuditBase (NOLOCK) where ([Action] = 101 and [ObjectTypeCode] = 1)
) and 
attributemask like '%,12345,%'

As far as I can understand it tries to find name (ChangeData column) of removed attributes before rendering audit entry for them. That specific organization has been using CRM for about three years and got 47 million rows in AuditBase table. It surely should've been cleaned up but they are reluctant to loose any audit history.
After checking queries and execution plans, I thought that these queries should run fast if there is nothing to look for.
This workaround will remove audit entries for deleting attribute action for problematic entity (account in my our case):

delete
from AuditBase
where ObjectTypeCode = 1 -- Problematic entity (Account)
	and [Action] = 101 -- Delete Attribute

Sure it should be considered doing if this actions aren't seen as valuable.
In result audit history view load takes two seconds instead of sixty. Definitely worth it.

Show Comments