solidDB Help : Replication : Advanced Replication : Performance monitoring and tuning for Advanced Replication : Tuning for data synchronization : Tuning publication definitions
  
Tuning publication definitions
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:
Nested publication version
Unnested publication version
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.
Go up to
Tuning for data synchronization