ASP.NET super fast data search for autocomplete, please guide me

Thanks for your time and attention.

I have ajaxtookit Autocomplete and requrime开发者_如何学Pythonnt is to make it super fast. Currently data is in database and on every request it is fetched and come from database. This data are contacts and addresses and often new records will be added in the tables. There are 3 solutions that come to mind. Please guide me what you suggest and what you feel is the best.

1. Let the data in database, just do indexing and if possible do some connection polling so that connection establishing time can be saved.

2. Cache data in application server. Use Linq to query.

3. Do search on sorted data in some file.

Please guide and help me.

Thanks

---

If your data doesn't change very often, I'd go with server-side caching.

---

Here's a few tips:

• Use a stored procedure for the database work - optimize it as well as reasonably possible (use indexes, hints, views, etc). Using a stored procedure will allow the execution plan to be "cached" in the DB engine so it doesn't have to be re-calculated each time. Don't use LINQ or ADO.NET - these are provider translation overheads which you cannot afford in this particular scenario - go straight to native SQL. Using a stored procedure means your writing native SQL from the get go, as opposed to LINQ-SQL/LINQ-Entities where the expression tree needs to be translated to native SQL. You can't afford this overhead (you don't need to). Of course depending on your ORM (if you have one), there are steps to counter-act this such as compiled queries in Entity Framework.

• Cache Option #1 - "Recent Queries". We have a "SearchQuery" cache table. We keep track of searches, and what "matched" record is kept against it. (So "New York" might have "NY", "New York", "new york", "nyc" all related to it). This way, we don't have to keep on "figuring out" the matching record(s) for the autocomplete user query (however you do it, be it SQL Server FTS, a T-SQL LIKE, or basic LINQ/string operations). This can save you a lot of time. You can cache "the last 100 searches" in memory to add some bonus speed.

• Cache Option #2 - "Data". Cache the result of the database call on the web server. The cache key should match the parameters to the stored procedure.

• Cache Option #3 - "Output (JSON/XML)". I assume you are using ASP.NET Web Forms, and not MVC. So im guessing you have a ASMX/WCF Web Service responding to your auto complete calls. You can cache the output of this web method call, in addition to caching the actual data. Just be wary that combined output/data caching can cause "stale" data if the expirations aren't handled effectively.

HTH