Prerequisites
Please answer the following questions before submitting an issue. YOU MAY DELETE THE PREREQUISITES SECTION.
- Production Verion 9.1:
- Type: Angular
- Framework: .net core
If issue related with ABP Framework
- ABP Framework version: 5.11
Hi there,
We have around 3 million rows in the AuditLogs table, and growing. We dont want to perform a periodic delete because we need these auditlogs to meeting record retention compilance (7 years)...
We are finding the table to be very slow to view the records when viewing them on the AuditLogs Page (app/admin/audit-logs).
Could you outline some strategies that might work to make this table work faster? We have indexes a the few columns we use to filter, while they have helped, still find the table very slow.
Regards JT
2 Answer(s)
-
0
Hi jtallon, You can try
AsNoTracking().var results = await query .AsNoTracking() .OrderBy(input.Sorting) .PageBy(input) .ToListAsync();If your data needs to be stored for 7 years, it is recommended that you create different query tables based on the year to avoid excessive data in a single table. If the performance is still not good, you can try to use native SQL (Dapper) to optimize the query statement.
-
0
Hi jtallon,
I was in the same situation : 4 million records in audit log table. I was also facing performance issue in the whole app running on Azure.
I finally created an Azure Data Factory pipeline to export audit logs to CSV file once per week and deleted those records from database. I can be more specific on this part if somebody needs help.
Right now the app perforamance is much more better ! I've optimized other things (SignalR, more power to Azure instance, better database server, periodic database maintenance...) but I have to say that audit logs have a huge impact.
I think zero team should inform about that. Even if it is a sys DBA action, and zero is database agnostic, it's too important to avoid mentioning that. A blog post or an article could be a good starting point :)
