Improving the performance of AJAX autocomplete
I have a US / State city table table in my sql server 2005 database that has a million records. My web app pages have a location text box that uses the AJAX autocomplete feature. I need to show the full city / state when the user enters 3 characters. For example: Input boss .. Output: Boston, MA
Currently, the performance is very low, this functionality is rather slow. How can I improve it?
Thanks for reading.
a source to share
Have you checked the indexes in your database? If your query is formatted correctly and you have the appropriate indexes on the table, you can query a database of 5 million rows and get your results in less than a second. I would suggest to see if you have a zip code in the city with the State column added to the index. So when you query for city, it returns 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 for your query. If you see something like a table scan or an index scan, then you have the wrong index on the table. You want your results to be indexed, which means that your query goes through the appropriate pages in the database to find your data.
Hope it helps.
a source to share
I am guessing that the problem you are having is not the database itself (although you should check it for index issues), but the amount of time it takes to fetch information from the database is put into the appropriate objects, etc. ., and send them to the browser. If so, there aren't many options without some real work.
-
You can cache frequently viewed information on a web server. If you know that there are many cities that are frequently accessed, you can save them beforehand and then check the database if what the user is looking for is not logged in. We use prefix trees to store information when the user is typing something and we need to find it in the list.
-
You can start fetching information from the database as soon as the user starts typing and then concatenate the complete result delivered after receiving more information from the user. This is a bit tricky as you need to keep the information in memory between requests (so if the user types "B", you start the search and store it in the session. When the user has finished typing "BOS", the result set from the original query is temporarily in memory and you can loop and pull the subset that matches the final query).
a source to share