Browse Prior Art Database

Smart caching of SQL queries results

IP.com Disclosure Number: IPCOM000243185D
Publication Date: 2015-Sep-17
Document File: 4 page(s) / 105K

Publishing Venue

The IP.com Prior Art Database

Abstract

Aim is to create a transparent (from application point of view) device/service that will in an intelligent way cache results of SQL queries. Large part of applications (especially web applications) read data from database much more frequently than write to the database. In some case data is being written only by etl (extract-transform-load) processes scheduled to be run periodically e.g. once a day. Moreover majority of the reads is being performed by so called prepared sql statements - predefined queries where only a small and know number of parameters can be altered. The above gives possibility to introduce a caching mechanism that could return valid result for sql query without even involving the database, or asking the database to provide only some subset of the data needed to construct valid response by the cache itself. This way the results will be provided faster, possibly with reduced need for bandwidth and additionally the cache may be shared by multiple applications or instances of application.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 51% of the total text.

Page 01 of 4

Smart caching of SQL queries results

  Large part of applications (especially web applications) read data from database much more frequently than write to the database. In some case data is being written only by etl (extract-transform-load) processes scheduled to be run periodically e.g. once a day. Moreover majority of the reads is being performed by so called prepared sql statements - predefined queries where only a small and know number of parameters can be altered. The above gives possibility to introduce a caching mechanism that could return valid result for sql query without even involving the database, or asking the database to provide only some subset of the data needed to construct valid response by the cache itself. This way the results will be provided faster, possibly with reduced need for bandwidth and additionally the cache may be shared by multiple applications or instances of application.

Aim is to create a transparent (from application point of view) device/service that will in an intelligent way cache results of SQL queries. We assume that whole SQL traffic goes through the device - this is vital to ensure data and cache consistency. We only

want to cache reads (SELECT statements).

The basic idea is as follow:

  Upon receiving a request in form of SQL query the cache analyses the query:
- Extract artifacts: tables or views with associated ranges
- Identify query as a read request or write request

  In case of write request cache purges any internally stored data related to identified artifacts.

  In case of read request it checks if the response can be generated using only cached data and if it is not the case, it prepares a copy of original request with ranges possibly reduced (not to re-fetch what is already cached) and sends the modified request to the database. Then combine database response with internal data to compose valid response to original query.

1


Page 02 of 4

Practical implementation will consist of two blocks - cache logic/sql parser and key - value store in form of either a separate service (like memcached) or associative array (hashmap) of any kind (including but not limited to hierarchically stored data (for example ram spilling to flash spilling to mechanical disk)) directly built in into device/service (cache agent on picture). Cache can be implemented anywhere on data path between application and database including but not limited to: as network proxy (stand-alone) device, as a local service on application server, as local service on db machine, as part of db access libraries/driver. In most cases cache can be exposed to more than one application (app server and user API on picture above) which will give additional benefit - data cached as per request from one application can be re-used by the other.

Catchable queries.

As relational da...