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, so that the queries within the batch are processed concurrently 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. Queries that have the same batch number are processed concurrently instead of sequentially. Batches are processed in ascending order, with the queries in batch 1 processed before the queries in batch 2, and so on.
Axiom query batches can be processed using two different approaches. The specific approach can be set on a per file basis.
-
Per-sheet batching: By default, batches are processed per sheet. Each sheet is still processed in order, but the batched queries on the sheet are processed first, followed by the non-batched queries.
-
Cross-sheet batching: If cross-sheet batching is enabled, then batches are processed across sheets. All queries that belong to a batch are processed concurrently in batch order, regardless of which sheet they are on. Once the batched queries are complete, non-batched queries are processed sheet-by-sheet as normal.
Enabling per-sheet batch processing
To enable per-sheet batch processing for Axiom queries, 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. 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. This means:
- The database queries for all Axiom queries in the batch are made at the same time.
- Axiom query data is placed in the sheet in the order that the database queries are completed. For example, if AQ1 and AQ2 are in the same batch, but the database query for AQ2 completes first, then data for AQ2 is placed in the sheet first.
-
After all batches have been processed for a sheet, the remaining Axiom queries on that sheet are processed, in ascending order based on query number.
Per-Sheet 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.
Enabling cross-sheet batch processing
To enable cross-sheet batch processing for Axiom queries:
-
In the Workbook Options section of the Control Sheet, set Process cross sheet AQ Batches to On.
Setting to enable cross-sheet batching
-
For all Axiom queries that you want to run in a batch, 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.
When batch numbers are defined for Axiom queries and cross-sheet batch processing is enabled, query processing works as follows:
-
Queries in batch 1 are processed first. This occurs regardless of which sheets the queries are on. Queries are processed in parallel, which means:
- The database queries for all Axiom queries in the batch are made at the same time.
- Axiom query data is placed in the sheet in the order that the database queries are completed. For example, if AQ1 and AQ2 are in the same batch, but the database query for AQ2 completes first, then data for AQ2 is placed in the sheet first.
-
Queries in batch 2 are processed next, and so on, until all batches are completed.
-
After all batches are completed, any remaining Axiom queries are processed using normal per sheet processing, in ascending order based on query number.
Cross-Sheet Example
Imagine that the Axiom queries in a file are configured as follows:
| Sheet1 | Sheet2 | |||||
|---|---|---|---|---|---|---|
| Query | Batch | Query | Batch | |||
| AQ1 | 1 | AQ1 | 2 | |||
| AQ2 | 2 | AQ2 | 1 | |||
| AQ3 | 1 | AQ3 | 2 | |||
| AQ4 | 2 | AQ4 | ||||
| AQ5 | ||||||
When this file is processed, the queries are run as follows:
- The queries in batch 1 are processed first, in parallel. This includes AQ1 and AQ3 from Sheet1, and AQ2 from Sheet2.
- The queries in batch 2 are processed next, in parallel. This includes AQ2 and AQ4 from Sheet1, and AQ1and AQ3 from Sheet2.
- Normal per-sheet query processing now begins. AQ5 on Sheet1 is processed first, then AQ4 on Sheet2.
Requirements and limitations
If a query is dependent on another query, 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 there are 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 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 the following types of queries:
- 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.
