每次迭代 SQL 查询都会变慢? [英] SQL Query gets slower with each iteration?

问题描述

有没有办法让这个 SQL 查询更快?它在 .sdf 数据库中搜索匹配的词.数据按字母顺序排列.目前搜索大约需要 10-15 秒，搜索的第一次迭代似乎相当快但是每次迭代搜索变得更慢?为什么是这样?抱歉，这是我第一次使用 SQL.

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);
}

推荐答案

一些提示:

1. 不要在查询中使用 *，而是使用所有字段名称.

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

在 Melted 字段上创建索引.

Create an index on Melted field.

在创建连接对象和命令之外构建查询

Build your query outside of creating Connection object and Command

在代码开头不需要时打开连接

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

如果你考虑这个技巧，你的代码会变得更快.

If you consider this tips your code become faster.

这篇关于每次迭代 SQL 查询都会变慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！