View Full Version : Understanding Lianja SQL SmartQuery

2012-10-31, 07:34
Much of the data that is required in web apps and mobile apps uses SQL to query the data from a backend database server.

The bad things about this is that it is very "non-green" as the query gets parsed and optimized then data is retrieved every time the query is performed.

When you think about it, this is rather stupid as on many occasions the tables containing the data may not have changed since the last time a query was performed.

In many Apps that handle data as WORM (Write Once Read Many), the Lianja SQL engine can be optimized to return the data that was last queried without having to parse or optimize the SQL SELECT statement over and over for each user.

To accomplish this it uses what we call a "SmartQuery" algorithm.

When a query is performed, it first looks in the "smartquery cache" to see if there is a cached version of the resultant data from the SQL SELECT. If there is, it then checks to see if any of the tables involved in the query have been modified in any way since the resultant data was cached. If any of them have been modified it throws away the cached result and continues to parse and optimize the query. If they have not then it just returns the last cached results of the query.

Clearly on a heavily loaded system with many concurrent requests going on this can provide a dramatic performance improvement especially on tables that do not change very often.

To utilize Lianja SQL SmartQuery the following commands can be used.

set smartquery on | off
set smartquery to <seconds>
clear smartquery

The "set smartquery to <seconds>" command provides the ability to specify an expiration interval in seconds for the cached data.

With the introduction of virtual tables in Beta10, SmartQuery can be used to great advantage to reduce network traffic and provide high performance for all types of Apps; Desktop, Cloud and Mobile, that perform queries against MSSQL, Oracle, MySQL or native Lianja databases.