Fast & Furious (JSON Support)

Summary

QueryableValues achieves its performance by relying on parameterized T-SQL queries. This is achieved by taking advantage of the XML data type support in SQL Server, which is available in all the supported versions of SQL Server to date. JSON support was introduced in SQL Server 2016, and it provides better query performance when compared to its XML counterpart.

This version adds support for JSON as an alternative to XML. When available, it provides a significant performance boost on certain scenarios as shown in the benchmarks below.

JSON support is available when the following is true:

• The SQL Server instance is 2016 and above.
• The database has its compatibility level set to 130 or higher.

Configuration

By default, this version of QueryableValues automatically detects if JSON can be used, otherwise it falls back to XML. This behavior can be configured via the QueryableValuesConfigurator class:

QueryableValuesConfigurator
    .Configure()
    .Serialization(SerializationOptions.Auto);

The Configure method also allows you to specify the configuration on a per DbContext fashion:

QueryableValuesConfigurator
    .Configure<MyDbContext>()
    .Serialization(SerializationOptions.Auto);

You may want to use SerializationOptions.UseJson if you know that your environment does support JSON, or SerializationOptions.UseXml if it does not. By not using SerializationOptions.Auto you can avoid a one-time initial roundtrip that is done to detect JSON support (this only happens once per connection string).

Benchmarks

These benchmarks are using XML as the baseline for the Int32, String, and Guid data types. They consist of a query that does a JOIN between a table and a set of values via the AsQueryableValues extension method. The benchmarks can be found in the benchmarks directory of this repository.

The most dramatic performance gains can be observed on the Int32 data type, particularly between 1024 and 16384 values. At 16384 we can see that JSON is up to 13.25x faster than XML.

System

BenchmarkDotNet=v0.13.2, OS=Windows 11 (10.0.22621.521)
AMD Ryzen 9 6900HS Creator Edition, 1 CPU, 16 logical and 8 physical cores
.NET SDK=6.0.400
  [Host]     : .NET 6.0.8 (6.0.822.36306), X64 RyuJIT AVX2
  Job-CHWPLZ : .NET 6.0.8 (6.0.822.36306), X64 RyuJIT AVX2

Runtime=.NET 6.0  Server=True  InvocationCount=12  
IterationCount=10  RunStrategy=Monitoring  UnrollFactor=1  
WarmupCount=1  

SQL Server Instance (running on the same computer)

Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64)   Jul 19 2021 15:37:34   Copyright (C) 2019 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 22621: ) (Hypervisor) 

Serializer	Type	No. of Values	Mean	Error	StdDev	Ratio	RatioSD
XML	Int32	2	898.4 μs	309.83 μs	204.93 μs	1.00	0.00
JSON	Int32	2	727.2 μs	95.11 μs	62.91 μs	0.84	0.16
XML	Int32	4	864.1 μs	67.88 μs	44.90 μs	1.00	0.00
JSON	Int32	4	729.5 μs	56.51 μs	37.38 μs	0.85	0.07
XML	Int32	8	897.2 μs	68.45 μs	45.27 μs	1.00	0.00
JSON	Int32	8	733.9 μs	46.96 μs	31.06 μs	0.82	0.06
XML	Int32	16	1,031.7 μs	358.94 μs	237.41 μs	1.00	0.00
JSON	Int32	16	725.5 μs	34.74 μs	22.98 μs	0.73	0.12
XML	Int32	32	1,042.6 μs	52.87 μs	34.97 μs	1.00	0.00
JSON	Int32	32	732.8 μs	47.17 μs	31.20 μs	0.70	0.04
XML	Int32	64	1,295.3 μs	81.40 μs	53.84 μs	1.00	0.00
JSON	Int32	64	736.5 μs	47.40 μs	31.35 μs	0.57	0.03
XML	Int32	128	1,845.9 μs	408.16 μs	269.98 μs	1.00	0.00
JSON	Int32	128	785.5 μs	54.61 μs	36.12 μs	0.43	0.04
XML	Int32	256	2,771.4 μs	370.15 μs	244.83 μs	1.00	0.00
JSON	Int32	256	814.3 μs	46.68 μs	30.87 μs	0.30	0.03
XML	Int32	512	4,887.4 μs	491.57 μs	325.14 μs	1.00	0.00
JSON	Int32	512	1,038.1 μs	371.97 μs	246.03 μs	0.21	0.06
XML	Int32	1024	8,213.6 μs	430.26 μs	284.59 μs	1.00	0.00
JSON	Int32	1024	1,276.8 μs	72.12 μs	47.70 μs	0.16	0.01
XML	Int32	2048	14,809.4 μs	420.00 μs	277.81 μs	1.00	0.00
JSON	Int32	2048	1,817.0 μs	93.85 μs	62.08 μs	0.12	0.00
XML	Int32	4096	27,859.6 μs	224.83 μs	148.71 μs	1.00	0.00
JSON	Int32	4096	2,836.3 μs	282.18 μs	186.65 μs	0.10	0.01
XML	Int32	8192	53,160.4 μs	623.33 μs	412.29 μs	1.00	0.00
JSON	Int32	8192	4,769.1 μs	383.36 μs	253.57 μs	0.09	0.00
XML	Int32	16384	106,420.7 μs	624.87 μs	413.31 μs	1.00	0.00
JSON	Int32	16384	8,028.5 μs	439.35 μs	290.60 μs	0.08	0.00
XML	Int32	32768	211,579.2 μs	4,253.48 μs	2,813.41 μs	1.00	0.00
JSON	Int32	32768	45,521.2 μs	571.41 μs	377.95 μs	0.22	0.00
XML	Int32	65536	415,989.6 μs	2,353.38 μs	1,556.62 μs	1.00	0.00
JSON	Int32	65536	85,472.2 μs	696.38 μs	460.61 μs	0.21	0.00
XML	Int32	131072	827,467.1 μs	2,622.26 μs	1,734.46 μs	1.00	0.00
JSON	Int32	131072	172,388.8 μs	2,185.62 μs	1,445.65 μs	0.21	0.00
XML	String	2	838.1 μs	38.12 μs	25.21 μs	1.00	0.00
JSON	String	2	781.2 μs	107.94 μs	71.39 μs	0.93	0.10
XML	String	4	850.8 μs	44.99 μs	29.76 μs	1.00	0.00
JSON	String	4	740.0 μs	35.80 μs	23.68 μs	0.87	0.04
XML	String	8	855.5 μs	54.26 μs	35.89 μs	1.00	0.00
JSON	String	8	766.6 μs	47.56 μs	31.45 μs	0.90	0.05
XML	String	16	870.5 μs	38.86 μs	25.70 μs	1.00	0.00
JSON	String	16	788.9 μs	62.18 μs	41.13 μs	0.91	0.05
XML	String	32	969.6 μs	75.21 μs	49.74 μs	1.00	0.00
JSON	String	32	829.9 μs	43.18 μs	28.56 μs	0.86	0.05
XML	String	64	1,132.2 μs	67.71 μs	44.79 μs	1.00	0.00
JSON	String	64	919.4 μs	47.51 μs	31.43 μs	0.81	0.05
XML	String	128	1,411.8 μs	111.00 μs	73.42 μs	1.00	0.00
JSON	String	128	1,104.9 μs	55.01 μs	36.39 μs	0.78	0.02
XML	String	256	2,195.5 μs	88.26 μs	58.38 μs	1.00	0.00
JSON	String	256	1,592.6 μs	164.13 μs	108.56 μs	0.73	0.05
XML	String	512	3,797.8 μs	369.74 μs	244.56 μs	1.00	0.00
JSON	String	512	2,244.4 μs	152.95 μs	101.17 μs	0.59	0.05
XML	String	1024	6,130.0 μs	458.77 μs	303.45 μs	1.00	0.00
JSON	String	1024	4,436.5 μs	519.10 μs	343.35 μs	0.72	0.05
XML	String	2048	10,652.8 μs	818.38 μs	541.31 μs	1.00	0.00
JSON	String	2048	7,182.6 μs	863.99 μs	571.48 μs	0.68	0.06
XML	String	4096	19,073.8 μs	913.31 μs	604.09 μs	1.00	0.00
JSON	String	4096	12,094.1 μs	1,003.52 μs	663.77 μs	0.63	0.02
XML	String	8192	36,900.7 μs	378.68 μs	250.47 μs	1.00	0.00
JSON	String	8192	32,947.0 μs	1,761.03 μs	1,164.81 μs	0.89	0.03
XML	String	16384	71,615.6 μs	584.28 μs	386.46 μs	1.00	0.00
JSON	String	16384	59,320.7 μs	998.85 μs	660.68 μs	0.83	0.01
XML	String	32768	137,495.2 μs	952.20 μs	629.82 μs	1.00	0.00
JSON	String	32768	113,267.7 μs	930.92 μs	615.74 μs	0.82	0.01
XML	String	65536	269,414.8 μs	6,212.20 μs	4,108.99 μs	1.00	0.00
JSON	String	65536	223,019.1 μs	1,169.58 μs	773.60 μs	0.83	0.01
XML	String	131072	533,535.1 μs	8,341.72 μs	5,517.53 μs	1.00	0.00
JSON	String	131072	441,343.4 μs	4,939.26 μs	3,267.02 μs	0.83	0.00
XML	Guid	2	889.5 μs	41.38 μs	27.37 μs	1.00	0.00
JSON	Guid	2	842.8 μs	64.40 μs	42.60 μs	0.95	0.07
XML	Guid	4	930.8 μs	64.83 μs	42.88 μs	1.00	0.00
JSON	Guid	4	836.1 μs	55.92 μs	36.99 μs	0.90	0.06
XML	Guid	8	947.8 μs	78.82 μs	52.14 μs	1.00	0.00
JSON	Guid	8	847.8 μs	100.07 μs	66.19 μs	0.90	0.10
XML	Guid	16	996.9 μs	76.10 μs	50.34 μs	1.00	0.00
JSON	Guid	16	949.3 μs	265.27 μs	175.46 μs	0.95	0.17
XML	Guid	32	1,042.2 μs	77.45 μs	51.23 μs	1.00	0.00
JSON	Guid	32	869.2 μs	41.48 μs	27.44 μs	0.84	0.04
XML	Guid	64	1,133.6 μs	53.92 μs	35.66 μs	1.00	0.00
JSON	Guid	64	900.6 μs	58.49 μs	38.69 μs	0.80	0.04
XML	Guid	128	1,338.3 μs	49.86 μs	32.98 μs	1.00	0.00
JSON	Guid	128	1,035.6 μs	135.66 μs	89.73 μs	0.77	0.07
XML	Guid	256	2,162.5 μs	438.75 μs	290.21 μs	1.00	0.00
JSON	Guid	256	1,371.8 μs	373.14 μs	246.81 μs	0.64	0.13
XML	Guid	512	3,241.5 μs	247.05 μs	163.41 μs	1.00	0.00
JSON	Guid	512	1,622.7 μs	112.37 μs	74.33 μs	0.50	0.03
XML	Guid	1024	4,897.7 μs	321.94 μs	212.94 μs	1.00	0.00
JSON	Guid	1024	2,346.5 μs	146.43 μs	96.85 μs	0.48	0.03
XML	Guid	2048	8,726.8 μs	365.20 μs	241.55 μs	1.00	0.00
JSON	Guid	2048	3,954.9 μs	362.21 μs	239.58 μs	0.45	0.03
XML	Guid	4096	15,770.8 μs	532.20 μs	352.01 μs	1.00	0.00
JSON	Guid	4096	6,148.5 μs	464.99 μs	307.56 μs	0.39	0.02
XML	Guid	8192	31,515.9 μs	502.29 μs	332.23 μs	1.00	0.00
JSON	Guid	8192	19,518.6 μs	908.79 μs	601.11 μs	0.62	0.02
XML	Guid	16384	58,049.5 μs	902.44 μs	596.91 μs	1.00	0.00
JSON	Guid	16384	36,028.5 μs	488.03 μs	322.80 μs	0.62	0.01
XML	Guid	32768	111,932.4 μs	1,103.80 μs	730.10 μs	1.00	0.00
JSON	Guid	32768	67,418.6 μs	739.21 μs	488.94 μs	0.60	0.00
XML	Guid	65536	218,151.9 μs	1,353.40 μs	895.19 μs	1.00	0.00
JSON	Guid	65536	130,093.4 μs	899.34 μs	594.86 μs	0.60	0.00
XML	Guid	131072	431,397.2 μs	3,221.56 μs	2,130.86 μs	1.00	0.00
JSON	Guid	131072	253,950.0 μs	1,194.62 μs	790.17 μs	0.59	0.00

Conclusion

I am pretty happy with the results 🙂. Next stop? Add this same feature to BlazarTech.QueryableValues, which is being tracked by yv989c/BlazarTech.QueryableValues#19.

That's all for now. ✌️