Last active
February 20, 2017 17:21
-
-
Save bertwagner/989f6d7591135aa44a41bfdd9ac5dc63 to your computer and use it in GitHub Desktop.
Comparing performance of SQL Server 2016 vs Json.Net
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Turn on stats and see how long it takes to parse the ~20k JSON array elements | |
SET STATISTICS TIME ON | |
-- Test #1 | |
-- Test how long it takes to parse each property from all ~20k elements from the JSON array | |
-- SQL returns this query in ~546ms | |
SELECT JSON_VALUE(value, '$.year') AS [Year], JSON_VALUE(value, '$.make') AS Make, JSON_VALUE(value, '$.model') AS Model FROM OPENJSON(@cars, '$') | |
-- Test #2 | |
-- Time to deserialize and query just Golfs without computed column + index | |
-- This takes ~255ms in SQL Server | |
SELECT * FROM OPENJSON(@cars, '$') WHERE JSON_VALUE(value, '$.model') = 'Golf' | |
-- Test #3 | |
-- Time it takes to compute the same query for Golf's with a computed column and clustered index | |
-- This takes ~1ms on SQL Server | |
SELECT * FROM dbo.Cars WHERE CarModel = 'Golf' | |
-- Test #4 | |
-- Serializing data on SQL Server takes ~110ms | |
SELECT * FROM dbo.Cars FOR JSON AUTO | |
-- What about serializing/deserializing smaller JSON datasets? | |
-- Let's create our smaller set | |
DECLARE @carsSmall nvarchar(max) = '[ {"year":2001,"make":"ACURA","model":"CL"}, {"year":2001,"make":"ACURA","model":"EL"}, {"year":2001,"make":"ACURA","model":"INTEGRA"}, {"year":2001,"make":"ACURA","model":"MDX"}, {"year":2001,"make":"ACURA","model":"NSX"}, {"year":2001,"make":"ACURA","model":"RL"}, {"year":2001,"make":"ACURA","model":"TL"}]'; | |
-- Test #5 | |
-- Running our query results in the data becoming deserialized in ~0ms | |
SELECT JSON_VALUE(value, '$.year') AS [Year], JSON_VALUE(value, '$.make') AS Make, JSON_VALUE(value, '$.model') AS Model FROM OPENJSON(@carsSmall, '$') | |
--30ms in sql | |
-- Test #6 | |
-- And serialized in ~0ms | |
SELECT TOP 7 * FROM dbo.Cars FOR JSON AUTO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment