A NoSQL Workshop: Small Efforts Turned Into Big Savings and Increased Performance

Science Warehouse, a procurement software provider, was planning for significant growth over a 12-18 month period. The predicted volume of data and number of concurrent operations would have been severely limited by their current installation of Oracle Database Standard Edition. Since their current license did not allow further hardware upgrades, basically, they had two possible options:

Science Warehouse brings cloud-based services and data analysis technologies to procurement organizations. The company offers a range of solutions that simplify purchasing processes by delivering savings and provide real-time desicion support based on collected analytical data.

  • Around 10–20 GB of data from different sources is added to the database weekly.

  • The Oracle database contains approximately 12,000,000 products data and 500,000,000 price records.

  • The procurement solution is capable of setting different prices for different groups of customers.

  • The archived price history contains nearly 400,000,000 of records.

  • A lot of computing operations are required to properly analyze and visualize data. Several processes are running concurrently: new data is added to the database; users search for products, create orders, and carry out different kinds of transactions.

The customer wanted to preserve the core architecture of the existing Java application and keep the current version of the Oracle database to avoid additional costs. Apart from buying a more expensive license to upgrade to the Oracle Database Enterprise Edition, the company would have to migrate data from the legacy database to a new store and spend time on testing, tuning, etc. Science Warehouse wanted to check out if there was a NoSQL solution that could help them to improve performance without huge investments like this.

  • Introduction to the NoSQL paradigm and overview of the pros and cons of the traditional SQL databases

  • Comparison of two approaches: storing all data in one centralized database vs. storing different types of data in several application databases

  • A run through the different types of NoSQL databases: key/value, column family, document, and graph—including comparison of their current SQL queries against the various NoSQL queries

  • Instructions on data modeling, based on the architecture of the Science Warehouse system

  • Practical recommendations on how to improve the system — including current Oracle database architecture and queries — and suggestions on using NoSQL databases to solve particular tasks

While the agenda of the first day, as described above, mainly covered the theoretical aspects of the new non-relational databases, the second day was dedicated to practical examples and actual suggestions for optimization. Altoros’s big data experts pointed out some bottlenecks that were detected while reviewing the system and the database that had been carried out prior to the workshop. They provided some recommendations on how to improve the system and also demonstrated how this can be done.

Altoros suggested using the polyglot persistence approach for optimizing the Science Warehouse solution. It would enable the company to store different types of data in different databases: transactional data would be stored in the Oracle SQL database, while non-sensitive, quickly growing data (such as logging information, price history, etc.) would be kept in a NoSQL database.

  • Add a caching layer that would be based on a document store (e.g. Couchbase Server or MondoDB) to speed up access to the most popular items in the catalogue. Altoros’s engineers analyzed the most popular queries and found out that there were millions of simple queries that consumed a lot of database and network resources. For instance, one query was executed nearly 70 million times and the database server processed more than 9 TB of data. With the aspect-oriented programming approach, the caching layer could be seamlessly weaved into the Science Warehouse system without making significant changes to the business logic of the solution.

  • Write the log data on visitor activities on the Web site to a Column Family store (e.g. Cassandra or HBase). Previously, this information was recorded to a single table of the Oracle database. Since this data is never being deleted, the table grows constantly. Event logs are not a kind of sensitive business data and they do not require support for ACID characteristics. So, this data can be easily recorded to a separate NoSQL store that can be seamlessly integrated into the system.

  • Move a price history data to a Column Family store (e.g. Cassandra or HBase). This option will allow for building analytics reports using data from a NoSQL database instead of querying the main Oracle database.

During a two-day workshop, Science Warehouse’s engineers got enough detailed information on NoSQL to evaluate the possibilities and start prototyping themselves. The recommendations from Altoros helped the company to point in the right direction for the future of their system and define the areas they should focus on. The given practical examples continuously referred to their current setup and everyday work, which helped Science Warehouse to rapidly see the difference between SQL and NoSQL solutions and learn about the possibilities of a multi-database system. The company’s engineers analyzed Altoros’s recommendations and started prototyping the suggested features.

The workshop showed that it is not necessary to re-write a system from scratch or even refactor it to solve scalability and performance issues. The NoSQL database technologies can quite easily be introduced into existing systems to speed up operations, gain scalability, and avoid upgrading to expensive licenses.

Customer Speaks

Mr. Jon Moody

Product and Technology Director, Science Warehouse

    “Altoros’s profound knowledge and overview of big data technologies really helped our engineers to save a lot of time—and money, too. Our business-critical products will be optimized significantly based on the workshop we did with Altoros and the features suggested by this company that we are prototyping now. I can highly recommend Altoros’s engineers to help improve software products using cutting-edge technologies.”

    Want to develop something similar?

    © 2001 - 2016 Altoros