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:

• SqlHelper.
• ChinookDatabase.

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 increased code complexity to achieve a significant performance jump in your database access.
• 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 manual mapping.
                                                    CancellationToken.None);
}

private Album MapAlbum(sqlite3_stmt statement)
{
    // Use the appropriate sqlite read to specifically 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
    };
}