"ca_query_pre_result" table may have several rows
Article ID: 250471
Updated On:
Products
Issue/Introduction
It's possible that on some environments this table can have thousands or even millions of rows, this doesn't affect the environment itself, but why this table can have so many rows and how to correct this behavior?
Environment
Client Automation 14.5
Cause
ca_query_pre_result table is a temporary table (repository) used to store the data related to the intermediate query results while a linked query is being executed, which are custom queries. These queries are being used for dynamic group evaluation, policies, etc. by Customers.
Based on the dependency of the data for outer query execution, this data will be stored in the MDB rather than deleting it, as happens on regular queries. If a Customer is using a lot of Linked queries and also executing them on a continuous basis, this table will increase in size.
Resolution
Based on the review made by L2 team, this behavior can be modified, but since the effort to do it big, it can't be made on a single fix or patch, this requires a change in DB schema and also checking for multiple Engine execution, as a way to run a DELETE internally, without affecting the normal execution of queries.
L2 team will consider adding this modification for a future release.
If any Customer is facing this behavior, at the moment this article was written (14.5 CU4), we only have workarounds, which are the following:
1. First thing to do, which is completely harmless to the environment, is executing a SQL script (TRUNCATE) to cleanup the data from this table, which can be executed on a timely basis, as a way to manually keep the data controlled.
2. Another suggestion is to find out which queries are Linked queries in the environment, and verify if all of those are really needed in the environment.
3. If those queries are actually needed, what can be checked is the frequency of execution of them and try to reduce it.
Feedback
