Skip to main content
Version: 5.5

Databases

Always select with filter​

Only use SQL select in conjunction with filters (WHERE clause) to avoid loading the entire table into the working memory. In conjunction with transfer flags, filter by <transferflag> &lt;&gt; 1, for example.

Mark transferred records​

Mark transferred records. In your tables, provide columns in which OPC Router can mark transferred records ("transfer flag" not transferred, transferred, transfer error). The transfer flag columns should be assigned the default value "0" and should not be allowed to accept NULL values to ensure that the three statuses are always correctly assigned.

Index transfer flag

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

  • If the OPC Router is to filter by transfer flag = 0:
    CREATE NONCLUSTERED INDEX [IX_TransferTable_Transferflag] ON [dbo].[TransferTable]
    (
      [Transferflag] ASC
    )
    WHERE ([Transferflag]=(0))
    GO
  • If the OPC Router should filter by transfer flag &lt;&gt; 1:
    CREATE NONCLUSTERED INDEX [IX_TransferTable_Transferflag] ON [dbo].[TransferTable]
    (
      [Transferflag] ASC
    )
    WHERE ([Transferflag]&lt;&gt;(1))
    GO

Sort Select results​

Sort the table in ascending order by the transfer flag. This ensures that the non-transferred records are transferred first; failed individual transfers are only retried at the end of the transfer. To optimize performance, you can filter by 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 indexed sensibly:

  • Columns that are frequently searched should be indexed.
  • If indexed columns of existing data records are updated frequently, the index gradually becomes fragmented and must be recreated regularly. Otherwise, performance losses would result.

Further information, including on the use of diagnostic scripts, can be found in the inray commissioning manual, which you can find on the installation source under Additionals/Doc.