According to analysts, “the growth rates of insights-driven businesses makes them an economic tidal wave” – one that will earn \$1.8 trillion dollars by 2021. How does a long-established, Global 500 company, listed in the Top 100 global brands, become an insights-driven business? A leading producer of industrial machinery is taking bold steps in this direction, powered by SingleStore.
Industrial machinery is a broad and varied business. It includes components that go into planes, trains, and automobiles – and the machines that make and power those components. The largest industrial machinery companies are global and have hundreds of thousands of employees. These might seem to be classic “old economy” businesses – primarily involved in making things, not software or services, and requiring a great deal of capital and people to get things done. Yet leading companies in this segment are well-represented on the cutting edge of technology, and are heavy users of SingleStore.
As with other cutting-edge businesses, industrial machinery companies need speed, across their global operations. For the SingleStore customer profiled here, the immediate need for speed was in analytics. Like many large organizations, the company has an internal financial reporting application, which we’ll refer to here as Cadence.
The Need for Speed in Cadence
Cadence is a high-visibility product within this global company. In fact, given that the company has hundreds of thousands of employees, along with a large network of suppliers, customers, and other stakeholders, Cadence is a widely-used and well-known application by any standard.
Cadence supports direct SQL queries from a wide range of users, and from widely used business intelligence (BI) tools such as Dundas BI, Looker, Microsoft Power BI, and Tableau. Cadence users include personnel at all levels of the company, including the very top. Users were eager to get the data Cadence provided, and dashboards and custom applications made the data easily accessible and actionable – at first.
However, as the demand to use Cadence grew, performance became a problem. Like many large companies, this one is a Microsoft shop for many of its software needs. With site licensing in use for many products, the cost per seat for Microsoft SQL Server was negligible. Yet the cost of poor performance was too great to ignore. (We have a lot of SQL Server experience among the SingleStore product team, including the author of our very popular blog post on the problems with NoSQL.)
To try to achieve acceptable performance in SQL Server, the company had tried using the cubes feature of SQL Server Analysis Services. The cubes feature was required because it brings all needed data into memory, which was the only way to get performance somewhere close to what the company needed to power dashboards.
The cubes feature, though, requires the creation of a data warehouse and other steps. One of the project leaders described the process of creating data cubes as “really data-intensive” and “prone to error.” Loading the cubes into memory took hours.
So the company was stuck with a slow, hard to use, and complex data infrastructure that was not meeting its needs in powering Cadence and other analytics needs. They went looking for an alternative.
Finding a New Solution
Personnel at the company Googled several terms related to what they needed, including “fastest in-memory database” – and found SingleStore. The ironic thing here is that SingleStore has both in-memory rowstore tables and disk-based columnstore tables, both with excellent performance.
The two outstanding aspects of SingleStore, in relation to this company’s needs, are incorporated into the company’s name:
- “Mem” for performance. The “Mem” in SingleStore represents SingleStore’s in-memory rowstore and memory-led, disk-based columnstore tables. The company needs near-real-time responsiveness, including for complex operations, and they get it with SingleStore.
- “SQL” for compatibility. In the SingleStore name, SQL represents that SingleStore is a fully relational database (not a NoSQL database), with full ANSI SQL support. The company uses a wide range of business tools, and has internal SQL expertise, so compatibility is vital.
SingleStore is a fully scalable relational database that supports SQL. Due to SQL support, it can be used as a nearly drop-in replacement for most existing relational databases. The built-in MySQL wire protocol support in SingleStore means that it works “out of the box” with a very wide range of BI tools, including the ones this company uses. (This is in stark contrast to NoSQL databases, which are scalable, but lack SQL support. This makes them both difficult to write queries for and slow to respond to queries.)
SingleStore works very well as a drop-in replacement for slow-performing analytics databases. Our recent webinar on data migration shows how easily customers can move to SingleStore for this purpose.
What makes SingleStore stand out, compared to those other tools, is performance, across both rowstore and columnstore tables. The company ran tests that took 40 seconds in Amazon Aurora, two seconds in SQL Server, and 30ms in SingleStore. A test of 100 different SQL statements took 750ms in SQL Server and only 9ms in SingleStore.
After a thorough review, they decided to make the move to SingleStore.
Running Orders of Magnitude Faster with SingleStore
Initially, getting started with SingleStore took some work. As a fully distributed relational database, using SingleStore “required some study to understand how the database works, and set up good sort and shard keys,” according to the project lead.
Now, compared to the former, SQL Server-based solution, running SingleStore is much simpler. According to the project lead, “The installation of SingleStore was straightforward, and we are now seeing 10x to 100x performance improvements compared to MSSQL.”
The company is able to get the performance they need, at low cost, using SingleStore’s disk-based columnstore tables in SingleStore. Only a few tables are in rowstore, which runs faster for many operations, but is memory-based – and therefore more expensive to run. With SQL Server Analytics Services, by contrast, the cubes that supported both BI tools and apps had to run entirely in memory.
Now, with SingleStore, dashboards update much faster. The company processes tables with hundreds of millions of rows in SingleStore, totalling more than half a terabyte in size, and still has plenty of headroom for growth. They can respond, in real time, to critical issues and opportunities alike.
The company now has the performance it needs, with compatibility and operational simplicity. The project lead describes his team as “very happy” with SingleStore.
SingleStore has performed so well that the company now has big plans for replacing MSSQL and other database implementations with SingleStore instances. “We will move our three biggest projects to SingleStore in the next few months,” said the project lead. “After that, all our new projects will use SingleStore. Existing projects will be migrated as we have time.”
Getting Started with SingleStore
If you have workloads that require fast ingest, fast processing of large data tables, and fast queries, in operational analytics or other use cases, try SingleStore for free today, or contact us to learn how we can help you.
