Tuning publication definitions

Advanced Replication Guide : Performance monitoring and tuning : Tuning for data synchronization : Tuning publication definitions

solidDB^® advanced replication uses publications for downloading incremental data from the master to replica databases. In a publication, you define the data for propagation from the master to the replica. Using the CREATE PUBLICATION statement, you specify the tables and the search criteria for selecting data to the replica. For details on publications, read Creating publications.

Below is a simple publication definition example:

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 SELECTs. 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:

▪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, we recommend that you avoid nesting result sets in publication definitions. See the following sections of for examples of nested vs. 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.