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
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
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
statements is maintained. The generic SQL code used for the matching will be
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
your code - just supply plain variable content (
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
SELECT statements results, directly in JSON.
The SQLite3 engine access is protected at SQL/JSON cache level, via
DB.LockJSON() calls in most
TSynCache instance is instantiated within the
TSQLDataBase internal global instance, with the following
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
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.
The last two caches are in fact shared at the
TSQLRest level. A
TSQLRestCache instance can be created, and will maintain
such a tuned caching mechanism.
A call to
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.