Recently, a system integrator asked us to provide data that would provide both speed and storage requirement comparisons between an out-of-the-box SQL database and Canary. They wanted to use this data to decide whether it would be best for customer's to deploy a Canary system, or deploy, tune, and maintain an SQL system.
Below are the published result of a 10,000 tag system in both a Canary historian as well as a Microsoft SQL server.
Summary
The purpose of this study is to demonstrate the value of using the Canary Historian out of the box in comparison with Microsoft SQL Server for an enterprise historian. This study focuses on comparing write performance and disk usage of the 2 products. Engineering is familiar with Microsoft SQL Server and acknowledge that there are numerous ways to tune SQL server for significant improvements to both write and read access, however no tuning was done to either logging system for this study.
This study was completed on a single development machine using Microsoft SQL Server 2017 Developer Edition (same as Enterprise Edition) and a beta version (v18) of the Canary historian and sender service. Based on our experience migrating customers from SQL Server to the Canary historian, two SQL Server databases with different schemas were compared; one with 3 related tables with primary and foreign keys and one with a single table with no primary key and no indexes.
The write application is a .NET console application based on a sample that we provide (upon request) to customers to write data to the Canary sender service. Simple ADO.Net code was then added to this sample to insert tag data into SQL server. A .NET Stopwatch object (which provide high resolution timing) was used to determine length of write tests. Windows explorer was used to determine size (in bytes) of historian data files and SQL server data and log files. Data written consisted of 40,416,000 TVQ records (Timestamp, Value, Quality). This was the accumulation of 10,000 tags changing throughout 1 day (2000 every 5 seconds, 4000 every 1 minute, and 4000 every hour).
Tag Test Data
40 character base tag name + integer index from 1-10,000
Example: SessionExample..Tagname_xxxxxxxxxxxxxx_1)
Float values
10,000 tags total for a 24 hour period
Change rate of tags as follows:
-2,000 change at 5 second intervals
-4,000 change at 1 minute intervals
-4,000 change at 1 hour intervals
Total of 40,416,000 TVQ records
Results - Performance
| Database | Write Speed (TVQs/sec) | % Faster (than slowest) |
| Canary Enterprise Historian | 174,961 | 32,102.94% |
| Microsoft SQL Relational DB | 545 | n/a |
| Microsoft SQL Single table DB | 548 | 0.6% |
Results - Storage Requirements
| Database | Disk Usage (MB) | % More (than Canary) |
| Canary Enterprise Historian | 458.04 | n/a |
| Microsoft SQL Relational DB | 1631.65 | 339.7% |
| Microsoft SQL Single table DB | 5044.76 | 1,050.4% |
Test Machine Specs
| Manufacturer: | Dell |
| Model: | Precision Tower 7810 |
| Processor: | Intel Xeon CPU E5-2630 v4 @2.20GHz (10 core) |
| Memory: | 32 GB 2400 MHz |
| System Drive (C:): | Sk hynix PC300 PCIe NVMe 1TB SSD |
| Storage Drive (D:): | Western Digital 1TB hard drive 7200 RPM SATA 6 Gb/s 64MB buffer |
| Operating System: | Windows 10 Pro 64 bit 10.0.16299 |
SQL Database Details
Database 1 (Relational DB):
- (Table1) Sites
- Site information storage
- Primary key
- (Table2) Tags
- Tag definition storage
- Primary key
- (Table3) TVQs
- Tag value storage
- Foreign keys to tag and site tables
- Primary key
Database 2 (Single table DB):
- (Table1) TVQs
- Tag definition (name) and value storage
- No primary key
- No indexes
SQL Server Specs
| Version: | Developer Edition Version 14.0.1000.169 (2017) = Same as enterprise edition |
| Storage Drive: | D: |
Canary Historian Details
Historian data was written to a single data set using a single session through the store and forward sender service API (WCF Service).
Canary Software Specs
| Version: | 18 (pre-release build) 2/12/18 |
| Storage Drive: | D: |
| Sender Service Buffer Drive: | C: |
| High Speed License | Yes |
Writer Software
| Canary: |
SAF_Examples project using SessionHelper implementation Single threaded, 1 session |
| SQL: |
ADO.NET Single threaded; 1 connection |
Conclusion
The Canary historian was built from the start for high performance time series data logging and provides a significant performance improvement regarding write speed and disk space savings over an out of the box Microsoft SQL Server installation.
Engineer Information
Joshua Wolf
Senior Software Engineer
Canary Labs, Inc.
B.S. Information Science and Technology Penn State
12 years programming experience with .Net and SQL Server in the realm of industrial control systems and content management system.
