Reading data
Reading data in AL covers how to retrieve records from Business Central tables efficiently. It answers questions about Get, Find, FindSet and Next, partial records, record isolation levels, SQL performance of database methods, and read scale-out.
Path: Development > Programming in the AL language > Tables and data > Reading data · tier official · system development · narrative reviewed (checked by Opus)
Overview
This section explains how AL code reads data and how to do it with less load on SQL Server. It starts with the basic retrieval methods (Get, Find, FindFirst, FindLast, GetBySystemId, Next). It then moves to performance guidance: which database methods suit which scenarios, why CalcFields and CalcSums need separate SQL unless results are cached, and how SetAutoCalcFields, ModifyAll and DeleteAll help performance.
The other pages cover three ways to reduce cost further. Partial records load only selected fields and load the rest just-in-time; there is a usage page and a FAQ. Record instance isolation level limits locking on individual record instances. Read scale-out sends read-only workloads, such as analytical queries and OData GET requests, to a read-only database replica.
Start with “Get, Find, and Next methods” for the basics, then read “AL database methods and performance on SQL Server”. Use the partial records, isolation level and read scale-out pages when tuning specific reports, pages or integrations.
Key points
- Get, Find, FindFirst, FindLast, GetBySystemId and Next cover key-based and filter-based record retrieval in AL.
- FindSet and Next suit looping over records; CalcFields and CalcSums need separate SQL unless cached.
- SetAutoCalcFields, ModifyAll and DeleteAll are named as ways to improve performance.
- Partial records load a subset of fields using SetLoadFields, AddLoadFields, SetBaseLoadFields and LoadFields; AreFieldsLoaded checks the loaded status.
- Unselected fields load just-in-time when accessed; the FAQ mentions an Enable Partial Records server setting and table extension optimization.
- Record instance isolation level (ReadIsolation) offers Default, ReadUncommitted, ReadCommitted, RepeatableRead and UpdLock to limit locks.
- Read scale-out uses the DataAccessIntent property to run read-only workloads on replicas, and the intent can be overridden at runtime.
Learn pages
- AL database methods and performance on SQL Server: Read about the relationship between basic database methods in AL and SQL statements in Business Central.
- FAQ for Partial Records: Answers some of the most typical questions about the partial records capability in Business Central
- Get, Find, and Next methods: Learn about the Get, Find, and Next methods for searching records in Business Central.
- Record instance isolation level: Learn how to set the isolation levels used when querying the Business Central database.
- Using partial records: Describes the partial records capability in Business Central.
- Using Read Scale-Out for Better Performance: Learn how to use read scale-out in Business Central to improve performance
Videos, posts and code changes
Linked by a Haiku matcher with a grounding quote from the item’s summary (link/topics.ts); machine-generated.
- #11847 Use matching key for workflow event step instance lookup (code change): “A workflow step instance lookup key is redefined to match the actual filter and sort order”
Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.