How to improve asp.net AJAX autocomplete performance

Question

My web site has city,state and zip code autocomplete feature.

If user types in 3 characters of a city in the textbox, then top 20 cities starting with those characters are shown. As of now, Autocomplete method in our application queries sql 2005 database which has got around 900,000 records related to city,state and zip.

But the response time to show the cities list seems to be very very slow.

Hence, for peformance optimization, is it a good idea to store the location data into Lucene index or may be in Active directory and then pull the data from there?

Which one will be faster...Lucene or Activedirectory?And what are the pros and cons of each?Any suggestions please?

Thanks a bunch!

Answer 1

Taking a nuclear option (like changing backing data stores) probably shouldn't be the first option. Rather, you need to look at why the query is performing so slowly. I'd start with looking at the query performance in SQL Profiler and the execution plan in Sql Management Studio and see if I am missing anything stupid like an index. After you cover that angle, then check the web layer and ensure that you are not sending inordinate amounts of data or otherwise tossing a spanner in the works. Once you have established that you aren't killing yourself in the db or on the wire, then it is time to think about re-engineering.

On a side note, my money would be on Sql Server handling the data end of this task better than either of those options. Lucene is better suited for full-text searches and AD is a poor database at best.

Answer 2

I would cache the data into a separate table. Depending on how fresh you need that data to be, you can rebuild it as often as necessary.

--Create the table
SELECT DISTINCT city, state, zip INTO myCacheTable FROM theRealTable

--Rebuild the table anytime
TRUNCATE TABLE myCacheTable
INSERT INTO myCacheTable (city, state, zip) SELECT DISTINCT city, state, zip FROM theRealTable

Your AJAX calls can access myCacheTable instead, which will have far fewer rows than 900k.

Answer 3

Adding to what Wyatt said, you first need to figure out which area is slow? Is the SQL query slow OR the network connection slow between the browser and the server? OR is there something else?

And I completely agree with Wyatt that SQL Server is much more suitable for this task then Lucene and Active Directory.