Limitations When Using Legacy SQL Functions

The SQL library used in Citect SCADA prior to release 7.30 has a number of limitations and these are documented in the Knowledge Base (article Q4014). This library was not designed to handle large scale SQL data transactions. For large volumes of data it is recommended that Citect SCADA Reports (CitectHistorian) be used. Note that new functions using ADO.NET do not share these limitations.

If you intend to use the embedded SQL library in Citect SCADA, consider the following:

• Use "Simple" model for a SQL database to limit transaction information logging to transaction log file.
• Use the fixed size of transaction log to restrict the log file growing.
• Back up the database regularly to keep the transaction log size under control. When SQL Server finishes backing up a database or its transaction log, it automatically truncates the inactive portion of the transaction log. If the transaction log file is full, your database transactions will cease.
• Keep working tables as small as possible. When Citect SCADA SQL adds or appends a new row to data table, it uses SELECT * first to get column information. If there are hundred and thousand of records in the table, this action will certainly hinder the performance.
• Use SQL Server trigger to remove records from the working tables to the permanent tables. In this way, sizes of the working tables that are directly interfaced with Citect are not growing unrestricted.

Published June 2018