JSON parsing 10x faster than XML parsing

First published on MSDN on Nov 13, 2017
Usually, when people talk about the performance of JSON in SQL Server, they are starting with “JSON is not actually a native type, it is just a plain text” and imply that it is probably too slow. It is true that JSON is stored as NVARCHAR and that there is no special type like for XML or Spatial. However, it does not imply that performance is worse than performance of full-blown types. NVARCHAR is not a plain text as someone would assume – it is probably the most optimized non-scalar type in SQL Server. JSON leverages simplicity of NVARCHAR and this might be one of the biggest strengths and not weaknesses. In this post, I will compare performance of XML and JSON in a simple scenario – accessing a field on a specified path, and show that paring JSON plain text might be 10x faster than using native types.

Experiment

In this experiment, I’m using the same structure of JSON and XML and trying to get a value on the specified path (XPATH for XML and JSON path for JSON). Here are sample JSON/XML documents used in this experiment.
DECLARE @json AS NVARCHAR(200) = '{"Root":{"ProductDescription":{"ProductID":"1","ProductName": "Road Bike","Features": {"Warranty": "1 year parts and labor","Maintenance": "3 year parts and labor extended maintenance is available"}}}}'
DECLARE @xml as xml = N'<?xml version="1.0"?><Root><ProductDescription><Features><Maintenance>3 year parts and labor extended maintenance is available</Maintenance><Warranty>1 year parts and labor</Warranty></Features><ProductID>1</ProductID><ProductName>Road Bike</ProductName></ProductDescription></Root>'
XML text is slightly bigger, but it is pre-parsed into strongly typed XML variable so I’m not counting the time needed to process XML (this is even the best case for XML variable although sometime not realisic case).
I’m measuring the time needed to get the value both from XML and JSON on a specified path.

I have used the following code to test XML value method:
set @dt1 = SYSDATETIME()
set @s = @xml.value('(/Root/ProductDescription/ProductName)[1]', 'nvarchar(4000)')
set @dt2 = SYSDATETIME()
set @spentTime += DATEDIFF(nanosecond, @dt1, @dt2)

And for JSON I have the similar code:
set @dt1 = SYSDATETIME()
set @s = JSON_VALUE(@json, N'$.Root.ProductDescription.ProductName')
set @dt2 = SYSDATETIME()
set @spentTime += DATEDIFF(nanosecond, @dt1, @dt2)
I’m running 10000 iterations and getting the average spent time.

Average time to parse JSON and get the value using JSON_VALUE() function is around 3 microseconds while the equivalent action with XML typed variable and value() method takes between 30 and 40 microseconds .

This shows that parsing JSON “plain text” is 10x faster than parsing strongly typed XML variable.

Code

The code used in this experiment is shown below so you can try it on your server. I have used SQL Server 2017 Express edition in this experiment, and you might get different result in your environment.
XML Test

DECLARE @dt1 datetime2(7), @dt2 datetime2(7)

declare @spentTime float = 0

declare @i int = 0

declare @s nvarchar(100)

while(@i<100000)
begin
DECLARE @xml as xml = N'<?xml version="1.0"?><Root><ProductDescription><Features><Maintenance>3 year parts and labor extended maintenance is available</Maintenance><Warranty>1 year parts and labor</Warranty></Features><ProductID>1</ProductID><ProductName>Road Bike</ProductName></ProductDescription></Root>'

-- Start
set @dt1 = SYSDATETIME()
set @s = @xml.value('(/Root/ProductDescription/ProductName)[1]', 'nvarchar(4000)')
set @dt2 = SYSDATETIME()
set @spentTime += DATEDIFF(nanosecond, @dt1, @dt2)

set @i = @i+1

end;

SELECT 'Execution time is ' + CAST( (@spentTime / 100000 ) as nvarchar(100) ) + ' nano-seconds per call'
JSON Test
DECLARE @dt1 datetime2(7), @dt2 datetime2(7)
declare @spentTime float = 0
declare @i int = 0
declare @s nvarchar(100)

while(@i<100000)
begin

DECLARE @json AS NVARCHAR(200) = '{"Root":{"ProductDescription":{"ProductID":"1","ProductName": "Road Bike","Features": {"Warranty": "1 year parts and labor","Maintenance": "3 year parts and labor extended maintenance is available"}}}}'

-- Start
set @dt1 = SYSDATETIME()
set @s = JSON_VALUE(@json, N'$.Root.ProductDescription.ProductName')
set @dt2 = SYSDATETIME()
set @spentTime += DATEDIFF(nanosecond, @dt1, @dt2)

set @i = @i+1

end;

SELECT 'Execution time is ' + CAST( (@spentTime / 100000) as nvarchar(100) ) + ' nano-seconds per call'