Defining an efficient data access layer
When thinking about performance in an ERP solution, the first level that we need to evaluate is the data access layer. Are we creating extensions that access our data efficiently or inefficiently?
When we design a solution, we are often required to create tables and fields. To have the best performance, it’s required to also add indexes to tables according to the way your AL code will access the data.
A table object in AL must have a primary key (which uniquely identifies each record in the table) but it can also have many secondary keys (indexes on SQL). The primary key is composed of up to 16 fields in a record. It’s technically possible to create a primary key based on up to 20 fields in the development environment, but due to SQL Server limitations, only the first 16 are used.
In the SQL data layer, table extension objects inherit the primary key of the table object they extend (the base table object), and any key...
