开发者

SQL Query gets slower with each iteration?

Is there anyway to make this SQL query faster? It searches a .sdf database for matching words. The data is arranged in alphabetical order. Currently it takes about 10-15 seconds to search, the first iteration of the search seems to be rather quick however the search becomes slower with each iteration? Why is this? Sorry this is my first experience with SQL.

private void Search_Database() //Searches SQL Database for matching words
{
    Possible_Words.Clear();
    using (var cn = new SqlCeConnection(@"Data Source=|DataDirectory|\Resource\Wordlist.sdf"))
    {
        cn.Open();
        using (var cmd = cn.CreateCommand())
        {
            string[] Final_Search_Array = (string[])Packaged_Search_Array.ToArray(typeof(string)); // Receives  and Unwraps Packaged Array
            int Search_Array_Index = Final_Search_Array.Length - 1;
            for (; Search_Array_Index != -1; Search_Array_Index = Search_Array_Index - 1)
            {
                switch (Final_Search_Array[Search_Array_Index].Length)
                {
                    case 2:
                        Search_Table = "[2 Letter Words]";
                        break;
                    case 3:
                        Search_Table = "[3 Letter Words]";
                        break;
                    case 4:
                        Search_Table = "[4 Letter Words]";
                        break;
                    case 5:
       开发者_高级运维                 Search_Table = "[5 Letter Words]";
                        break;
                    case 6:
                        Search_Table = "[6 Letter Words]";
                        break;
                }
                cmd.CommandText = "Select * from " + Search_Table + " where [Melted] like '%" + Final_Search_Array[Search_Array_Index] + "%'";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Possible_Words.Add(reader["Word"].ToString());
                    }
                }
            }
        }
        cn.Close();
    }
    FullList_PossibleWords.Add(Possible_Words);
}


Some tips:

  1. Instead of using * in your query use all field names.

  2. Create an index on Melted field.

  3. Build your query outside of creating Connection object and Command

  4. Open connection when you need it not in beginning of your code

If you consider this tips your code become faster.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜