Skip to main content
Version: 5.2

Databases

Select always with filter​

Use SQL Select only with filters (WHERE clause) to avoid loading the entire table into memory. In conjunction with transfer marks, you filter e.g. after <Transferflag> <> 1

Mark transferred records​

Mark transferred records. In your tables, keep columns in which the OPC Router can mark transferred data records (“Transfer flag” not transferred, transferred, transfer error). The transfer flag columns should be set to the default value of 0 and should not be allowed to assume NULL, so that a correct assignment to the three statuses is always guaranteed.

Index transfer flag

To improve performance, the transferflag column should be indexed as follows:

  • If the OPC Router is to filter for transferflag = 0:

    CREATE NONCLUSTERED INDEX [IX_TransferTable_Transferflag] ON [dbo].[TransferTable]
    (
      [Transferflag] ASC
    )
    WHERE ([Transferflag]=(0))
    GO

  • If the OPC Router is to filter for transfer flag <> 1:

    CREATE NONCLUSTERED INDEX [IX_TransferTable_Transferflag] ON [dbo].[TransferTable]
    (
      [Transferflag] ASC
    )
    WHERE ([Transferflag]<>(1))
    GO

Sort select results​

Sort the table in ascending order after the transfer flag. So first the untransferred records are transferred; erroneous individual transfers are only attempted again at the end of the transfer. For performance optimization you can filter for the transfer flag column (transfer flag = 0 or = 2). A correspondingly filtered index can be created in the database for this purpose.

Database design​

Database tables should be meaningfully indexed:

  • Columns within which you search frequently should be indexed.
  • When indexed columns of existing records are updated frequently, the index gradually becomes fragmented and needs to be rebuilt on a regular basis. Otherwise, performance losses would be the result.

Further information, including the use of diagnostic scripts, can be found in the in-commissioning manual, which can be found on the installation source at Additionals/Doku.