Change audit query SQL to improve performance



When querying the audit tables using the alfresco API it can be seen that a query runs for a long time on the DB side which can max out DB resources.

[Steps to reproduce]

This has been reproduced at a customer's environment which has millions of entries in the audit tables, the customer is in the process of cleaning these out and they found this issue when trying to query audit records by date range

[Expected Behaviour]

When querying audit records by date range request do not result in http 500 errors 

[Observed Behaviour]

When querying audit records by date range requests can return http 500 errors due to waiting on DB to return.

[Analysis to date]

The DBA found the problematic query which belongs to the alfresco SQL template 



These queries take about 1-3 minutes to run, after tuning the query the new template looks like this:


The customer would like to have this embedded in the product.



rds postgres, tomcat, aws

Testcase ID



Marina Oliveira
February 12, 2021, 8:37 AM

Assigning back to support as triage and solution is proposed

Alan Davis
January 8, 2021, 5:15 PM

I don't think this issue is directly related to PRODMAN-341, which is about trying to reduce the overall size of audit data in the database.

It looks more closely related to and the MNT-21083. These two issues resulted in changing the way we cleaned up data in tables AFTER audit entries were deleted. The new approach was disabled by default in 5.2 (the version this customer is on) and only became the default in a later version, to avoid other customers suddenly having clean up jobs running if they took a 5.2 SP or HF with it in. Both customers had larger numbers of audit entries. The performance on the query side only improved after the old entries were removed and the new clean up script was run.

The query from the SQL template was never intended to scan millions of rows. However the investigation done by the customer may allow us to improve on what is there. Care would need to be taken to ensure that it would work for all our supported databases.

Steve Blair
January 6, 2021, 9:11 AM

This ticket is a Must-Have for ACS 7.0 and may be superseded by the work being done on

Won't Fix


Steve Blair


Jose Portillo



Escalated By


Security Issue


ACT Numbers


Premier Customer


Code Branch


Build Location


Regression Since


Work Funnel End


Patch Attached


Dependent Version/s


Cloud or Enterprise


Prioritization Score


Delivery Team

Team 5

Bug Priority

Category 2

Fix versions

Affects versions