views:

366

answers:

4

I have a search module with Auto Suggest feature to build in ASP.Net The search criteria is Training Name and there is a table in database that stores trainings. The size would be as large as 30,000 trainings in the table so I have to be very careful in selecting the approach keeping in mind the performance.

There could be about 3000 users logging in the system simultaneously. When the user starts typing a training name the system should autosuggest.

The approaches that came in my mind were as under

  1. Cache object - There would be a database hit after the user types 3 (e.g. saf) characters and the system would search the activity table for all trainings starting with saf and would cache them. The other requests would go thro this cache. But the problem with this approach would be if there are 3000 concurrent users using the system and if they all search for different combinations of 3 different letters the cache would just blow.

  2. Client side caching - Did not think much on this. The only drawback I see here is we might have to purge the temporary internet folder periodically.

  3. Using Session - I thought to rule this out completely as I thought it would hit performance.

Can you please suggest the best or any other different approach I can take here. I am looking for all information/ideas that you have on this.

Thank you so much

Deepa.

A: 

You could use the jQuery Auto Complete plugin, which has caching features built in.

$(document).ready(function()
{
        $(".landingpage").autocomplete('/AutoSuggestHandler.ashx',
  {
   minChars: 1,
   matchSubset: 1,
   autoFill: false,
   delay: 10,
   scroll: false
  }).result(OnResultSelected);
}

Furthermore, you could specify outpu caching on the generic handler, to accommodate the need of caching across users.

MartinHN
A: 

I think your first approach will work.

Make sure there is an index on the field - you probably won't need to index the whole field. This should give the database a decent boost. You may need to look at full text indexing depending on how your search works, or even use an external library like lucene for the index is performance is an issue.

Cache the object, or even the resulting xml/json from the queries to improve performance.

You should also set the http headers so that browsers cache the xml/json as well.

benlumley
+1  A: 

My favourite jQuery plug-in to do that (if you're in intent to use jQuery) is the Flexbox.

It has a really impressive list of features.

tanathos
A: 

Your posting really contains two questions:

  1. How can I get autocomplete on my webpage?
  2. I am concerned about performance due to a large number of queries hitting my database at the same time.

My answers...

1: We've found the ASP.NET AJAX AutoComplete Extender works well on all modern browsers, provides a slick user experience and is pretty easy to implement.

In your web application you need to create a web service that has a method with a specific signature (covered in the documentation linked to above).

2: Have you proven that you actually have a performance bottleneck with this part of your project? I'd recommend setting up a test harness and hitting your database with a large number of autocomplete queries to see how much it can take. Be wary of premature optimization.

Richard Ev
Have you run into problems with the Autocomplete extender when using the keyboard to select an item? On my current project, selecting an item with the enter key causes the first button on the page to postback. We're using panels, but no default button is set.
chris
No problems at all - we did a lot of testing using the mouse and just the keyboard. Do you have AutoPostback set to true on your TextBox?
Richard Ev
Nope, and what's strange is that if you remove a button from the page, it just picks the next available button.
chris
Odd indeed...have you tried starting with the sample app that comes with the AJAX Control Toolkit and seeing if that has the same issue if you start adding buttons?
Richard Ev