High Speed access to Cached Data RRS feed

  • Question

  • User-750935311 posted

    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?

    Tuesday, February 11, 2014 6:39 PM


  • User-1023471226 posted

     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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 11, 2014 10:45 PM