Autocomplete Optimization for Large Datasets

Question

I am working on a large project where I have to present an effective way for a user to enter data into a form.

Three of the fields in this form require a value from a subset of a common data source (SQL table). I used jQuery and the jQuery user interface to create autocomplete that is sent to the shared HttpHandler.

Internally, the handler uses Linq-to-sql to capture the data needed from this particular table. The table has about 10 different columns, and the linq expression uses SqlMethods.Like () to match one search term in each of these 10 fields.

The problem is that this table contains about 20 thousand rows. Autocomplete works flawlessly, you must admit that a clean amount of data introduces losses in about 6 seconds or so (when debugging on my local computer) before it appears.

Autocomplete JqueryUI has 0 delay, key requests 3, and the result of the message is created in multiple-line Facebook style choices. (I almost had to rewrite the autocomplete plugin ...).

Thus, the problem is related to speed data. Any thoughts on how to speed this up? The only thing I had was to cache data (How / Where?); or use direct sql data reader to access data?

Any ideas would be greatly appreciated! Thanks,

<bleepzter/> 

Answer 1

I would only look at returning the first number of X lines using the .Take(10) linq method. This should translate into a secret sql call, which will result in significantly less load on your database. As the user enters them, they will find fewer and fewer matches, so they will only see the data that they need.

I usually think that 10 elements is enough for the user to understand what is happening and get to the required data quickly (see the amazon.com sample search string).

Obviously, if you can sort the data in a meaningful way, then 10 results will be much more believable to give the user what they are quickly.

Answer 2

Returning the results of the top N is a good idea. We found (requesting a potential list from 270K) that returning to the top 30 is the best bid for a user who is looking for what he is looking for, but is FULLY dependent on the data you request.

In addition, you REALLY have to discard the delay for something reasonable, such as 100-300 ms. When you set the delay to ZERO, as soon as you press the 3-character trigger, EVERYONE is effective. ONE. KEY. STROKE. sent as a new request to your server. This can easily lead to an unintended and undesirable effect of slowing the response even MORE.