{{announcement.body}}
{{announcement.title}}

SQL to NoSQL: 7 Metrics to Compare Query Languages

DZone 's Guide to

SQL to NoSQL: 7 Metrics to Compare Query Languages

In this article, look at seven metrics to compare SQL and NoSQL.

· Database Zone ·
Free Resource

Organizations who want to take advantage of the many benefits of NoSQL databases, often find that they have two challenges:

  • How to convert their RDBMS schemas to leverage the schema-less document model.
  • Learn a new API/Query to access the data.

Some also find the confusion with the NoSQL name. The abbreviation stands for 'Not only SQL', but it can also be misinterpreted it as 'No to SQL', thus accepting that in order to use NoSQL database, organizations will not only have to convert their relational data model into the document model, but also get training on whichever APIs for the NoSQL database that they will select.

In reality, the NoSQL database industry has never abandoned the most popular data access for databases. Many NoSQL vendors are still using a variation of SQL. Cosmos DBCassandra CQLElasticsearch SQLCockroach Labs. Even with MongoDB query language, you will find that it is based on the select-join-project construct, which is the foundation of relational algebra that is used in SQL.

One database company in this NoSQL space, that has addressed this issue full-on, is Couchbase with its N1QL query language. While Couchbase stores the data in the native JSON format, the data model that it supports can either be relational or hierarchical structure, which is often used in the document-based model for its schema flexibility and extensibility. This is possible because Couchbase provides a SQL like query language - N1QL, which extends the SQL language to allow users to manipulate the hierarchical nature of the document model. All this is built on top of Couchbase high-performance Data Service with its key-value APIs.

But in today's NoSQL database market, the options are numerous, and as the results can pose many challenges for organizations who are looking to ensure that their database technology investments can take advantage of all the many benefits that are currently available with NoSQL technology.

  • The support of structured and unstructured data 
  • Horizontal scalability with commodity hardware
  • Easy to manage schema evolution
  • Perhaps the most important of all, is the option for vendor choice, over and above the current RDBMS vendors who have dominated the database market for the last several decades.

To help customers with the decision, Altoros — a company that focuses on helping companies to shift from their legacy IT system to the future, have published a Query Comparison Report to compare the query language in today’s most popular databases. It has selected to focus on MySQL/SQL, Couchbase N1QL, and MongoDB query languages. Each query language was assessed for their implementations to meet the different query scenarios using the following criteria.

  1. Simplicity
  2. Readability
  3. Expressiveness
  4. Flexibility
  5. Skills availability
  6. Line of codes
  7. The number of application to server trips

All the examples of queries and database dumps, which can help to deploy and run all the scenarios from this report can be found in this GitHub repository.

The Altoros Report Methodology

The goal of the report is to compare the query languages from the perspective of the traditional RDBMS applications. For that it has selected:

An Activity Management application model, that is often found in the most of the CRM systems that manage Sales, Services and Marketing activities. The report setup includes both a relational model for MySQL and the document database model for Couchbase and MongoDB.

It also uses a set of query scenarios that most users of these systems would recognize. 

Scenario

Description

1. Customer Meeting Report

To prepare for customer meetings that I will be attending next week, I want to get a list of all the customers to attend the meetings and their contacts.

2. Regional Sales Territory Report

I am a Regional Sales Manager for the C-Suite Sellers territory. I want to get all accounts assigned to this territory and the account team members.

3. Top 10 Industries from Customers

Determine the top 10 industries from our customers based on the 2018 sales activities.

4. Sales organizations

I want to find out how much time we spent talking to the accounts assigned to my territory for Q3FY19.

5. A sales activity report

How the number of sales-related tasks have changed a month over a month during the year 2018.

6. A Sales team Skill Set

An analysis of the sales team skillsets/roles in the current sales organization

7. Customer interactions report

A query to review all the presentations that we have conducted with the customers in CY19Q4 with detail metrics on time spent on each customer and the effectiveness of the meeting.

8. Analyze the sentiment of hotel reviews

Calling Google Natural Language API to sort all reviews based on the sentiment score

9. A text search report to identify customer meetings

Identify the customer accounts and their related contacts, where a particular topic has been discussed. The search criteria may include the following information partially or in full: a meeting title, a meeting date range, customer contact details, sales team member details (participants), and a customer name.

For each scenario, the report provides the corresponding solutions written in SQL, N1QL and MongoDB query language, then provides the assessment for each solution.

The Assessment Criteria Results

The table below is a summary of all the ratings for all of the query scenarios. Please refer to the report for the individual assessment for each of the query scenarios. 

Using MySQL-SQL as the reference point, the report assesses Couchbase N1QL and MongoDB Query Language based on a number of criteria. 

Notes:

  1. Altoros who has worked with MongoDB, Cassandra, RedisLab has found that N1QL is very similar to SQL, and has consistently given it a more favorable rating than that of the MongoDB query language.

  2. The sample code for scenario #3  shows that the three query languages are relatively similar for the simple query, and have similar assessment criteria ratings. However, the differences appear in query scenarios where document JOIN and Aggregations 

The Number of Lines of Code

The chart shows the number of lines of code for each query. While this metric can be subjected to misrepresentation as all query languages have their own recommended formatting, it can provide a simple guide as to the complexity involved.  

  • The N1QL query language has about the same number of lines of code as SQL.
  • MongoDB query language consistently has more lines of code.
  • For scenario #7, the Altoros team had to write 347 lines for MongoDB query language, compared to 21 lines of N1QL.  This outlier reflects the MongoDB query language limitations to compute complex aggregations and common table expressions (CTE) that SQL, and now N1QL, have always been the key strengths of relational database technology in the past several decades.

Please note that this is the MongoDB query solution provided by the Altoros team. As with any implementation, there could be other ways to write the Mongo code, but this is what the team had come up with. 

The Number of Client Server Trips


The chart shows the number of trips that the application has to submit to the database server. 

Notes:

  1. For most of the scenarios, SQL/N1QL only requires a single query submit to the server, whereas the MongoDB query  can result in multiple parts and thus requires several trips to the backend servers. This is due to the expressiveness of SQL/N1QL, where the application developers simply need to declare the desired output, and it is up to the server to process and returns the results.

  2. The lack of the support of complex aggregation requires MongoDB to perform its calculation in multiple phases. This is similar to the standard SQL sub-query approach. The difference here is that the sub-query result sets need to be maintained in the client applications, which are then subsequently passed on to another query.

The Altoros Query Comparison Report — Key Findings


Resources:

1. The Altoros Query Comparison Report — https://resources.couchbase.com/c/altoros-database-query-report

2. Couchbase N1QL tutorial — https://query-tutorial.couchbase.com/tutorial

3. Index Advisor as a Service for N1QL — https://index-advisor.couchbase.com/indexadvisor

Topics:
nosql database ,relational models ,query language ,database ,sql ,sql vs nosql

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}