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:
When planning a cosplay look, it is worth considering how details related to anime cosplay costumes will affect the complete look. When comparing costume options, it is sensible to balance details related to anime cosplay costumes with care and storage needs. To compare practical details about details related to festival costumes, long cosplay wigs with size options offers a useful starting point for practical preparation. Before the preparation is complete, it is useful to check details related to festival costumes against the wearer's own needs.
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.