R – How to improve asp.net AJAX autocomplete performance

active-directoryajaxasp.netlucenelucene.net

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!

Best Answer

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.