Instant search considerations

Question

Hi,

I've started working on a basic instant search tool.

This is a workflow draft.

1. User presses a key
2. Current value gets passed to the function which will make an Ajax call to a web service
3. Web service will run a select on a database through LINQ-To-SQL and will retrieve a list of values that match my value. I will achieve this by using SQL Like clause
4. Web service will return data to the function.
5. Function will populate relative controls through jQuery.

I have the following concerns/considerations:

Problem: Fast typists: I have typed in this sentence within few seconds. This means that on each key press I will send a request to a database. I may have 10 people doing the same thing. Server may return a list of 5 records, or it may return a list of 1000 records. Also I can hold down a key and this will send few hundred requests to a database - this can potentially slow the whole system down.

Possible solutions:

1. Timer where I will be able to send a request to database once every 2-4 seconds
2. Do not return any data unless the value is at least 3 characters long
3. Return a limited number of rows?

Problem: I'm not sure whether LINQ-to-SQL will cope with the potential load.

Solution: I can use stored procedures, but is there any other feasible alternatives?

I'm interested to hear if anybody else is working on a similar project and what things you have considered before implementing it.

Thank you

Answer 1

When to call the web service

You should only call the web service when the user is interested in suggestions. The user will only type fast if he knows what to type. So while he's typing fast, you don't have to provide suggestions to the user.

When a fast typist pauses for a short time, then he's probably interested in search suggestions. That's when you call the web service to retrieve suggestions.

Slow typists will always benefit from search suggestions, because it can save them time typing in the query. In this case you will always have short pauses between the keystrokes. Again, these short pauses are your queue to retrieve suggestions from the web service.

You can use the setTimeout function to call your web service 500 milliseconds after the user has pressed a key. If the user presses a key, you can reset the timeout using clearTimeout. This will result in a call to the web service only when the user is idle for half a second.

Performance of LINQ-to-SQL

If your query isn't too complex, LINQ-to-SQL will probably perform just fine.

To improve performance, you can limit the number of suggestions to about twenty. Most users aren't interested in thousands of suggestions anyway.

Answer 2

Consider using a full text catalog instead of the like clause if you are searching through blocks of text to find specific keywords. Besides being much faster, it can be configured to recognize multiple forms of the same word (like mouse and mice or leaf and leaves).

To really make your search shine, you can correct many common misspellings using the levenshtein distance to compare the search term to a list of similar terms when no matches are found.