Excessive Number of Historic Records

One potential cause of slow performance with Historic Lists and Historic Trends is that there is an excessive number of historic records. This can happen when historic data is recorded too frequently, causing the historic files to become large in size (over 5MB).

If you are experiencing slow performance of Historic Lists or Historic Trends, you should first determine whether an appropriate Historic cache size is in place and whether your server has appropriate hardware for your requirements (see Slow Historic Display Performance due to Cache Size or Volume of Historic Data). If the Historic cache size and server hardware are sufficient, you should investigate the number of historic records being generated:

1. Access the ClearSCADA Server Status tool, see Run the Server Status Tool.
2. Expand the Historic folder, then select the Historian category.
3. Look at the Raw row (which represents the raw historic data). Pay particular attention to the Largest File status—if it is 5MB or greater, it means that there is a large amount of historic data.
4. Log on to ViewX and display any Queries List (see Display a Queries List in the ClearSCADA Guide to Lists).
5. Right-click on the List.
   A context-sensitive menu is displayed.
6. Select the Edit Query option.
   The SQL window displays the List’s SQL Query.
7. Replace the existing SQL Query with the following SQL:

   SELECT TOP(10) SUM(RecordCount),F.ObjectId,O.FullName

   FROM CDBHistoricFile AS F INNER JOIN CDBObject AS O ON (F.ObjectId = O.Id)

   GROUP BY F.ObjectId

   ORDER BY 1 DESC

   NOTE: The TOP(10) part of the query instructs the List to display the ten points. If you wanted the list to show a greater or lesser number, you can change the number accordingly, for example, TOP(30) would return a List containing information about 30 points.

8. Select the OK button to run the Query.
   The Queries List displays a list of the points that have the highest number of historic records.

   

   Each row in the list represents 1 database point and provides a Sum total of the historic records for that point, the point’s unique ID (Object Id), and the point’s Name value (FullName) which shows the name and location of the point.

9. Check the configuration of the points named in the list. These are the points that have the highest number of historic records and so will have their historic values recorded too frequently. You need to configure the points so that their historic values are recorded less often (this will reduce the number of historic records and in turn, the size of the historic files).

   By changing the configuration of the points, you may have resolved the performance issues with your Historic Trends and Historic Lists. But the performance problems you experienced will continue until the Historic Life has expired (as the large historic files will still be in place). The Historic Life is defined in the Keep Online For field in the server’s Historic configuration (see Specify the Duration to Retain Historic Data Online).

   When the Historic Life has expired, you may notice an improvement in the performance of your Historic Trends and Historic Lists. If your Historic Trends and Lists are still affected by slow performance, you should repeat steps 4-9 of this procedure, as there may be further points that are having their historic values recorded too frequently.

Further Information

Define an individual point’s Historic storage features: see Defining Historic Details for a Point in the ClearSCADA Guide to Core Point Configuration.