MongoDB vs SQL Server 2016 Performance

One of the dilemmas many architects face when designing a system is: To SQL or to NoSQL?

Of course, this is not a “One size fits all” question, and the answers will vary based on many factors: Schema flexibility, type of queries, importance of transactions, scale and more.

A few weeks ago, with one of my clients, we faced the same dilemma. Our conclusion was to use NoSQL, and store our data (of this specific type) as a JSON string, knowing we will have to find an efficient way to query it later.

So the question now became: Which NoSQL database should we use?

As of today, the quite natural answer for this question will usually be “MongoDB, of course!”. But we, as petty as we are, decided to make an informed decision.

The service we were working on was a logging service. In a very early point it was decided that the most important aspect of this service should be performance. We knew this service is going to be queried a lot, and we wanted to make sure the calling services will get the answer in a timely manner.

Since the organization’s IT supported SQL Server 2016 in addition to MongoDB, we decided to compare the performance of those two databases.

We devised a test that was supposed to demonstrate which database – SQL Server 2016 or MongoDB -was faster when querying the JSON data.

It’s important to note that our test measured querying only, and not other types of data actions (ie. Insert, update, delete). The reason is, as stated above, that we knew we’re going to get a lot of queries, and wanted to make sure they’ll perform very well under load.

So here is what we did:

1. We loaded 100K records with JSON data to each database. The records are not too large, and contain ~500 bytes each.
2. We tested 5 types of queries on the data. Each query was designed to demonstrate a different type of query behavior we anticipated our users to execute. The queries were:
   1. Retrieve the whole 100K records
   2. Query by a date range
   3. Query by a single parameter
   4. Query by 4 parameters
   5. Query by nested property of the JSON

We ran each test 1000 times, for each database, and then got the average of the results.

Before posting the results, some technical info:

• We didn’t make any special tuning or configuration change on either of the databases. We used the default installation on both.
• We didn’t define any index on either database.
• The code for querying the databases was written in .NET Core.
• The machine we used for testing has i7-4785T 2.20GHz CPU, 8GB RAM, SSD Drive, Windows 10.

And here are the results:

Surprised? Me too… I was sure MongoDB will be the clear winner here, but the actual results show quite the opposite. In 4 out of our 5 tests, SQL Server 2016 was the faster DB, in some cases by a large margin.

Only in one type of query, nested properties, MongoDB came out the winner, but even in this case it was a very close call.

For our system this was quite a convincing test, and we went with SQL Server 2016.

However, this test was tailor made for our scenarios, and your mileage may vary.

The important lesson for me here was – never assume anything. Always test, and work by the numbers.