Generic asynchronous SQLite search builder

Question

I am writing a database manager that reads and writes to a local SQLite database on a mobile device. It works pretty well for the most part, but read access is somewhat slow - it takes about 2-5 seconds to load 250 to 500 records. I've already gone and made some improvements where I can, but I'm hoping that there is more I can do.

I am using the Mono.Data.Sqlite API as my database driver.

The following methods are meant to be used with generics. This means I need to use reflection in order to populate model properties (or at least that's the only way I can think of - let me know if there are better ways).

The FindAsync<T> method takes two arguments. The first is a string the represents the WHERE clause of a SQL query. This string can use a format similar to String.Format() to allow the user to specify custom parameters (instead of using {0} to mark a parameter, you would instead use @0). The second parameter is a list of objects that will be used as parameters in the WHERE clause. It returns a list of objects of type T for which the WHERE clause is true. For an example of how this method is called, please see the bottom of this question.

The type of T must be an IModel, which is an in-house interface that specifies objects that can be stored in the SQLite database. Though these objects do have a little bit of specialization, for the purposes of this review they are more or less just POCOs.

public async Task<IEnumerable<T>> FindAsync<T>(string whereClause, params object[] parameters) where T : IModel
{
    // This method will ensure the DB connection is valid (verifies DB exists, initializes Connection, etc...)
    VerifyInitializationStatus();

    IEnumerable<T> retList;
    using (var cmd = Connection.CreateCommand())
    {
        InitializeFindCommand<T>(cmd, whereClause, parameters);
        using (var reader = await Task.Run(() => cmd.ExecuteReader()))
            retList = ParseSqliteReader<T>(reader);
    }
    return retList;
}

private void InitializeFindCommand<T>(SqliteCommand cmd, string whereClause, object[] parameters)
{
    cmd.CommandText = String.Format("SELECT * FROM {0}{1}{2}",
            typeof(T).Name,
            whereClause.Length > 0 ? " WHERE " : "",
            whereClause);
    if (whereClause.Length > 0)
        InitSqlCommandParameters(cmd, parameters);
}

private void InitSqlCommandParameters(SqliteCommand cmd, object[] values)
{
    var index = 0;
    foreach (var param in values)
    {
        var p = cmd.CreateParameter();
        p.ParameterName = String.Format("@{0}", index);
        p.Value = param;
        cmd.Parameters.Add(p);
        index++;
    }
}

// According to my tests, this is the method that takes the most time 
// when accessing the DB.
private IEnumerable<T> ParseSqliteReader<T>(SqliteDataReader reader)
{
    var retList = new List<T>();
    var modelProperties = typeof(T).GetProperties();

    var propertyDict = new Dictionary<int, PropertyInfo>();
    for (var i = 0; i < reader.FieldCount; i++)
    {
        propertyDict.Add(i, modelProperties.First(p => p.Name == reader.GetName(i)));
    }

    while (reader.Read())
    {
        var model = (T)Activator.CreateInstance(typeof(T));
        for (var i = 0; i < reader.FieldCount; i++)
        {
            var property = propertyDict[i];
            var value = reader[i];
            property.SetValue(model, value == DBNull.Value ? null : value);
        }
        retList.Add(model);
    }

    return retList;
}

Here's some examples of how the FindAsync method is called/used:

// Look for all ExamplePersons that have a Name field of "Tom"
var searchName = "Tom";
var peopleNamedTom = await FindAsync<ExamplePerson>("Name = @0", searchName);

// Look for all ExampleProducts that are food and cost less than $5
var productType = "food";
var cost = 5;
var cheapFoods = await FindAsync<ExampleProduct>(
    "ProdType = @0 AND Cost < @1", 
    productType, 
    cost
);

So, to recap my main question - Is this an efficient way of reading in data from a SQLite database query? Is there any way I can speed this up?

Answer 1

Reflection is slow. That line probably doesn't help your performance :

var model = (T)Activator.CreateInstance(typeof(T));

I notice that you always create your instance with parameterless constructors. So you could add the type constraint new() in your class. That means you could do :

var model = new T();

That'd be faster and well... clearer.

You also use reflection to get properties. But you do it every time you make a query. You should cache this, after all, there's not much chances an object will gain properties during runtime :p (By caching, I mean adding your properties once in a static dictionary, so you won't need to get them twice or more.)

Overall, you're using reflection to map properties to your object, and that is probably the cause of your problem. You should use another solution. For example, I recently wrote a question about a property mapper using compiled Linq expressions. That solution is much faster than using reflection (like, way faster) but someone wrote an answer that proposes another solution I didn't explore yet that is even faster to map properties.

Overall, your performance problem is very probably related to the use of reflection!