Write Blazing Fast SQL in MAUI

Improve database reads by 300-500% in MAUI apps that use sqlite-net.

This is achieved through by-passing the use of reflection to map a tables columns to an objects properties.

The source code for this can be found at:

On a Pixel 6, in release mode, the query changes yield the following results:

Query	ORM Mapped Queries	Manually Mapped Queries
Fetch Artists	~10ms	~3ms
Fetch Albums	~16ms	~4ms
Fetch Tracks	~240ms	~35ms

Mapped queries also have the following benefits:

Less Memory Churn: By avoiding reflection, less objects are created and the garbage collector less likely to be triggered.
Less CPU pressure: Reflection requires more "work" to figure out the property types and then do the property mapping.

This approach has the following drawbacks:

Data retrieval code may become brittle and inflexible. If you add a new column/property, you must update the associated mapper.
Requires deterministic order of column retrieval; the mapping code is tightly coupled to the order that columns are retrieved.

This approach is RECOMMENDED if:

Your ORM models are "well-baked" and rarely change.
You have identified your app requires optimisation.
You are willing to accept some technical debt to achieve a significant performance jump.
You have unit tests in place to prevent regressions that this code may introduce.

This approach is NOT RECOMMENDED if:

Your ORM models are rapidly changing.
Your app is young and rapidly changing.
You do not have unit test coverage to prevent regressions.
Your data retrieval code is not isolated into a data layer via the Repository Pattern.

Example

We can use SqlHelper.ExecuteCancellableQuery and provide a Mapper function:

public List<Album> GetAlbums()
{
    const string query = $"SELECT AlbumId, Title, ArtistId FROM {nameof(Album)}";

    return SqlHelper.ExecuteCancellableQuery<Album>(connection,
                                                    query,
                                                    emptyParameters,
                                                    MapAlbum, // This function accepts a sqlite3_stmt statment and does manaul mapping 
                                                    CancellationToken.None);
}

private Album MapAlbum(sqlite3_stmt statement)
{
    // Use the appropriate sqlite read to specifical retrieve each column and then directly map via `new Album`.
    var albumId = SQLite3.ColumnInt(statement, 0);
    var title = SQLite3.ColumnString(statement, 1);
    var artistId = SQLite3.ColumnInt(statement, 2);

    return new Album()
    {
        AlbumId = albumId,
        ArtistId = artistId,
        Title = title
    };
}

matthewrdev/SqlLitePerf.Maui

Write Blazing Fast SQL in MAUI

Example