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 = grandparent.id
Get all children with the parentId = parent.id (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.