SQL Server 2016: JSON, Part 6 – JSON vs XML Performance Comparison

Total: 4 Average: 4

We are approaching the most interesting part of these article series. I have mentioned that developers give preferences to JSON as it has clear structure and smaller data size comparing to XML.

In this article, we are going to compare JSON vs XML performance.

To do this, I have created a script, which measures the speed of the JSON_VALUE statement and the value() method for XML. For JSON, I have decided to check how the data retrieval of both varchar(max) and nvarchar(max) works. I have created JSON and XML queries similar in the structure. Now, we will try to retrieve different data types (numeric and string data types) from various file parts.

To be honest, the result has surprised me. I would like to note that data selection from the JSON file stored in the nvarchar(max) type is processed faster by 5-15%, comparing to the standard type without Unicode. However, we should have got a vice versa result. It turns out that it is beneficial to process JSON stored in the Unicode format. In addition, data retrieval from JSON is 2 – 10 times faster comparing to XML. Thus, I highly recommend using JSON instead XML whenever it is possible.

Also Read

SQL Server 2016: JSON, Part 1 – Data Extraction

SQL Server 2016: JSON, Part 2 – Data Generation

SQL Server 2016: JSON, Part 3 – Transformation into Relational Structure

SQL Server 2016: JSON, Part 4 – Data Modification

SQL Server 2016: JSON, Part 5 – Storage and Indexation

Sergey Olontsev

Software Expert at Kaspersky Lab
Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.
Sergey Olontsev