GetAllWithChildren() performance issue

Question

I used SQLite-Net Extensions in the following code to retrieve 1000 rows with their children relationships from an Sqlite database:

var list =
SQLiteNetExtensions.Extensions.ReadOperations.GetAllWithChildren<DataModel>(connection);

The problem is that the performance is awkward. Because GetAllWithChildren() returns a List not an Enumerable. Does exist any way to load the records in to an Enumerable using Sqlite.net extensions?

I now use Table() method from Sqlite.net, loads the fetched rows in to the Enumerable but I dont want to use it because it does not understand the relationships and does not load the children entities at all.

Answer 1

GetAllWithChildren suffers from the N+1 problem, and in your specific scenario this performs specially bad. It's not clear in your question what you're trying, but you could try these solutions:

Use the filterparameter in GetAllWithChildren:

Instead of loading all the objects to memory and then filter, you can use the filter property, that internally performs a Table<T>().Where(filter) query, and SQLite-Net will convert to a SELECT-WHERE clause, so it's very efficient:

var list = connection.GetAllWithChildren<DataModel>(d => d.Name == "Jason");

Perform the query and then load the relationships

If you look at the GetAllWithChildren code you'll realize that it just performs the query and then loads the existing relationships. You can do that by yourself to avoid automatically loading unwanted relationships:

// Load elements from database
var list = connection.Table<DataModel>().Where(d => d.Name == "Jason").toList();
// Iterate elements and load relationships
foreach (DataModel element in list) {
    connection.GetChildren(element, recursive = false);
}

Load relationships manually

To completely workaround the N+1 problem you can manually fetch relationships using a Contains filter with the foreign keys. This highly depends on you entity model, but would look like this:

// Load elements from database
var list = connection.Table<DataModel>().Where(d => d.Name == "Jason").toList();
// Get list of dependency IDs
var dependencyIds = list.Select(d => d.DependencyId).toList();
// Load all dependencies from database on a single query
var dependencies = connection.Table<Dependency>.Where(d => dependencyIds.Contains(d.Id)).ToList();
// Assign relationships back to the elements
foreach (DataModel element in list) {
    element.Dependency = dependencies.FirstOrDefault(d => d.Id == element.DependencyId);
}

This solution solves the N+1 problem, because it performs only two database queries.