Querying _Aud-Audit-Data

D

dwhite

Member
We use the Progress built in tables to store audit data and are having some performance problems when trying to bring back the data in a certain way. Here is the query we run:

FOR EACH _aud-audit-data NO-LOCK
WHERE _aud-audit-data._audit-date-time > StartDate
 AND _aud-audit-data._audit-date-time < EndDate
 AND _aud-audit-data._Event-id = EventId
 AND _aud-audit-data._event-context = UserId:

For some reason this takes an extraoridinarly long time to run - but as far as we can tell we are hitting the indexes. I was wondering if anyone uses these tables to query data and if so if you could give me some hints on how to improve performance.

FYI - using a start date of 3 pm today to 4 pm today, can a good 5 minutes to come back. So you can imagine if you want to run this for several days how this will perform.
 
As far as I remember: It is recommended to have the indexes on the audit tables deactivated in the production database to not hurt transaction performance and regularly dump the audit data and import it into a audit reporting database which should NOT have that indexes deactivated.

If you are doing the report against the production database it is very likely that the indexes on the audit tables are deactivated which would explain the very poor performance.

Heavy Regards, RealHeavyDude.
 
You must log in or register to reply here.
Back
Top