SQL Server Compact ADO.NET data access performance – part 3: SELECTs

In the third instalment in this series, I will demonstrate how to implement SELECTs using the two available APIs in the SQL Server Compact ADO.NET provider. Keep in mind that the “alternative” method of SELECTing requires a corresponding index, so it will on work for simple scenarios where you are looking up on the primary key, for example – luckily, this is quite a common scenario.

I will implement the following interface method, which finds a cacheelement by it’s ID:

CacheElement FindElementByKey(Guid key);

Let’s first look at the implementation of the classic T-SQL SELECT based method:

public CacheElement FindElementByKey(Guid key)
{
    using (var command = _connection.CreateCommand())
    {
        command.Parameters.Add("@key", SqlDbType.UniqueIdentifier).Value = key;
        command.CommandText = @"SELECT [Tag], [Value], [CreatedAt], [ExpirationAt]
            FROM CacheElement
            WHERE [Key] = @key;";
        using (var reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                var result = new CacheElement();
                result.Key = key;
                result.Tag = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);
                result.Value = (byte[])reader[1];
                result.CreatedAt = reader.GetDateTime(2);
                result.ExpirationAt = reader.GetDateTime(3);
                return result;
            }
            else
            {
                return null;
            }
        }
    }
}

This implementation uses a standard T-SQL based parameterized SELECT query. Nothing new here.

Now let us have a closer look at the implementation using TableDirect mode:

public CacheElement FindElementByKey(Guid key)
{
    using (var command = _connection.CreateCommand())
    {
        command.CommandType = CommandType.TableDirect;
        command.CommandText = "CacheElement";
        command.IndexName = "PK_CacheElement";

        using (var reader = command.ExecuteReader())
        {
            reader.Seek(DbSeekOptions.FirstEqual, key);
            if (reader.Read())
            {
                var element = new CacheElement();
                element.Key = key;
                element.Tag = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);
                element.Value = (byte[])reader.GetValue(2);
                element.CreatedAt = reader.GetDateTime(3);
                element.ExpirationAt = reader.GetDateTime(4);
                return element;
            }
        }
        return null;
    }
}

Notice the required differences: The CommandType is set to TableDirect, the CommandText is set to the name of the table we want to query, and the IndexName property is set to the name of the index to be used. The Seek command will force a call to read to find the first matching column matching the key value. Notice that the key parameter is an params array, allowing you to use multi-column indexes.

In terms of performance, the classic SELECT appears to be 3 times slower than the TableDirect Seek, but of course your mileage may vary.

You can download the sample code from here.