How Does SQL Perform When Compared to Canary?

Feb 20, 2018 9:56:45 AM / by Jeff Knepper

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.

 

 

Topics: Case Studies, Data Historian, Products

Jeff Knepper

Written by Jeff Knepper