Opinion: Do you need a $60,000 process historian to log data? One SQL database expert says no.

Are relational databases too slow for fast process data? You decide.

03/26/2009


Steve Hechtman, Inductive Automation

I wish to register a complaint. There is a rumor that has been circulating for years that relational databases are too slow for fast process data and that only process historians are up to the job. Vendors of process historians will cite sluggish performance and the lack of data compression as the reasons standard off-the-shelf relational databases won’t work. Apparently the last time they used a SQL relational database was a few decades ago.

While there may be some specialized domains where process historians have a niche, they are not a practical choice for most industrial applications. In effect, historian vendors are saying your Toyota Camry is inappropriate transportation because it is incapable of going 180 mph or finishing the quarter mile in under 10 seconds.

The rumor denigrating relational databases for poor throughput is baseless. A standard, off-the-shelf Microsoft SQL Server coupled with FactorySQL can log in excess of 100,000 tags per second using a desktop machine. In all likelihood, other factors such as the industrial network would become bottlenecks before the database does. Furthermore, today’s generation of SQL relational databases are designed to scale gracefully to power high-volume Website traffic, whose load peaks dwarf those of industrial controls applications.

Data compression is an area where process historians do score a point. However, even this consideration can be handled with standard off-the-shelf SQL relational databases. Take a look at the MySQL 5.0 Archive Storage Engine which achieves on average a four to one compression ratio. Proprietary process historians may beat that, but let’s get back to the point of practicality. Hard disk space is so cheap these days that even considering this point is becoming an anachronism. For the rare application that demands it, table compression coupled with intelligent data logging allow databases to compete even in this regard.

One crucial question that process historian vendors omit is: what are IT departments willing to support? When I make initial contact with IT folks, I always ask which relational database they use. Then I assure them we’ll work with that. This generally makes them very happy. Believe me, you want IT on your side or your project will end up on a data island which is useless in an enterprise system. Think of it from their point view; they have the training and tools, generally, to support just one type of database. With these tools and training they can support the database with scheduled backups, tuning and other maintenance.

Okay, we’ve heard process historian rants about relational databases; let’s talk about the downside of process historians. Let’s start with support. Just check the Amazon bookstore for any one of the proprietary process historians and you’re likely to come up empty handed. On the other hand, check for “SQL configuration” and you’ll come up with hundreds of books. How about finding people to support these proprietary systems? Good luck.

Then there is the concern about supporting relational data with a process historian. Frankly, the middleware layer is all about relational data. Time-series data, which is what process historians deal with, is just a fraction of what is needed in the middleware layer. Correlating batches, shifts, inventory, orders, downtime, quality, etc., is purely relational in nature, and these are the features that today’s enterprise integration projects demand.

What about a cost comparison? The process historian is going to be ten to thirty times the cost of a relational database using a driver like FactorySQL depending on the number of tags required. The controls industry is still backwards on this point and prefers to price its software per tag as though the extra tags cost money to manufacture.

In summary, we’re talking about practical choices. The Ferrari may be great fun, but do you need a $500,000 vehicle to drive the kids to school or would the Camry suffice? Likewise, do you need a $60,000 process historian to log data? A relational database makes a great historian, but the reverse isn’t true. A process historian cannot process relational data. For the vast majority of systems, a relational database has more than enough power to service the historical and relational data requirements, making it not just the practical, but the wise choice.

Steve Hechtman is president of
Inductive Automation (Sacramento, CA), maker of FactorySQL, a product that enables ERP systems to interact with plant floor data directly through SQL databases. This article originally appeared in his blog , accessible through his Website .

Do you agree with Steve, or think he’s blowing smoke? Voice your opinion below in the TalkBack section.

– Edited by Renee Robbins , senior editor
Control Engineering News Desk

Register here and scroll down to select your choice of eNewsletters free.





No comments
The Top Plant program honors outstanding manufacturing facilities in North America. View the 2013 Top Plant.
The Product of the Year program recognizes products newly released in the manufacturing industries.
The Leaders Under 40 program features outstanding young people who are making a difference in manufacturing. View the 2013 Leaders here.
The new control room: It's got all the bells and whistles - and alarms, too; Remote maintenance; Specifying VFDs
2014 forecast issue: To serve and to manufacture - Veterans will bring skill and discipline to the plant floor if we can find a way to get them there.
2013 Top Plant: Lincoln Electric Company, Cleveland, Ohio
Case Study Database

Case Study Database

Get more exposure for your case study by uploading it to the Plant Engineering case study database, where end-users can identify relevant solutions and explore what the experts are doing to effectively implement a variety of technology and productivity related projects.

These case studies provide examples of how knowledgeable solution providers have used technology, processes and people to create effective and successful implementations in real-world situations. Case studies can be completed by filling out a simple online form where you can outline the project title, abstract, and full story in 1500 words or less; upload photos, videos and a logo.

Click here to visit the Case Study Database and upload your case study.

Bring focus to PLC programming: 5 things to avoid in putting your system together; Managing the DCS upgrade; PLM upgrade: a step-by-step approach
Balancing the bagging triangle; PID tuning improves process efficiency; Standardizing control room HMIs
Commissioning electrical systems in mission critical facilities; Anticipating the Smart Grid; Mitigating arc flash hazards in medium-voltage switchgear; Comparing generator sizing software

Annual Salary Survey

Participate in the 2013 Salary Survey

In a year when manufacturing continued to lead the economic rebound, it makes sense that plant manager bonuses rebounded. Plant Engineering’s annual Salary Survey shows both wages and bonuses rose in 2012 after a retreat the year before.

Average salary across all job titles for plant floor management rose 3.5% to $95,446, and bonus compensation jumped to $15,162, a 4.2% increase from the 2010 level and double the 2011 total, which showed a sharp drop in bonus.

2012 Salary Survey Analysis

2012 Salary Survey Results

Maintenance and reliability tips and best practices from the maintenance and reliability coaches at Allied Reliability Group.
The One Voice for Manufacturing blog reports on federal public policy issues impacting the manufacturing sector. One Voice is a joint effort by the National Tooling and Machining...
The Society for Maintenance and Reliability Professionals an organization devoted...
Join this ongoing discussion of machine guarding topics, including solutions assessments, regulatory compliance, gap analysis...
IMS Research, recently acquired by IHS Inc., is a leading independent supplier of market research and consultancy to the global electronics industry.
Maintenance is not optional in manufacturing. It’s a profit center, driving productivity and uptime while reducing overall repair costs.
The Lachance on CMMS blog is about current maintenance topics. Blogger Paul Lachance is president and chief technology officer for Smartware Group.