OHDSI Home | Forums | Wiki | Github

Question about adding caching to a MS Sql Server database: Are there off the shelf caching tools that sit on top of MS Sql Server that others have used in the past with success?

Implementers
#1

Hi Everybody,

Looking for a little help from the DBA and IT folks in the crowd. I’ve just ported the project I’m working on over to MS Sql Server. During one of my processes I make thousands of requests to the concept and the concept relation table (to get standard codes from non standard codes). These queries were under a second but not by much in some cases and added up to a significant delay that was not acceptable. I was able to write my own caching layer and seize the day, however, I’d like to think there’s something already out there that might be a little more tried and true.

Are there off the shelf caching tools that sit on top of MS Sql Server that others have used in the past with success? Bonus points for ease of install/configuration/maintenance and feeding. More bonus points for free (or it is already built into MS Sql Server somehow?).

Edit: My project is written in Java

Thanks

#2

Not a caching scheme, but a way to limit the number of rows searched when mapping to standard concepts. sandbox/CDMv5/source_to_concept_map_5_with_maps_to_value.sql

1 Like
#3

For performance, you want to limit the number of requests you make to the database.
Option 1: pull in your lookups locally with one request. You don’t need a dedicated caching layer if it is only about standard concept looks. Just create e.g. a HashMap in memory of all required source_code : target_concept_id pairs.
Option 2: if your source data is in a database, it is best to do all lookups in one request with one or more joins. The database management system will then handle how to do the lookup efficiently. It might help to have indices on the right columns. The OMOP vocabularies already come with these indices, so a join on concept and concept_relationship table should be fast.

1 Like
t