SQL Query gets slower with each iteration?

Question

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

SqlParameter why waste precious CPU cycles trying to secure my code. — Jesus Ramos, Jul 23 '11 at 03:33
Jesus has a point (obtuse though it may be). Use `SqlParameter`s to build your command statement. Also, if you're re-opening your `SqlConnection` each time you iterate through a loop, you should put your `using SqlCeConnection` statement outside the loop. — Robert Harvey, Jul 23 '11 at 03:35

score 3 · Accepted Answer · edited Jun 06 '15 at 13:15

3

Some tips:

Instead of using * in your query use all field names.
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.

edited Jun 06 '15 at 13:15

shA.t

16,580
5
54
111

answered Jul 23 '11 at 04:09

Arian

12,793
66
176
300

1

Actually, with single user, in memory databases (SQL Compact at least) the general rules of connection handling do nor apply. There is a lot of overhead involved in creating them and they should be reused if possible. – Scott Munro Jul 23 '11 at 04:46
1

This SO questions covers this point. http://stackoverflow.com/questions/386223/sqlce-connections-keep-them-open-or-let-them-close – Scott Munro Jul 23 '11 at 05:02

SQL Query gets slower with each iteration?

1 Answers1

Linked