Caching in Teiid

Teiid has several ways to improve performance by caching the data. The simplest approach is to provide a caching hint right in the SQL statement. The query result below will be cached for 1 minute, and stored preferably in memory. The subsequent selects using the same query return the cached results for 1 minute. It is important that query should be "string-equal" to retrieve the cached result. Teiid does not parse the query for performance considerations, and even a single extra space added to the query can force Teiid to get for a fresh result instead of cache. Also the same behavior happens if functions like now() used - the results are not cached.

 /*+ cache(pref_mem ttl:60000) */select id, value from test.table1;  
   
  id value  
  --- -----  
  1 80  
  2 20  
   
 update test.table1 set value = 10 where id = 2;  
   
 /*+ cache(pref_mem ttl:60000) */select id, value from test.table1;  
   
  id value  
  --- -----  
  1 80  
  2 20  
   
 select id, value from test.table1;  
   
  id value  
  --- -----  
  1 80  
  2 10  

Another approach is to use materialized views, or (simpler approach) a "lookup" function. This function useful to create a system-wide cache on some dictionary and perform a search. The results are cached until Teiid restarted, so it might not be convenient to keep a changing data.

 select lookup('test.table1', 'value', 'id', 1);  
   
 expr1  
 -----  
 80  
   
 update test.table1 set value = 50 where id = 1;  
   
 select lookup('test.table1', 'value', 'id', 1);  
   
 expr1  
 -----  
 80  

And the most flexible way to operate a cache is to use a custom translator with support of Caching API.
This is the overridden method from CacheTestExecutionFactory which defines the caching approach for translator:

   @Override  
   public CacheDirective getCacheDirective(Command command,  
       ExecutionContext executionContext, RuntimeMetadata metadata)  
       throws TranslatorException {  
     CacheDirective cd = new CacheDirective();  
     cd.setScope(Scope.USER);  
     return cd;  
   }  

For complete source code of CacheTestExecutionFactory teiid translator see caching-api project on GitHub ( https://github.com/rokhmanov/teiid-test ). Also the Teiid user forum has a related discussion thread.