Skip to main content

Databases

Always Use a Filter with SELECT​

Use the DB Select transfer object only in connection with filters to prevent the entire table from being loaded into memory. You can configure the filters graphically; the overview of database filter operators shows which comparison operator is used for what purpose.

When used in connection with transfer flags, you filter based on the transfer flag column. For the three states “not transferred,” “transferred,” and “transfer error,” two filters are equivalent: A filter using NOT_EQUAL set to the value for “transferred” returns the same records as the combination of “not transferred” and “transfer error”—both retrieve exactly the pending and the erroneous records. Create a suitably filtered index for this in the database.

Mark transferred records​

Mark transferred records. Provide columns in your tables where the OPC Router can mark transferred records (“Transfer flag” not transferred, transferred, transfer error).

The transfer flag columns must not accept NULL values to ensure that a correct assignment to the three statuses is always guaranteed. The default value assigned to the column in the table, however, depends on the process: If new data records are to be queued for transfer immediately, the value is “not transferred”; otherwise, it is a different value. The marking values and both cases are described at Database to Other Systems.

Indexing the Transfer Flag

To improve performance, the transfer flag column should be indexed. The following examples are written in T-SQL for Microsoft SQL Server; the same rule applies to Oracle, MySQL, PostgreSQL, SAP HANA, as well as ODBC and OLE DB connections, using the syntax specific to each system.

  • If the OPC Router is to filter by Transferflag = 0:

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

  • If the OPC Router is to filter by Transferflag <> 1:

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

Sorting Select Results​

Sort the query results in ascending order by the "Transfer Flag" column. To do this, in DB Select, on the Query tab, set the “Sort order” and “Sorting by column” fields. This ensures that the records that have not yet been transferred are transferred first; failed individual transfers are retried only at the end of the transfer.

Database Design​

Database tables should be indexed appropriately:

  • Columns that are frequently searched should be indexed.
  • If indexed columns in existing records are updated frequently, the index will gradually become fragmented and must be rebuilt regularly. Otherwise, performance will suffer.

For additional information, including guidance on using diagnostic scripts, please contact inray Support at ](https://www.opc-router.de/kontakt-und-support/) or visit the [.