Performance and Filtering Techniques
Advanced AL strategies for extreme server optimization, replica reading, and source filtering (Pushdown).
Our reduction in load time from 15 minutes to 30 seconds was not achieved solely by using a Query object, but through the application of aggressive Pushdown Computation (Server-side Filtering) techniques that we designed into the core of our AL code.
Below, we detail the three main optimization directives that we have implemented in our extraction APIs.
1. Traffic Redirection: DataAccessIntent = ReadOnly
Sección titulada «1. Desvío del Tráfico: DataAccessIntent = ReadOnly»In a transactional ERP system, the write database node (Primary Node) is sacred. We cannot allow our massive extractions to block accounting or warehouse insertions.
query 50100 "Movs_Productos_API"{ QueryType = API; // La directiva mágica: DataAccessIntent = ReadOnly; // ...}What does it do at the Azure SQL level? By declaring ReadOnly, we are telling the server orchestrator that our API will not insert or modify data. The server automatically redirects us to one of the database's Read-Only Replicas. This way, our reports can saturate the replica's CPU without affecting production user performance in the slightest.
2. Early Filtering: DataItemTableFilter
Sección titulada «2. Filtrado Temprano: DataItemTableFilter»The goal of ELT is to ensure that the smallest amount possible of useless data travels over the network cable. Instead of downloading everything into Power BI and then filtering, we encode the filter into the API.
elements { dataitem(itemLedgerEntry; "Item Ledger Entry") { // Filtro rígido a nivel de servidor SQL DataItemTableFilter = "Entry Type" = filter(Purchase | Sale | "Positive Adjmt." | "Negative Adjmt."), "Global Dimension 1 Code" = filter(<> '20');Benefits:
DataItemTableFilteris compiled directly as theWHEREclause ofSELECT.- We exclude useless input types and omit records from certain dimensions in milliseconds.
- The payload (the weight of the returned JSON) is drastically reduced, eradicating timeouts caused by HTTP network saturation.
3. FlowField Handling: ColumnFilter
Sección titulada «3. El Manejo de FlowFields: ColumnFilter»This is perhaps the most sophisticated engineering aspect of our APIs. As seen in the system architecture, some fields in Business Central are formulas (FlowFields).
If we try to filter a FlowField within the general DataItemTableFilter, the AL compiler will throw an error, because SQL Server cannot perform a WHERE on a calculated subquery that has not yet been processed.
To filter on these fields (such as excluding the 'PROVEEDOR_DEMO' provider), we must use the ColumnFilter property specifically nested within the projected column.
// Campo de tabla normal column(Cantidad; Quantity) { }
// Filtrado Nivel Pro: Aplicado sobre un FlowField o campo especial column(Nombre_Proveedor; "Nombre proveedor") { ColumnFilter = Nombre_Proveedor = filter(<> 'PROVEEDOR_DEMO'); }By doing this, the server first resolves the table, calculates the necessary FlowFields, applies the column post-filter, and finally sends us a clean and extremely compact data block.
4. Query Optimization Checklist
Sección titulada «4. Checklist de Optimización de Consultas»- ✓ Zero Blocking:
DataAccessIntentproperty configured asReadOnlyto direct the load to Azure SQL passive replicas. - ✓ SQL Filtering: Implementation of
DataItemTableFilterto reduce the volume of rows to transfer before HTTP transmission. - ✓ FlowFields Handling: Use of
ColumnFilterin dynamic columns to avoid query engine compilation errors.

