ORM Layer Performance

Feb 16, 2026
8 minutes to read

This article explains how to fix the most frequent SQL-related performance issues caused by ORM data model design, business logic, or UI settings.

Use the diagnostic information from the Database Performance help topic to analyze the code of ORM persistent classes as described in this topic.

ORM Best Practices

Follow the best practices for your ORM to achieve the best performance results.

If your code already follows these best practices, review SQL queries with the highest call count and/or execution time. Note that if SQL query execution takes up the most time in end-user operations, then SQL query optimization must be performed.

Note

We do not recommend using SQLite with XAF in production scenarios with large amounts of data. SQLite is a file-based database that, due to its design, is inferior in performance and other features to powerful distributed server-based RDBMSs such as SQL Server, Oracle, MySQL, or PostgreSQL (used by the majority of our customers). If you experience performance issues with SQLite and other file-based databases (even with our Server Mode data sources), we cannot offer any suitable solutions other than switching to another RDBMS.

Troubleshooting

Check this list of the most frequent causes of performance issues and make sure you apply all recommendations from this list.

A ListView loads data slowly if the corresponding database table contains more than 100K records (or more than 10K records with complex structure).

Solution 1: If you do not need to show all records simultaneously, add a server-side filter to your ListView.

Solution 2: For Grid List Editors, set List View Data Access Modes to values other than Client. For Pivot Grid List Editors, see Ways to improve Pivot Grid List Editor or PivotChart performance with large amounts of data.

For more information, refer to the following video: Data Access in DevExpress XAF & ORM-related Performance Considerations - Data Access Modes in ListView - DEMO.

The main SELECT query takes a long time to execute, although not many rows are returned.

This may occur if each persistent object contains many fields with images, long text, references to complex persistent objects, etc.

Solution 1: For List Editors, set the ListView’s DataAccessMode to DataView, ServerView or InstantFeedbackView to load only required properties (by default, all object properties are loaded except for collections). For reports, use ViewDataSource.

Solution 2: Enable Delayed Loading for BLOB and complex reference properties. Note that delayed properties should not be displayed in the ListView.

XPO or EF Core sends additional queries to load associated (referenced) objects.

For XPO, this behavior is expected; it loads related object IDs in the main query, and then loads all of these objects in the second query by these IDs. In most cases, this behavior should not cause performance issues. If it does, there are two ways to address these issues:

Solution 1: Include referenced objects in the main SELECT query by applying the ExplicitLoading attribute to the corresponding properties.

Solution 2: If related objects are not used in list views, you can use Delayed Loading for them.

In most cases, all referenced objects of the same type are loaded through a single additional query for all records. If additional queries are performed for each record, see the next case.

For EF Core, review the following topics:

XPO or EF Core executes a separate query or multiple queries for each record.

For more information, see:

To resolve such issues, see what additional queries are executed and analyze your business class to understand what code causes this. Below are the most common cases.

Additional queries load a property of the current business class that is not loaded in the main SELECT query.
Additional queries select data from other tables according to a PersistentAlias expression.
Additional queries are executed for an unknown reason.

The applications frequently perform the same queries that return the same database records.

Solution: If these records are rarely changed, it makes sense to enable caching at the Data Layer level to prevent repetitive requests.

An XPO-based WinForms app repeatedly creates and closes database connections on performing asynchronous operations in Instant Feedback mode.

Solution: Make sure data store provider’s connection pooling is enabled in Startup.cs:

csharp

builder.ObjectSpaceProviders
    .AddSecuredXpo((application, options) => {
        //...
        options.EnablePoolingInConnectionString = true;
    })

Validation may take significant time when an object that must be saved exposes a large collection of aggregated objects.

Solution: Aggregated objects are integral to a master object and should be validated together. The PersistenceValidationController loads the entire aggregated collection in such cases. Refer to the following article for more information and possible solutions: Validation performance - PersistenceValidationController and the Aggregated attribute trigger the selection of child records one at a time when the master object is saved.

Your persistent classes have many reference and collection properties (or the entire object graph is complex), and thus the profile confirms excessive JOIN/additional queries.

Consider if you can denormalize your database table or map your persistent class to a database view with only required columns from your database table. In the UI, you may lose the capability to navigate to certain reference sub-properties, but this may be unimportant for users (so the changes are justified).

This article lists only the most common performance issues. You can also encounter issues related to a certain database provider or legacy database, scenario-specific issues, and so on. We recommend that you find out how the query or the database table can be modified to improve performance, and then try to modify your persistent objects accordingly.

Next Steps

If you still experience performance issues after you followed the advice in this topic, review the next article in our optimization guide: Application Performance.