0

I am working with an application where we have SQL Server as a database. There is a requirement to add Hazelcast cache layer over the SQL Server. This is a legacy system and sort of works like SQL as a service where SQL queries to be executed against SQL Server DB are stored in a configuration table. For instance there is a UI for which data is returned by the query

Select case when page > 18 then 'Adult' else 'Teen' as Category, convert(varchar(20), p.registrationDate) as registeredOn from Person p

This is dynamically fetched and retrieved and executed against the database. There are many such views rendered off dynamic queries. With Hazelcast some of these SQL syntax will not just work. Is there any database abstraction framework or library or some adapter layer which can take in the SQL query and execute it against Hazelcast?

Vy Do
  • 46,709
  • 59
  • 215
  • 313
Casual Gamer
  • 1

1 Answers1

0

I see a few choices with different trade-offs:

  1. Use an ORM library (e.g. Hibernate) with Hazelcast as a 2nd level cache. ORM fully manages the cache by caching the entities and queries automatically. You should however use the ORM API instead of the native SQL. That might require you to rewrite the app code.

  2. Cache complete query results and use the SQL query as a key. This is the least invasive option and is just good enough if the DB data change rarely and you use the same SQL queries repeatedly. By using cache-aside pattern, the changes to your app will be minimal (https://hazelcast.com/blog/a-hitchhikers-guide-to-caching-patterns/)

  3. Query Hazelcast using SQL. This gives you most options to optimize the performance. Cache the DB tables you need and query Hazelcast using SQL. Might require rewriting the SQL queries to comply with Hazelcast SQL limitations: https://docs.hazelcast.com/hazelcast/5.1-beta-1/sql/sql-overview#limitations

Vlado Schreiner
  • 478
  • 2
  • 5