Ir al contenido

Performance and Filtering Techniques

Advanced AL strategies for extreme server optimization, replica reading, and source filtering (Pushdown).

0views

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.

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:

  • DataItemTableFilter is compiled directly as the WHERE clause of SELECT.
  • 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.

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.

  • ✓ Zero Blocking: DataAccessIntent property configured as ReadOnly to direct the load to Azure SQL passive replicas.
  • ✓ SQL Filtering: Implementation of DataItemTableFilter to reduce the volume of rows to transfer before HTTP transmission.
  • ✓ FlowFields Handling: Use of ColumnFilter in dynamic columns to avoid query engine compilation errors.