GXL Batch Execution
This topic describes the GXL Batch Execution feature, a performance optimization capability that reduces database round-trips by grouping multiple GXL queries into batched executions.
Note: This feature is available in Spreadsheet Server v25.4 and later.
Overview
Batch Execution improves calculation performance for workbooks with numerous GXL formulas by accumulating queries that normally run individually against the database, grouping these queries into optimized chunks, and executing them as batched queries instead of individual queries. This approach reduces communication between Excel and database servers (SQL Server, Oracle, Cloud Connector, etc.), resulting in significant performance gains.
How Batch Execution Works
When Batch Execution is enabled, the system:
- Accumulates GXL queries that normally run individually against the database
- Groups these queries into chunks (chunk size varies based on database type and query length limits)
- Runs batched queries instead of individual queries
- Returns results mapped back to the original formula requests
Enable Batch Execution
Important: Batch Execution is not enabled by default. Each user must manually activate this feature.
To enable Batch Execution:
- Open Excel with Spreadsheet Server installed.
- Navigate to the Spreadsheet Server ribbon.
- Click the Data Refresh dropdown menu.
- Select Batch Execution to toggle the feature on.
Note: The option displays a checkmark when enabled. To disable, click the option again.
Scope
This optimization applies specifically to Custom GLs only. Standard/built-in ERPs and other formula types remain unaffected by this setting.
Performance Results
Testing demonstrates significant performance improvements with Batch Execution enabled:
1747 GXL Test Workbook
| Run | Before | After | Enhanced | Improvement |
|---|---|---|---|---|
| 1 | 65s | 25s | 4s | -61s (-94%) |
| 2 | 36s | 7s | 5s | -31s (-86%) |
| 3 | 15s | 7s | 6s | -9s (-60%) |
| 4 | 17s | 7s | 5s | -12s (-71%) |
| Avg | 33s | 12s | 5s | -28s (-85%) |
35010 GXL Customer Workbook
| Run | Before | After | Enhanced | Improvement |
|---|---|---|---|---|
| 1 | 351s | 247s | 185s | -166s (-47%) |
| 2 | 310s | 227s | 155s | -155s (-50%) |
| 3 | 311s | 264s | 160s | -151s (-49%) |
| 4 | 324s | 233s | 168s | -156s (-48%) |
| Avg | 324s | 243s | 167s | -157s (-48%) |
Summary
- Smaller workbook (1,747 formulas): Approximately 85% faster - reduced from 33s average to 5s average
- Larger customer workbook (35,010 formulas): Approximately 48% faster - reduced from 324s average to 167s average
GL Support
Currently Supported
- Astronova GL - Fully tested and supported
Known Unsupported
- Viewpoint GL - Uses UNION ALL within SQL for balances, which is incompatible with the batching algorithm
- Other GLs may have compatibility issues that have not yet been identified
Future Support
Support will expand to additional GLs based on user feedback and testing. To request support for a specific GL or report issues, contact support or submit a feature request.
Limitations
- Applies only to Custom GLs - built-in ERPs remain unaffected
- Not all GLs are compatible with the batching algorithm
- GLs using UNION ALL in SQL statements may not function correctly
- Query chunk sizes vary by database type due to different query length limits
- Some GLs have not been tested and may have undiscovered compatibility issues
Troubleshooting
If calculation issues occur with Batch Execution enabled:
- Disable Batch Execution using the Data Refresh menu.
- Recalculate the workbook to verify the issue relates to Batch Execution.
- Report the GL type and issue details to support for investigation and potential support addition.