-1

What is the fastest way to find data in SQLCE in Windows Mobile (using C#)? I have a database with one million records. Is the fastest way an SQL query, a DataReader, or what?

Ricardo Altamirano
  • 14,650
  • 21
  • 72
  • 105
Gold
  • 60,526
  • 100
  • 215
  • 315

3 Answers3

2

By far the fastest way is to not use the query processor at all. Index the table to the field you want to search on and then use a SqlCeCommand with TableDirect and open a reader. Just adding the query procesor makes it an order of magnitude slower.

ctacke
  • 66,480
  • 18
  • 94
  • 155
1

Use an index for your where clause and a SqlConnection.

James Black
  • 41,583
  • 10
  • 86
  • 166
  • While I'm speaking out of my knowledge zone since it's mobile (assuming this exists and is the same) make sure you use ExecuteReader or the similar method that you can read from a DataReader instead of a DataSet/DataTable as they are extremely ineffecient compared to the Reader. Make sure you keep all of your readers and sql connections in `USING()` constructs so that they are properly released too. – Chris Marisic Nov 06 '09 at 18:45
  • To connect you can look at http://stackoverflow.com/questions/793128/connecting-to-sql-ce-db-using-sqlconnection and http://stackoverflow.com/questions/1125846/whats-wrong-with-this-sqlce-query – James Black Nov 06 '09 at 19:11
  • Just useing a WHERE clause means you're using the query processor, which is slow. – ctacke Nov 06 '09 at 20:29
  • If you are using any database you need a WHERE clause, otherwise you are selecting everything. If you are using indexes in your where clause it will be very fast, faster than parsing a file yourself to find the data. – James Black Nov 06 '09 at 21:50
0

In my tests the single fastest way is to treat SQLCE like old-style dBase;

// Get a cached Select Command
SqlCeCommand command = this.selectCommand;
// Tell it to match the first value you are searching for (having already set IndexName on the command 
command.SetRange(DbRangeOptions.Match, new object[] { key }, null);
// Read a single row
SqlCeDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SingleRow);
object value = null;
// Read your value by column index.
for (int i = 1; reader.Read(); i++)
{
    value = reader[1];
}
PhillipH
  • 6,182
  • 1
  • 15
  • 25