Requirements when Querying Historic Tables
When using SQL to query data in historic tables and historic views, you should construct your queries so that they query limited amounts of historic data. Large and inappropriate query searches can severely affect system performance and can potentially delay other system operations. For this reason, you should always include a SELECT Clause in such queries to limit the amount of historic data that will be queried by specifying the time range for that data and the relevant points to which the data relates. For example, when querying the CDBPoint and CDBHistoric classes, you might restrict the RecordTime and Id so that you query just the historic records for particular database point over a one-hour period. A historic view does not require a time constraint because it includes default time constraint properties in its configuration. If you do use a time constraint with a historic view, then that constraint will override those default properties. The following example shows historic views with time constraints in a UNION.
For the UNION of two Historic View tables, you have to include a SELECT clause for each Historic View table:
SELECT
*
FROM
HISDAILYAVERAGE
WHERE
ID = 6726 and RECORDTIME > {OPC 'D'}
UNION
SELECT
*
FROM
HisDailyMaximum
WHERE
ID = 6726 and RECORDTIME > {OPC 'D'}
Further Information
Use the Server Status Tool to check for any Inefficient SQL Queries.
Example that demonstrates an ST Logic program using an SQL query to query historic data: see Raw Historic Values in SQL Queries in the Geo SCADA Expert Guide to Logic.