How to optimize database performance when providing results for autocomplete / iterative search?

Question

Note. In this question, I use the term “autocomplete” (or “iterative search”) to refer to returned search results by type, for example. how google search gives you. Also my question doesn’t apply to web apps and fat clients.

How are SQL SELECT queries typically created to provide decent performance for this type of query, especially over arbitrarily large datasets? In the case where the search will only query based on the first n characters (the simplest case), I still give a new SELECT FROM sometable WHERE entry LIKE ... result on every key press. Even with various forms of caching, it looks like it can lead to poor performance.

In cases where you want your search string to return results with prefix matches, substring matches, etc., this is an even more complicated problem. When considering a contact list search case, you can return results matching FirstName + LastName, LastName + FirstName, or any other substring.

Answer 1

Searches such as Google, Yahoo, etc., use full text indexes to generate a list of high-performance keywords.

If you iterate through single-word columns, you won’t need full text indexes and keywords. You can use LIKE for the indexed columns themselves.

Since you search by type, you only use the prefix. Your indexed columns will still get normal performance with a LIKE clause and wild card doing the "prefix".

SELECT last_name FROM users WHERE last_name LIKE 'Adam%'

If you need to search from the other end, you will need a reverse index, but, fortunately, people do not dial back.

SELECT " ", . , . , LIMIT TOP, , 10 . , 10 .

SELECT last_name FROM users WHERE last_name LIKE 'Adam%' LIMIT 10

, _ . . , .

, , , , .

SELECT first_name FROM users WHERE last_name LIKE 'Adam%' LIMIT 10

(last_name, first_name).

- . , .

Answer 2

" ", Sql, .

select Emp_ID,Emp_Name,Father_Name,Email from Employee where Emp_Name like '" + textBox1.Text + "%' ORDER BY Emp_Name ASC 

, - # ( Sql). , textBox1, dataGridView . textBox1_TextChanged, " ". , ...

 private void textBox1_TextChanged(object sender, EventArgs e)
{
string query = "select Emp_ID,Emp_Name,Father_Name,Email from Employee where Emp_Name like '" + textBox1.Text + "%' ORDER BY Emp_Name ASC";
using (SqlCommand comand = new SqlCommand(query, con))
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comand;
DataTable ds = new DataTable();
ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
da.Fill(ds);
dataGridView1.DataSource = ds;
}
}