JSON vs CLR UDT - performance comparison

Ability to create custom user defined types in CLR and use them in SQL Server is introduced in SQL Server 2005. In SQL Server 2016 is added JSON support that might be alternative for storing complex objects. Since JSON is stored as NVARCHAR and CLR is a type, it is questionable what would be better approach to store complex objects. In this post, I will show you the case where JSON is 5x faster than the equivalent CLR type.

This is another experiment where I'm comparing JSON with other types such as XML or Spatial. In this experiment, I will compare performance of getting the X coordinate from CLR UDT type with extracting the X property from JSON text parsing. Results will show that JSON parsing is much faster in this case.

Experiment

I’m using simple Point CLR object taken from official documentation and “plan text” formatted as JSON.

Once I create a project, put the Point.cs code in the project and compile it, I can create CLR UDT using the following T-SQL script:

 DROP TYPE IF EXISTS Point;
GO
DROP ASSEMBLY IF EXISTS UDT;
GO
CREATE ASSEMBLY UDT FROM 'C:\path\to\your\assembly\ClrAssembly.dll' WITH PERMISSION_SET = SAFE
GO
CREATE TYPE Point
EXTERNAL NAME UDT.Point

I don't need the overhead of inserting or reading data from the storage, so I'm comparing parsing of values in local variables. Here are the variables that I’m using:

 DECLARE @point Point = '3:8';
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 @x = @point.X
set @dt2 = SYSDATETIME()
set @spentTime += DATEDIFF(nanosecond, @dt1, @dt2)

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

 set @dt1 = SYSDATETIME()
set @x = CAST(JSON_VALUE(@json, '$.x') as int)
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. CLR type needs around 5-7 microseconds to get the value.
2. JSON parser needs around 1-2 microseconds to get the value from X property (including CAST to int).

We can see that parsing JSON text is much faster than the CLR equivalent.

In some cases, you might consider moving simple CLR UDT to NVARCHAR text formatted as JSON if you have bigger performance issues with CLR.

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. Feel free to put the results that you are getting in your environment in comments.

 /*
DROP TYPE IF EXISTS Point;
GO
DROP ASSEMBLY IF EXISTS UDT;
GO
CREATE ASSEMBLY UDT FROM 'D:\Documents\Visual Studio 2017\Projects\SqlClr\ClrRegex\bin\Release\ClrRegex.dll' WITH PERMISSION_SET = SAFE
GO
CREATE TYPE Point
EXTERNAL NAME UDT.Point
*/

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

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

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