Autocomplete Dropdown - too much data, timing out

Question

So, I have an autocomplete dropdown with a list of townships. Initially I just had the 20 or so that we had in the database... but recently, we have noticed that some of our data lies in other counties... even other states. So, the answer to that was buy one of those databases with all towns in the US (yes, I know, geocoding is the answer but due to time constraints we are doing this until we have time for that feature).

So, when we had 20-25 towns the autocomplete worked stellarly... now that there are 80,000 it's not as easy.

As I type I am thinking that the best way to do this is default to this state, then there will be much less. I will add a state selector to the page that defaults to NJ then you can pick another state if need be, this will narrow down the list to < 1000. Though, I may have the same issue? Does anyone know of a work around for an autocomplete with a lot of data?

should I post teh codez of my webservice?

Answer 1

Are you trying to autocomplete after only 1 character is typed? Maybe wait until 2 or more...?

Also, can you just return the top 10 rows, or something?

Answer 2

Stupid question maybe, but... have you checked to make sure you have an index on the town name column? I wouldn't think 80K names should be stressing your database...

Answer 3

Sounds like your application is suffocating on the amount of data being returned, and then attempted to be rendered by the browser.

I assume that your database has the proper indexes, and you don't have a performance problem there.

I would limit the results of your service to no more than say 100 results. Users will not look at any more than that any how.

I would also only being retrieving the data from the service once 2 or 3 characters are entered which will further reduce the scope of the query.

Good Luck!

Answer 4

I think you're on the right track. Use a series of cascading inputs, State -> County -> Township where each succeeding one grabs the potential population based on the value of the preceding one. Each input would validate against its potential population to avoid spurious inputs. I would suggest caching the intermediate results and querying against them for the autocomplete instead of going all the way back to the database each time.

Answer 5

If you have control of the underlying SQL, you may want to try several "UNION" queries instead of one query with several "OR like" lines in its where clause.

Check out this article on optimizing SQL.

Answer 6

I'd just limit the SQL query with a TOP clause. I also like using a "less than" instead of a like:

select top 10 name from cities where @partialname < name order by name;

that "Ce" will give you "Cedar Grove" and "Cedar Knolls" but also "Chatham" & "Cherry Hill" so you always get ten.

In LINQ:

var q = (from c in db.Cities
        where partialname < c.Name
        orderby c.Name
        select c.Name).Take(10);