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