Archive configuration for best SQL evacuation performance
The time required to evacuate data to a database increases linearly with the number of insert SQL statements. In earlier zenon versions, recorded values were evacuated to the target database sequentially using single-insert statements. From zenon 11 Build 115310 and further, evacuation of archive data can use multi-row (bulk) insert statements with a fixed 50 values/recordings per variable (Feature 254362: “Service Engine Archive Evacuation uses multi-row insert where possible"). Single-insert SQL statements are still used in the evacuation process when the number of data points does not fit exactly the hardcoded bulk size (50).
Explanation:
A. Number of values recorded per variable (X) in the archive capsule is smaller than 50.
Variable X has 18 points recorded in the archive ARX file. -> Points are evacuated to the database using 18 single-insert statements.
B. Number of values recorded per variable (Y) in the archive capsule is greater than 50.
Variable Y has 78 points recorded in the archive ARX file. -> Points are evacuated to the database using 1 bulk-insert statement + 28 single-insert statements. A total of 29 SQL-insert statements are executed.
The goal is to set the archive saving cycle time, so that only multi-row inserts are used.
This possible in most cases of archives with cyclic scanning but not possible in most cases with archives recorded on change as the variables can change differently per saving cycle. Evacuation of the archives to the SQL Database is serialized. If multiple archives evacuate to the SQL database at the same time this will be done one after the other.
This possible in most cases of archives with cyclic scanning but not possible in most cases with archives recorded on change as the variables can change differently per saving cycle. Evacuation of the archives to the SQL Database is serialized. If multiple archives evacuate to the SQL database at the same time this will be done one after the other.
Simple example for perfect archive configuration in terms of performance:
Requirement:
- 2000 variables:
- 2000 variables:
- Record a value every second in a cyclic archive
Result:
- Use 5 minutes saving cycle because: 5minutes = 300 seconds, 300 seconds/50(bulk size) = 6 -> just 6 multi-row inserts are used to insert the data to the data base without any single insert statement.
- .ARX file size: (69 Bytes + 48 Bytes * 2000 + 24 Bytes * 2000 * 300) /1024 /1024 = ~13.82 MB
Rule of thumb:
Adopt the saving cycle to a multiple of 50. If the .ARX file gets bigger than ~>20MB because of to many variables reduce the saving cycle or variable amount. Check out the following FAQ regarding archive file size: FAQ: Historian configuration general recommendations (copadata.com)
Adopt the saving cycle to a multiple of 50. If the .ARX file gets bigger than ~>20MB because of to many variables reduce the saving cycle or variable amount. Check out the following FAQ regarding archive file size: FAQ: Historian configuration general recommendations (copadata.com)
If you have any question for this FAQ please leave a feedback.
If you would like to know more about Historian please contact your local COPA-DATA Representative.
Related Articles
FAQ: Historian configuration general recommendations
1. Recommendation on hardware: Storage Memory CPU - SSD (Solid-State Drive). - Disk space capacity as detailed in the attached Excel sheet outputs. - RAM required to load the archive data is about 4 times greater the size of the respective ARX files. ...Historian: SQL evacuation fails with certain archive files
Description An error occurs in the Historian during the SQL evacuation of archive data. The evacuation fails for certain archive files. This occurs when the last value of a variable is "NaN". In this case, this value is expected to be skipped and ...SQL evacuation failure with double entries in archive file
Summary The SQL evacuation stops if in the archive file are two identical values. Identical means same NameID same Value same Timestamp same Status. These entries could happen if in the archive configuration the option 'save process image during ...Archive Evacuation for RDA Archive not working for *.arx archives.
Summary Historian RDA Archive doesn't evacuate intern archive format *.arx archives DescriptionHistorian RDA Archive doesn't evacuate intern archive format *.arx archives Solution Evacuation for RDA archive working. Issue Number: 37195 Fixed on Date: ...NaN values should be suppressed during SQL evacuation
Summary NaN values should be suppressed during SQL evacuation, since NaN values cannot be represented in SQL. Description NaN values should be suppressed during SQL evacuation, since NaN values cannot be represented in SQL. Solution The evacuation ...