solidDB Advanced Replication uses publications for downloading incremental data from master to replica databases. In a publication, you define the data for propagation from the master database to the replica database by using the CREATE PUBLICATION statement, see Creating publications.
The following example shows a simple publication definition:
CREATE PUBLICATION configuration_of_device (device_name VARCHAR) BEGIN RESULT SET FOR device BEGIN SELECT * FROM device WHERE name = :device_name; RESULT SET FOR device_cfg_parameter BEGIN SELECT * FROM device_cfg_parameter WHERE device_id = device.id; END END END
Internally, the queries of the RESULT SET FOR paragraphs are executed as regular SELECT statements. Nested result sets always produce a join between an outer and inner result set. Therefore, in optimizing performance, the same indexing rules apply here as with other queries, which means you should take the following steps:
▪ create an index on columns of large tables that are used as search criteria,
▪ create an index on columns that are used for joins in the nested result set.
In addition, it is recommended that you avoid nesting result sets in publication definitions. See the following topics for examples of nested and unnested result sets:
You can extract the SQL that is actually generated from the publication definitions by setting SQL trace on in the master database using the following command:
ADMIN COMMAND 'trace on sql'
The output of the trace goes to the standard trace file of solidDB. The default name of the file is soltrace.out.