Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008

    Unanswered: SQL Server 2008 caching

    I have a SQL Server 2008 R2 database and a COM API that I need to call to access the data. The API is not efficient at all. We have to perform a multitude of calls (which turns into a multitude of queries) to get the data we need. The database is static and will never be updated, and we cannot make any stored procedures or queries on our own - so we're stuck with the API, which writes dynamic SQL. The vendor claims there is no other alternative to get the data. An example of what the API is doing is -
    Search the grandparent table by string
    Get all parents with the parentId =
    Get all children with the parentId = (and do this for all parents)
    Get all properties of the children
    Find the single property matching a value of "O"

    Numerating through this algorithm 300+ times in some searches results in a slow return time - 15-17 seconds in the worst case. Searches with small results are okay - less than 200 ms to return.

    The question is - is there a way to cache some of this data for improved performance? I would guess the same exact text queries will be run over and over, depending on the user, but there are 26^n combinations of text searches. I was hoping to figure out a way to cache the data but I'm not sure if this is even possible considering this scenario.

    Thanks a lot in advance.

  2. #2
    Join Date
    Mar 2013
    Why not query the data, then store it in your own DB, and create whatever objects you like to query that data, unless there are licensing restrictions from doing so.
    Sounds like Tamale to me.

  3. #3
    Join Date
    Dec 2008
    Thanks for the reply. There are licensing restrictions so persisting the data in another database is not possible. We're pretty much caught in a bind, I'd say!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts