Performance optimization on network drives using SMARTQUERY caching

[barrymavin]

I have now enabled SMARTQUERY caching for network drives in Lianja v2.1.

This is my test on a database and the performance gains I am now seeing by tuning the database engine with the commands detailed below.

clear smartquery // this clears the existing SMARTQUERY cache 
set smartquery on
set transaction isolation level repeatable read
set share on
set networkshare on
set icache to 1024
set dcache to 8192*8
open database bdficc
set explain on
SELECT * FROM cliente,historia into cursor testa nomemo WHERE HISTORIA.CL=CLIENTE.CCL

********************************************************************************
Explaining execution plan for command:
select * FROM cliente,historia into cursor testa nomemo WHERE HISTORIA.CL=CLIENTE.CCL
--------------------------------------------------------------------------------
No smartquery cache available
Optimized JOIN to table 'historia' using index tag 'INDH2'
SET EXCLUSIVE OFF
SET NETWORKSHARE ON
SET SMARTQUERY ON
SET DCACHE ON (Table page cache)
SET DCACHEBLOCKSIZE TO 8192 (Size of table cache pages)
SET DCACHESIZE TO 65536 (Number of table pages to cache)
SET ICACHE TO 1024 (Index node cache)
SET SQLCACHE ON (SQL Query cache)
SET SQLCACHESIZE TO 65536 (Size of SQL Query cache extents)
SET SQLBUFFERSIZE TO 1024 (Number of SQL write cache buffers)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ requires shared(read) lock on tables and indexes
Performing join
Parent table 'cliente' has 6469 records, table size is 7.00MB, DCACHE is ON, DCACHEBLOCKSIZE 8912, record size 1114, records per page 8
Child table 'historia' has 45619 records, table size is 63.00MB, DCACHE is ON, DCACHEBLOCKSIZE 8912, record size 1453, records per page 6
Processing parent table 'cliente' sequentially
Total I/O read operations was 51030
Total I/O read size was 71.4874MB
Total I/O write size was 0.0000MB
Total SEEK operations performed was 6470
Total SEEK operations failed was 1593
Total locks performed 0
Total rlocks performed was 7
Total unlocks performed was 3 (4 active)
Total I/O read cache hits was 55328
Total I/O read cache misses was 7818
Total I/O index cache hits was 21561
Total I/O index cache misses was 586
Join completed in 7.176 seconds
Transaction isolation level requires shared(read) unlock of tables and indexes
Save as CURSOR TESTA
Save as c:\users\barrym~1\appdata\local\temp\_00001ab00008.dbf
Inserting 34227 rows of size 2526 without memos using buffersize 161664, records per page 64
Rows inserted in 6ms
Total I/O read operations was 51037
Total I/O read size was 71.4971MB
Total I/O write size was 82.4523MB
Total SEEK operations performed was 6472
Total SEEK operations failed was 1595
Total locks performed 7
Total rlocks performed was 8
Total unlocks performed was 15 (0 active)
Total I/O read cache hits was 55329
Total I/O read cache misses was 7819
Total I/O index cache hits was 21561
Total I/O index cache misses was 587
34227 records selected in 7.737 seconds

Now I execute the same query which is now SMARTQUERY cached.

SELECT * FROM cliente,historia into cursor testa nomemo WHERE HISTORIA.CL=CLIENTE.CCL

********************************************************************************
Explaining execution plan for command:
select * FROM cliente,historia into cursor testa nomemo WHERE HISTORIA.CL=CLIENTE.CCL
--------------------------------------------------------------------------------
Using smartquery cache file C:\Lianja\data\system\_sq00001ab01.dbf
Save as CURSOR TESTA
Total I/O read operations was 8
Total I/O read size was 0.0106MB
Total I/O write size was 0.0000MB
Total SEEK operations performed was 7
Total SEEK operations failed was 0
Total locks performed 0
Total rlocks performed was 8
Total unlocks performed was 8 (0 active)
Total I/O read cache hits was 15
Total I/O read cache misses was 6
Total I/O index cache hits was 11
Total I/O index cache misses was 2
34227 records selected in <1ms

Notice that SMARTQUERY has detected that the query was previously executed and is now cached so it just uses that resultset. If there are any changes to the data in the FROM tables of the SQL SELECT then the SMARTQUERY cache for that query is thrown away and re-executed. You can specify the "lifetime" of the resultset using SET SMARTQUERY TO nSeconds, in which case if any of the tables have changed within nSeconds of the last query execution then the resultset cache will be thrown away and the query re-executed.

At any time you can throw away all of the SMARTQUERY cache by executing CLEAR SMARTQUERY.

In many applications real-time queries for BI and reporting are not required and you can work with a snapshot of data. You can either SET SMARTQUERY TO a high number e.g. 60*60*24 which is 24 hours or alternatively COPY DATABASE TO and report from the snapshot of the data.

If you want to perform a query and ignore the SMARTQUERY cache specify the NOSMARTQUERY keyword on the SQL SELECT.

SELECT * FROM cliente,historia into cursor testa nomemo nosmartquery WHERE HISTORIA.CL=CLIENTE.CCL

Or alternatively, you can force SMARTQUERY for a particular SQL SELECT like this:

SELECT * FROM cliente,historia into cursor testa nomemo smartquery WHERE HISTORIA.CL=CLIENTE.CCL

Working with huge amounts of data for BI and reporting is speeded up dramatically using SMARTQUERY.

Note that the smartquery cache is shared across all users on a system, so if one user performs a query and it becomes cached, if there are no changes made to the tables in the query other users use the cached resultset.

On a heavily loaded WORM (Write Once Read Many) database such as one used as a data warehouse this results is massive performance gains.

The smartquery cache is persistent across server reboots.

For anyone interested the SMARTQUERY resultset cache for each SQL SELECT can be found in:

x:\lianja\data\system\_sqXXXXXXX.dbf
x:\lianja\data\system\_sqXXXXXXX.dbt
x:\lianja\data\system\_sqXXXXXXX.dbd

These are kept in the server system database directory and shared across all desktop LAN users, Lianja SQL Server connections, Web and Mobile users.