JSON parsing is faster than Spatial equivalent

One of the most common assumption when people talk about JSON in SQL Server is that it is slow because it is placed in “plain text” and there is no native type. Instead of the assumption it is better to experiment and compare performance of JSON with some real native type. In this post, I will show you the case where JSON is 10x faster than the equivalent operation with spatial type.

I will compare performance of getting the X coordinate from Spatial type with JSON parsing that returns a property called X from JSON text. Results will show that JSON parsing is much faster in this case.

Experiment

I’m using simple point object represented as a spatial type and “plan text” formatted as JSON. Here are the variables that I’m using:

 DECLARE @point geometry = geometry::STGeomFromText('POINT(3 8)', 0);
DECLARE @json nvarchar(20) = '{"x":3,"y":8}';

First, I will take the X coordinate from @point variable and measure spent time:

 set @dt1 = SYSDATETIME()
set @f = @point.STX
set @dt2 = SYSDATETIME()
set @spentTime += DATEDIFF(nanosecond, @dt1, @dt2)

Then, I will get the X coordinate from JSON text:

 set @dt1 = SYSDATETIME()
set @f = CAST(JSON_VALUE(@json, '$.x') as float)
set @dt2 = SYSDATETIME()
set @spentTime += DATEDIFF(nanosecond, @dt1, @dt2)

I’m running 100.000 iterations and getting the average spent time in both cases. The results are:

1. Spatial type needs around 11-14 microseconds to get the X coordinate
2. JSON parser needs around 1-2 microseconds to get the value from X property (including CAST to float)

We can see that parsing JSON text is much faster than the equivalent operation in Spatial type.

JSON uses NVARCHAR type that is the most optimized type in SQL Server because it is used in most of the queries in SQL Server. JSON parser is based on T-SQL parser that is also very optimized. Spatial has a type, but it is still some binary serialized object that needs to be deserialized when we need to access the fields. In this case, scanning NVARCHAR is much faster than deserializing binary data into spatial.

As a result, we can see that the simple option such as getting the coordinate from an object might be much faster in JSON case even if there is no “native JSON type”.

NOTE: We are not recommending you to change your spatial types and store them as JSON. The true power of spatial is in the spatial index that can significantly improve performance of your spatial queries. Spatial indexes work only with native spatial types and that is the true benefit of Spatial support in SQL Server.

Code

Here you can take the T-SQL code that I have used in this experiment, so you can try it on your server. Note that I have used SQL Server 2017 Express Edition on Windows laptop, so your results might be different.

 

 
DECLARE @dt1 datetime2(7), @dt2 datetime2(7);
DECLARE @spentTime float = 0;
DECLARE @i int = 0;
DECLARE @f float(53);

DECLARE @point geometry = geometry::STGeomFromText('POINT(3 8)', 0);
DECLARE @json nvarchar(20) = '{"x":3,"y":8}';

while(@i<100000)
begin
   set @dt1 = SYSDATETIME()
   --set @f = @point.STX  -- uncomment this line and comment out the following one:
   set @f = CAST(JSON_VALUE(@json, '$.x') as float)
   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'