Last post Feb 11, 2014 10:45 PM by otnielpena
Feb 11, 2014 06:39 PM|Tualatin|LINK
I need to add new functionality to an existing application. I will use a static database with one table containing about 100,000 records: two fields (code and description). I seek a solution to auto-complete as users type into a text box - just like intellisense
or Google's search box. There is an async connection to the web server farm. The database can be located on each web server or seperately on another server. The web farm accesses a SQL database for dynamic data, and that server can not be burdened with
additional calls for the static data.
The user will start to enter codes or descriptions. As numbers are typed possible matches will pop-up. Probably it is best to search that user's history of previously entered codes first then the main cache if there is no match.
If searching Google for MICHAEL J, once the J is typed Google might suggest MICHAEL JORDAN and MICHAEL JACKSON. How do I pull this off
fast, like Google does? I can't cache 100,000 records on the client side. Possibly cache their history on the client. Should I convert the giant table to binary?
Feb 11, 2014 10:45 PM|otnielpena|LINK
The search criteria will change on every key down. So why would you save large amount of data every time a key is pressed? What you can do is make a search of the top (nth) elements that match with the current search criteretia. Using client side code you
can access the database fast every time the search criteria change. take the top(nth) elements and display them. That can be as faster as caching data that will change in seconds.
Another solution is saving in the cache part of the data that matches with the first terms of the search. For example, all the terms begining with certain number or letter.
Yoy can access a web metod from jquery that can use a <WebMethod(cacheduration:=120)> _. However, that method will save in the cache a result that will be valid for seconds. In the other hand, you can use a webmethod from jquery. that method can call a second
one that return the whole data and save it in the cashe for faster access. next time the data is accessed it search in the cache all the data instead of query the database. It is up to you determining whether the method calls increase or decrease performance
for the search instead of accessing the database every time.