Statement cache

In order to speed up the time spent in the SQLite3 engine (it may be useful for high-end servers), the framework is able to natively handle prepared SQL statements.

Starting with version 1.12 of the framework, we added an internal SQL statement cache in the database access, available for all SQL request. Previously, only the one-record SQL SELECT * FROM ... WHERE RowID=... was prepared (used e.g. for the TSQLRest. Retrieve method).

That is, if a previous SQL statement is run with some given parameters, a prepared version, available in cache, is used, and new parameters are bounded to it before the execution by SQLite3.

In some cases, it can speed the SQLite3 process a lot. From our profiling, prepared statements make common requests (i.e. select / insert / update on one row) at least two times faster, on an in-memory database (':memory:').

In order to use this statement caching, any SQL statements must have the parameters to be surrounded with ': (' and ') :'. The SQL format was indeed enhanced by adding an optional way of marking parameters inside the SQL request, to enforce statement preparing and caching.

Therefore, there are now two ways of writing the same SQL request:

Write the SQL statement as usual:

SELECT * FROM TABLE WHERE ID=10;

in this case, the SQL will be parsed by the SQLite3 engine, a statement will be compiled, then run.

Use the new optional markers to identify the changing parameter:

SELECT * FROM TABLE WHERE ID=:(10):;

in this case, any matching already prepared statement will be re-used for direct run.

In the later case, an internal pool of prepared TSQLRequest statements is maintained. The generic SQL code used for the matching will be this one:

SELECT * FROM TABLE WHERE ID=?;

and the integer value 10 will be bounded to the prepared statement before execution.

Example of possible inlined values are (note double " quotes are allowed for the text parameters, whereas SQL statement should only use single ' quotes):

:(1234): :(12.34): :(12E-34): :("text"): :('It''s great'):

All internal SQL statement generated by the ORM are now using this new parameter syntax.

Note that a similar SQL statement cache is available for external database engines. See this blog article.

The framework documentation explain how you may better use prepared statement in your code.
Most client access methods have two kind of parameters, including inlined parameters: you do not need to write any  :(1234): in your code - just supply plain variable content (integer, double, currency, RawUTF8), and the framework will inline their values according to the above scheme.

Global JSON cache

A global cache is used to enhance the framework scaling, and will use JSON for its result encoding.

In order to speed-up the server response time, especially in a concurrent client access, the internal database engine is not to be called on every request. In fact, a global cache has been introduced to store in memory the latest SQL SELECT statements results, directly in JSON.

The SQLite3 engine access is protected at SQL/JSON cache level, via DB.LockJSON() calls in most TSQLRestServerDB methods.

A TSynCache instance is instantiated within the TSQLDataBase internal global instance, with the following line:

constructor TSQLRestServerDB.Create(aModel: TSQLModel; aDB: TSQLDataBase;
  aHandleUserAuthentication: boolean);
begin
  fStatementCache.Init(aDB.DB);
  aDB.UseCache := true; // we better use caching in this JSON oriented use
  (...)

This will enable a global JSON cache at the SQL level. This cache will be reset on every INSERT, UPDATE or DELETE SQL statement, whatever the corresponding table is.

In practice, this global cache was found to be efficient, even if its implementation is some kind of "naive". It is in fact much more tuned than other HTTP-level caching mechanisms used in most client-server solutions (using e.g. a Squid proxy) - since our caching is at the SQL level, it is shared among all CRUD / Restful queries, and is also indenpendent from the authentication scheme, which pollutes the URI.

CRUD cache

The last two caches are in fact shared at the TSQLRest level. A dedicated TSQLRestCache instance can be created, and will maintain such a tuned caching mechanism.

A call to TSQLRest.Cache's SetCache() and SetTimeOut() methods is enough to specify which table(s) or record(s) are to be cached, either at the client or the server level. In order to ensure that the server content is coherent with the client side, a dedicated TSQLRestClientURI.ServerCacheFlush method is available, and will call a corresponding server on the server to flush its cache content on purpose. It's worth warning that it's up to the code responsibility to ensure that these caches are consistent over the network.

A typical content of these two last tuned caches can be global configuration settings, session data, or any other kind of data which is not likely to vary often. Profiling can be necessary to identify which data is to be registered within those caches, either at the client and/or the server side.

Conclusion - Cache efficiency

Thanks to those specific caching abilities, our framework is able to minimize the number of client-server requests, therefore spare bandwidth and network access, and scales well in a concurrent rich client access architecture.

Feedback and comments are welcome on our forum.