[thelist] Big drop-down lists.

Francesco francesco at blackcoil.com
Tue Sep 7 22:30:20 CDT 2010

I built an "auto-complete drop down" control for a client 2 years ago
that has been implemented on over 150 distinct sites, all querying the
same SQL database (or cached data sets, for performance enhancement).
It's basically a medical term search engine where users type in things
like "heart disease" "osteoporosis" "chest pain" and get an
autocomplete drop down that updates via Ajax on every character typed
after the first 2 characters, retrieving a fresh set of matching
keywords, along with a count(*) of results for that keyword.  VERY
similar, and actually modeled after, Google Suggest.

I would highly recommend this design. Customers love it, users love
it, it's very speedy if the indices are designed well and EXTRA speedy
if you cache data.  I'm talking a set of 25,000 medical articles in
XML in a SQL database, and about 250,000 tags (keywords) that are
searched as the user types characters.  My goal was to get my control
to update itself AS FAST as Google Suggest, and it's pretty damn

Francesco Sanfilippo, Microsoft ASP Insider
mobile: 702.272.8912 | francesco at blackcoil.com

On Tue, Sep 7, 2010 at 8:06 PM, Bill Moseley <moseley at hank.org> wrote:
> Working on a few new pages and the designers want to provide users with a
> drop-down box to select from a list of items.  The vast majority of current
> users have just a handful of items to select from -- most less than 100.
>  Moving to testing we find out that a very small number of users have a huge
> number of items -- some have a 100,000+ items.
> Obviously, populating the list with AJAX request is the correct approach to
> prevent transfer huge amount of data to the client.  But, there's no real
> point of providing a list that someone can scroll though for thousands and
> thousands of rows.  Who's going to do that?
> I realize this is a somewhat open-ended question, not to mention short on
> specifics, but how do you handle a situation like this where the design
> calls for a drop-down for 99% of the users, but for 1% it's just
> not feasible and you don't know there's too many until you query the
> database.
> Would you do a count(*) first and then if over some number just offer up,
> say, an ajax search box?  Even then a search might pull up too many.
> Just run the query to select the list but use a LIMIT and if over some
> "reasonable" length of a list add a "See more..." to the bottom of the list
> that goes to a page that's better designed for managing that many rows?
> If there's a natural order then just LIMIT to some number and say/document
> that only displays 100(?) most recent items?
> Sure, we will look at the database to see what performs well, but I'm just
> curious if you have a general approach to this kind of issue.
> --
> Bill Moseley
> moseley at hank.org
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !

More information about the thelist mailing list