Improving AJAX autocomplete performance

Question

I've a US city/state list table in my sql server 2005 database which is having a million records. My web application pages are having location textbox which uses AJAX autocomplete feature. I need to show complete city/state when user types in 3 characters. For example: Input bos.. Output:Boston,MA

Currently, performance wise, this functionality is pretty slow. How can i improve it?

Thanks for reading.

Answer 1

Have you checked in the indexes on your database? If your query is formatted correctly, and you have the proper indexes on your table, you can query a 5 million row database and get your results in less then a second. I would suggest to see if you have an index on the City with added column State onto the index. That way when you query by city, it will return both the city and state from the index.

If you run your query in sql management studio and press ctrl-m you can see the execution plan on your query. If you see something like table scan or index scan then you have the wrong index on your table. You want to make sure your results have an index seek, this means that your query is going through the proper pages in the database to find your data.

Hope this helps.

Answer 2

My guess would be that the problem you're having is not the database itself (although you should check it for index problems), but the amount of time that it takes to retrieve the information from the database, put it in the appropriate objects etc., and send it to the browser. If this is the case, there aren't a lot of options without some real work.

1. You can cache frequently accessed information on the web server. If you know there are a lot of cities which are frequently accessed, you can store them up-front and then check the database if what the user is looking for isn't in the system. We use prefix trees to store information when a user is typing something and we need to find it in a list.

2. You can start to pull information from the database as soon as the user starts to type and then pair down full result set down after you get more information from the user. This is a little trickier, as you'll have to store the information in memory between requests (so if the user types "B", you start the retrieval and store it in a session. When the user is done typing "BOS" the result set from the initial query is in memory temporarily and you can loop through and pull the subset that matches the final request).

Answer 3

Use parent child dropdowns