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

Total: 5 Average: 4.2

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.

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';
 
declare @json_u nvarchar(max) = N'[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';
 
declare @xml xml = N'
	32SQL Server 2016T-SQLJSON
	mSQL Server 2014In-Memory OLTP
	25SQL Server 2016In-Memory OLTP
';
 
declare
	@i int,
	@v1 int,
	@v2 varchar(100),
	@start_time datetime,
	@end_time datetime,
	@iterations int = 1000000,
	@path_expression nvarchar(1000),
	@returned_type varchar(100);
 
declare @results table (
	data_type varchar(100) not null,
	test_id tinyint not null,
	path_expression varchar(1000) not null,
	returned_type varchar(1000),
	elapsed_time_ms int not null
);
 
 
set @returned_type = 'int';
set @path_expression = '$[0].age'
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'int';
set @path_expression = '$[0].age'
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json_u, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'int';
set @path_expression = '(/root/rec/age)[1]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = @xml.value('(/root/rec/age)[1]', 'int');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '(/root/rec/@first_name)[3]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('(/root/rec/@first_name)[3]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '/root[1]/rec[3]/skills[1]/skill[1]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('/root[1]/rec[3]/skills[1]/skill[1]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
select *
from @results;

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

Sergey Olontsev

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.