Fastest way to query SQL Server CE database file in Windows Mobile 6.5?

Question

I have a large .sdf database file on windows mobile device that I need to query. The file has 40,000 records. I am trying to cut the time it takes to execute the query. Currently it is about 8 seconds, which is a lot of time for user to wait for the results.

At first I been using DataSet, by establishing a SqlCeConnection and filling adapter linked to dataset. Also tried to similar with DataTable, performance results were almost identical. Using DataSet or DataTable finished query in about 8 seconds.

The last thing I tried was just using Reader(). It give me a little better results, but only by half a second (query is finished in ~ 7.5 seconds).

string lastName;
lastName = "";
string connectionString = @"Data Source='/Path/To/MyDatabase.sdf' Max Database Size = 128; Max Buffer Size = 1024;";
string strSql = "SELECT LastName FROM employee_list WHERE LastName = 'Johnson'";

using (SqlCeConnection mConnection = new SqlCeConnection(connectionString))
{
    mConnection.Open();

    using (SqlCeCommand mCommand = new SqlCeCommand(strSql, mConnection))
    {
        using (SqlCeDataReader reader = mCommand.ExecuteReader())
        {
            while (reader.Read())
            {
                lastName = (string)reader["LastName"];
            }
        }

        mCommand.Dispose();
    }

    mConnection.Close();
    mConnection.Dispose();
}

Any ideas how can I make this faster?

Answer 1

I had a similar issue, though on iOS, where we needed to query over 100,000 records. Originally we were using a technology ... similar to an entity framework in the Windows world (known as CoreData) ... that was taking over 15 seconds to execute (over 20 fields in the query). We changed this such:

1) It now uses SQLite directly for a 2 part query. Part 1 only queries 3 fields, and it's indexed.

2) Part 2 is a subsequent query that runs which grabs the "real details" the user is interested in.

This worked for us because the user first sees a only a list, and doesn't need the full dataset at this point. It's only when selecting specific rows do they require the full "part 2" query to be executed.

Don't know if this helps you but it cut our query time down to ~ 1100 ms, which was a huge improvement in our particular case.