AX1743
By default, Axiom queries are processed in sequential order, one by one. Axiom Software walks through the sheets defined on the Control Sheet from left to right, and then processes the queries on each sheet in ascending order (AQ1 first, then AQ2, and so on).
You can optionally define batches of Axiom queries within a sheet, so that the queries within the batch are processed in parallel instead of sequentially. You can assign two or more queries to the same batch if those queries are not dependent on each other and therefore can be run at the same time. Batching queries to be run in parallel can improve file performance, especially when the file has many queries, or has several process-intensive queries (such as queries that return a lot of data, or that use large calc methods).
In order to process a query within a batch, you assign the query a batch number. All queries on a sheet that have the same batch number are processed in parallel instead of sequentially.
Enabling batch processing for a query
To enable batch processing for an Axiom query, enter a batch number into the Batch Number field on the Control Sheet. This setting is defined per Axiom query, in the Query Details section.
Batch Number setting for an Axiom query
When batch numbers are defined for Axiom queries, query processing works as follows:
-
Each sheet is still processed in order. It is not possible to configure queries on separate sheets to be processed in the same batch.
-
For each sheet, queries with an assigned batch number are processed first. Batches are processed in ascending order (batch 1 first, then batch 2, and so on). All queries with the same batch number are processed in parallel.
-
After all batches have been processed for a sheet, the remaining Axiom queries on that sheet are processed, in ascending order.
Example
Imagine that the Axiom queries on a sheet are configured as follows:
| Query | Batch | |
|---|---|---|
| AQ1 | 1 | |
| AQ2 | ||
| AQ3 | 1 | |
| AQ4 | 2 | |
| AQ5 | 2 | |
| AQ6 |
When this sheet is processed, the queries are run as follows:
- AQ1 and AQ3 (batch 1) are processed first, in parallel.
- AQ4 and AQ5 (batch 2) are processed second, in parallel.
- The remaining queries are processed sequentially—AQ2 first, then AQ6.
You can re-use the same batch numbers on separate sheets, since each sheet is processed separately. For example, both the Lists sheet and the Report sheet can have a batch 1.
Requirements and limitations
If a query is dependent on another query in the sheet, then either the dependent query should not be assigned to a batch, or it should be assigned to a separate batch that runs after the "parent" query.
For example, imagine that AQ1 is a rebuild query that brings in a list of departments, and AQ2 is an update-only query that brings in data for those departments. In this case, AQ1 must be run before AQ2. If the sheet has other queries that can be run in parallel with AQ1, then you can assign AQ1 and the other queries to batch 1, and leave the batch number for AQ2 blank. Or, if AQ2 can be run in parallel with other queries in the sheet that are dependent on the batch 1 queries, then AQ2 can be assigned to batch 2. But if both AQ1 and AQ2 are assigned to batch 1, then the queries will not work as expected.
Batch processing is not supported for:
- Queries where the primary table is a system table, such as Axiom.Columns. These queries cannot be run in parallel.
-
All queries run during file processing. File processing does not support running queries in parallel.
The following query features do not apply when using batch processing:
- Zero AQs on Save is ignored for any query that is assigned to a batch.
- Refresh Control Sheet between every AQ is ignored for batch processing. The Control Sheet is not refreshed and settings are not re-read during a batch, or in between batches. However, if the sheet has Axiom queries that are not part of batch processing, the Control Sheet will be refreshed as normal between those queries.
