lundi 28 mars 2016

dictionary application with suggest by c# and sqlite search fulltext so slow

I'm writing a dictionary application by C# and SQLite DB. When i edit the search textbox it will list the similar word in a listbox. It can work but speed so slow, running performance so slow. Should i use a thread or use better algorithm. I was created index in my DB, this table have 100.000 records. How to make the performance better? please help me. Thanks!

listSuggest is a ListBox.

private void txtKeyword_TextChanged(object sender, EventArgs e)
{
    try
    {
        conn = new SQLiteConnection(connectString);
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
            string sql = "select word from mytable where word like '" + txtKeyword.Text + "%' or search like '" + txtKeyword.Text + "%' order by id asc limit 0,200";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                listSuggest.Items.Add(reader["word"].ToString());
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Aucun commentaire:

Enregistrer un commentaire