Improve [Ignore] attribute behavior when querying with JOINs

[Robert Nitsch]

I am currently optimizing slow database queries. Most of the CPU time is lost due to sub-sequent calls to GetChild. E.g. my code looks like this:

var allStudents = this.databaseService.SqLiteConnection.Query<Student>("SELECT s.* FROM Student");
foreach (var student in allStudents)
{
    this.databaseService.SqLiteConnection.GetChild(student, s => s.Photo);
    if (withPortfolio)
    {
        this.databaseService.SqLiteConnection.GetChild(student, s => s.Portfolio);
    }
}

The foreach-loop was supposed to speed up things by only retrieving the children that are required. However I have noticed that 90% of the time is lost in the foreach-loop, because additional queries are performed for each student.

I had the following idea to fix this:

• Add "JoinedPhotoData" and "JoinedPortfolioData" properties to the Student class and assign the [Ignore] attribute to these properties (to ensure that no such columns are created in the database tables).
• To the SELECT query above, add JOINs on the Photo and Portfolio tables and select the Photo data and the Portfolio data as additional columns (named like the properties added to the Student class).

E.g. the query would look like this (only showing JoinedPhotoData here):

SELECT s.*, p.JoinedPhotoData AS JoinedPhotoData
    FROM Student AS s
    JOIN Photo AS p ON p.Id == s.PhotoId

My hope was that the Query function would merge the "JoinedPhotoData" result column into the Student objects.

But it does not work, because it seems that the [Ignore] attribute does not only exclude the properties from the table creation process, but it also disables processing the respective columns retrieved via manual SQL queries.

I propose to add a parameter to the Ignore attribute to control the behavior in combination with the Query function.

Last but not least I would like you to ask if you have a better idea for optimizing my query performance.

[Guillermo Gutiérrez]

I have no control over the treatment that sqlite-net does on his Ignore attribute. However, you can improve your query in these ways:

1. Perform a single JOIN query and map it manually back to the Student
2. Perform a single JOIN query and map to an intermediate object, StudentWithPhoto for example, and then map manually back to the Student. var allStudents = this.databaseService.SqLiteConnection.Query<StudentWithPhoto>("SELECT s.*, p.JoinedPhotoData AS JoinedPhotoData FROM Student AS s JOIN Photo AS p ON p.Id == s.PhotoId");
3. Perform a two-step query using automatic mapping, like this:

var allStudents = this.databaseService.SqLiteConnection.Query<Student>("SELECT s.* FROM Student");
var photoIds = allStudents.Select(s => s.PhotoId).toList();
var placeHolders = string.Join (",", Enumerable.Repeat ("?", photoIds.Count));
var query = string.Format ("SELECT * FROM Photo WHERE [{1}] in ({2})", placeHolders);
var photos = conn.Query<Student>(query, photoIds);

[Robert Nitsch]

Thanks for your advice.

Is there an automated way of mapping the query result data to an object's properties? I'm asking because my Student class has a huge number of properties and manual mapping would be quite bothersome and would require lots of maintenance (e.g. everytime a property is added in the future).

If not, I can probably work around this issue with your second proposal. I think I just have to make StudentWithPhoto a subclass of Student.

[Robert Nitsch]

I have found a feature request for an IgnoreForCreate in the sqlite-net project: https://github.com/praeclarum/sqlite-net/issues/72

This is exactly what I need. Therefore I will close this issue.

Of course I would appreciate if you could still answer my last question about the mapping of the query result data to an object's properties.

[Guillermo Gutiérrez]

You could use the sqlite3 library directly to perform raw queries or you can create your own TableMapping subclass and call List<object> Query (TableMapping map, string query, params object[] args) method with it.

However any other option is easier to maintain.