Selecting Data in my sdf file is very slow

Question

I'am working on application on symbol Motorola device, with Windows ce 5.0 version, and I try to read a query from the "sdf" database with a simple select command. but the result came very very slow.

this is my code

private SqlCeConnection myCn;
private SqlCeCommand cm;
private SqlCeDataReader dr;

myCn = new SqlCeConnection("Data Source=\Storage Card\PDT_Data.sdf;Max Database Size=4091;Max Buffer Size = 1024;Default Lock Escalation =100;");
cm = myCn.CreateCommand();
cm.CommandText = "SELECT * FROM MainInputFile WHERE BARCODE = " +  txtBarCode;
dr = null;
if (myCn.State != ConnectionState.Open) myCn.Open();
dr = cm.ExecuteReader();
while (dr.Read())
{
 txtBarCode.Text = dr["BARCODE"].ToString();
 txtItemInformation.Text = dr["DESCRIPTION"].ToString();
 txtItemInformation.Text += "\r\n" + dr["BYUM"].ToString();
 txtItemInformation.Text += "\r\n" + dr["ITEMSKU"].ToString();
 txtItemInformation.Text += "\r\n" + dr["SALEPRIC"].ToString();
}

please any one can help me.

Answer 1

Consider adding index to your compact database, its actually easy:

string command = "Create NONCLUSTERED INDEX BARCODE_INDEX ON MainInputFile(BARCODE)";
SqlCeCommand cmd = new SqlCeCommand(command, GetConnection());
cmd.ExecuteNonQuery();

Database operations on devices are usually expensive, if you have a lot of data be sure to add index properly.

Also as Martheen pointed: separate your DB operations from UI.