Introduction to Business Central caching

A few days ago, I had a chat with a friend who asked me where his BC data access queries could be hiding from the SQL Server Profiler. He was running the SQL Server Profiler which was undoubtedly set up correctly, and the proper profiling events were captured. Some of the queries (namely inserts and updates) were reflected in the profiler trace correctly, while some data access operations, like FindSet or CalcFields would not show up in the profiler. What could be wrong with these queries? Where could they be covered up?

You've probably already guessed what this hiding place could be. Of course, it is the BC transaction cache, and this conversation reminded me that I was planning a post on this topic for a while. So now I'm going to share a (short and very simplified) overview of the caching subsystem in BC and explain why most, if not all, attempts to build a cache in the client application do not improve performance in any way. Yes, this includes the GetRecordOnce pattern (it actually has a different purpose).

Caching subsystem is an important part of any data-intense system, and Business Central is not an exception. BC Server has an intricate caching mechanism which helps reduce the load on the underlying database and the network by saving the data received from the data provider level, closer to the application which consumes the data.

At a very high level, without delving into details, we can outline the process of retrieving data from a data source in the following diagram.

When a data access request is initiated in the client application, the server will check if the requested data can possibly reside in the cache before forwarding the request to the data provider. The evaluation of the request is based on multiple parameters, and in the end this evaluation results in the cache hit or cache miss decision. If the data cache finds the request data residing in the buffer, the query is not sent to the database server, but the cached data will be returned to the app instead.

In this post, I will demonstrate some examples of the data caching and how different parameters of data access functions impact caching.

Profiler setup

In the following examples I will use an on-premises instance of Business Central and the SQL Server Profiler to capture queries reaching the SQL Server. This is the simplest and the most reliable way to observe the communication between the BC server and its database. The testing method is very simple - any SQL query sent by the BC server will be reflected in the profiler. If a client application is reading data, and the respective query does not appear in the profiler trace, I assume that the cache is used.

To begin with, I simply start the profiler and choose the Tuning template without any changes to the predefined configuration.

The tuning template enables 3 events: RPC:Completed, SP:StmtCompleted, and SQL:BatchCompleted which will allow me to intercept all queries coming from the BC server to the SQL Server.

These are the events and fields enabled by default with the selected template.

Example 1: Exact cache hit

First example is the most straightforward demonstration of the data caching - I simply run the FindSet function on the Item table, and then repeat the same request again. This FindSet can be triggered from any object. I just put the function call in a page action.

action(SelectAllItems)
{
    Caption = 'Select All Items';
    ApplicationArea = All;

    trigger OnAction()
    var
        Item: Record Item;
    begin
        Item.FindSet();
    end;
}

When the page action is executed, the SQL Server Profiler captures the SELECT query reading the Item table.

This is the query in a nutshell - it's just a plain SELECT obscured by a long list of fields of the Item table.

SELECT "27"."timestamp","27"."No_","27"."No_ 2","27"."Description",...

<Here goes the long list of table fields>

FROM "CRONUS".dbo."CRONUS International Ltd_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972" "27"
WITH(READUNCOMMITTED)  
ORDER BY "No_" ASC OPTION(FAST 50)

Now, without any changes, I run the same action again and look at the profiler trace searching for queries to the Item table.

The trace still shows some queries, but all of them are triggered by system processes and none are querying the Item table again. The Business Central server already stored the requested dataset, so it returns the data directly from cache without sending the request to the database server.

Example 2: Filtered dataset

The second example introduces a small modification to the previous code snippet. Here, I add a filter on the No. field limiting the dataset.

trigger OnAction()
var
    Item: Record Item;
begin
    Item.SetRange("No.", '1000', '1900');
    Item.FindSet();
end;

After running the action, SQL Profiler demonstrates a query similar to the previous example, with an additional WHERE clause matching the record filter.

SELECT "27"."timestamp","27"."No_","27"."No_ 2","27"."Description",...

<Here goes the long list of table fields>

FROM "CRONUS".dbo."CRONUS International Ltd_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972" "27"
WITH(READUNCOMMITTED)  
WHERE ("27"."No_">=@0 AND "27"."No_"<=@1)
ORDER BY "No_" ASC OPTION(FAST 50)

I don't want to bore the readers and overload the article with screenshots of the profiler after each action. I will just state that, just like in the previous example, the second execution of the same action did not trigger the database query, and no queries from the client app are reflected in the profiler trace. Once again, all data is received from the BC cache.

Example 3: Filter on a subset of a cached dataset

Now let's run one trickier test. I'm going to run a FindSet on a record with a filter, and then narrow down the filter to receive a subset of records returned by the first query.

Next code block demonstrates the two actions that will be executed sequentially - one with the wider filter range first, and the more narrow filter after that.

action(SelectItemsFiltered)
{
    Caption = 'Select Items Filtered';
    ApplicationArea = All;

    trigger OnAction()
    var
        Item: Record Item;
    begin
        Item.SetRange("No.", '1000', '1900');
        Item.FindSet();
    end;
}

action(SelectItemsFilteredSubset)
{
    Caption = 'Select Items Filtered Subset';
    ApplicationArea = All;
    
    trigger OnAction()
    var
        Item: Record Item;
    begin
        Item.SetRange("No.", '1200', '1500');
        Item.FindSet();
    end;
}

When the first action is completed, items from 1200 to 1500 will already be present in the cache because these numbers fall within the range of the first query 1000..1900.

Despite this, both of the actions executed one after the other, trigger database queries. Basically, it is the same query - SELECT on the Item table. Even the WHERE clause looks the same in both:

WHERE ("27"."No_">=@0 AND "27"."No_"<=@1)

The only difference between the two queries is the values of the filter parameters @0 and @1, and this difference forces the Business Central server to evaluate the data request as a cache miss and send the database query. This illustrates the basic principle of the cache evaluation: the complete set of parameters can be considered the primary key of the cache entry, and the full match is required to access the cached data.

Even simpler example of the same concept is a Get request executed after a FindSet. If, after running the previous two actions, I trigger Item.Get on one of those records which have been read previously (for example, Item.Get('1200') ), this function will still send another SQL query, although the item record is now present is two cache entries. Data request parameters do noth match with either of the previous two, and the Get function has to query the database again.

Example 4: Find by primary key after Get

There is one exception, though, to the strict requirement of exact match of all request parameters. BC data access mechanism is capable of resolving a situation where a single record must be retrieved based on the provided primary key or the SystemId value. Therefore, the following AL functions can use one query cache entry and will not requery the database if invoked interchangeably in any order:

• Record.Get

• Record.GetBySystemId

• Record.Find / FindSet / FindFirst / FindLast with filters on all primary key fields which resolve to a single record

Despite the fact that in normal circumstances all these functions would generate pretty different SQL queries, this does not matter for the cache access evaluation when the BC data access provider is confident that the query yields a single record.

Here, I will conclude the first part of the introduction into BC query cache. But there are many more things to pay attention to when speaking about caching. Cache request evaluation is based on many parameters, among which are:

• Find type

• Filters

• Sorting fields

• "Top N" clause

• Security filtering

• Isolation level

• Locking

• Loaded fields

Today, I only touched on the first two points of this list, and there are many more exciting things for the next post.