SQL Server 2016/2017: особенности работы с JSON
Когда много лет подряд Microsoft лихорадит от одной крайности до другой, то понемногу начинаешь привыкать к этому и все новое ждешь с неким скепсисом. Со временем это чувство усиливается, и подсознательно ничего хорошего уже не ожидаешь.
Но иногда все получается в точности до наоборот. Microsoft вываливает из коробки идеально работающий функционал, который рвет все жизненные стереотипы. Ты ждешь от новой плюшки какого-то подвоха, но с каждой минутой все больше понимаешь, что именно этого тебе не хватало все эти годы.
Такое пафосное вступление имеет определенные основания, поскольку долгое время на Microsoft Connect поддержка работы с JSON в SQL Server была одной из самых востребованных фич. Шли годы, и неожиданно для всех этот функционал реализовали в SQL Server 2016. По факту вышло очень даже хорошо, но Microsoft не остановилась на этом и в SQL Server 2017 обстоятельно улучшила производительность и без того быстрого парсера JSON.
Но давайте обо всем по порядку...
1. Datatypes
Поддержка JSON на SQL Server изначально доступна для всех редакций. При этом отдельного типа данных, как в случае с XML, Microsoft не предусмотрела. Данные в JSON на SQL Server хранятся как обычный текст: в Unicode (NVARCHAR/NCHAR) либо ANSI (VARCHAR/CHAR) формате.
DECLARE @JSON_ANSI VARCHAR(MAX) = '[{"Nąme":"Lenōvo モデ460"}]'
, @JSON_Unicode NVARCHAR(MAX) = N'[{"Nąme":"Lenōvo モデ460"}]'
SELECT @JSON_ANSI, DATALENGTH(@JSON_ANSI)
UNION ALL
SELECT @JSON_Unicode, DATALENGTH(@JSON_Unicode)
Главное, о чем нужно помнить: сколько места занимает тот или иной тип данных (2 байта на символ, если храним данные как Unicode, или 1 байт для ANSI строк). Также не забываем перед Unicode константами ставить «N». В противном случае можно нарваться на кучу веселых ситуаций:
--- ----------------------------
25 [{"Name":"Lenovo ??460"}]
50 [{"Nąme":"Lenōvo モデ460"}]
Вроде все просто, но нет. Дальше мы увидим, что выбранный тип данных влияет не только на размер, но и на скорость парсинга.
Кроме того, Microsoft настоятельно рекомендует не использовать deprecated типы данных — NTEXT/TEXT. Для тех, кто в силу привычки их до сих пор использует, мы сделаем небольшой следственный эксперимент:
DROP TABLE IF EXISTS #varchar
DROP TABLE IF EXISTS #nvarchar
DROP TABLE IF EXISTS #ntext
GO
CREATE TABLE #varchar (x VARCHAR(MAX))
CREATE TABLE #nvarchar (x NVARCHAR(MAX))
CREATE TABLE #ntext (x NTEXT)
GO
DECLARE @json NVARCHAR(MAX) =
N'[{"Manufacturer":"Lenovo","Model":"ThinkPad E460","Availability":1}]'
SET STATISTICS IO, TIME ON
INSERT INTO #varchar
SELECT TOP(50000) @json
FROM [master].dbo.spt_values s1
CROSS JOIN [master].dbo.spt_values s2
OPTION(MAXDOP 1)
INSERT INTO #nvarchar
SELECT TOP(50000) @json
FROM [master].dbo.spt_values s1
CROSS JOIN [master].dbo.spt_values s2
OPTION(MAXDOP 1)
INSERT INTO #ntext
SELECT TOP(50000) @json
FROM [master].dbo.spt_values s1
CROSS JOIN [master].dbo.spt_values s2
OPTION(MAXDOP 1)
SET STATISTICS IO, TIME OFF
Скорость вставки в последнем случае будет существенно различаться:
#varchar: CPU time = 32 ms, elapsed time = 28 ms
#nvarchar: CPU time = 31 ms, elapsed time = 30 ms
#ntext: CPU time = 172 ms, elapsed time = 190 ms
Кроме того, нужно помнить, что NTEXT/TEXT всегда хранятся на LOB страницах:
SELECT obj_name = OBJECT_NAME(p.[object_id])
, a.[type_desc]
, a.total_pages
, total_mb = a.total_pages * 8 / 1024.
FROM sys.allocation_units a
JOIN sys.partitions p ON p.[partition_id] = a.container_id
WHERE p.[object_id] IN (OBJECT_ID('#nvarchar'), OBJECT_ID('#ntext'), OBJECT_ID('#varchar'))
obj_name type_desc total_pages total_mb
------------- -------------- ------------ -----------
#varchar IN_ROW_DATA 516 4.031250
#varchar LOB_DATA 0 0.000000
#nvarchar IN_ROW_DATA 932 7.281250
#nvarchar LOB_DATA 0 0.000000
#ntext IN_ROW_DATA 188 1.468750
#ntext LOB_DATA 1668 13.031250
Для справки, начиная с SQL Server 2005 для типов с переменной длиной поменяли правило «На каких страницах хранить данные». В общем случае, если размер превышает 8060 байт, то данные помещаются на LOB страницу, иначе хранятся в IN_ROW. Понятно, что в таком случае SQL Server оптимизирует хранение данных на страницах.
И последний довод не использовать NTEXT/TEXT — это тот факт, что все JSON функции с deprecated типами данных банально не дружат:
SELECT TOP(1) 1 FROM #ntext WHERE ISJSON(x) = 1
Msg 8116, Level 16, State 1, Line 63
Argument data type ntext is invalid for argument 1 of isjson function.
2. Storage
Теперь посмотрим, насколько выгодно хранение JSON как NVARCHAR/VARCHAR по сравнению с аналогичными данными, представленными в виде XML. Кроме того, попробуем XML хранить в нативном формате, а также представить в виде строки:
DECLARE @XML_Unicode NVARCHAR(MAX) = N'
<Manufacturer Name="Lenovo">
<Product Name="ThinkPad E460">
<Model Name="20ETS03100">
<CPU>i7-6500U</CPU>
<Memory>16</Memory>
<SSD>256</SSD>
</Model>
<Model Name="20ETS02W00">
<CPU>i5-6200U</CPU>
<Memory>8</Memory>
<HDD>1000</HDD>
</Model>
<Model Name="20ETS02V00">
<CPU>i5-6200U</CPU>
<Memory>4</Memory>
<HDD>500</HDD>
</Model>
</Product>
</Manufacturer>'
DECLARE @JSON_Unicode NVARCHAR(MAX) = N'
[
{
"Manufacturer": {
"Name": "Lenovo",
"Product": {
"Name": "ThinkPad E460",
"Model": [
{
"Name": "20ETS03100",
"CPU": "Intel Core i7-6500U",
"Memory": 16,
"SSD": "256"
},
{
"Name": "20ETS02W00",
"CPU": "Intel Core i5-6200U",
"Memory": 8,
"HDD": "1000"
},
{
"Name": "20ETS02V00",
"CPU": "Intel Core i5-6200U",
"Memory": 4,
"HDD": "500"
}
]
}
}
}
]'
DECLARE @XML_Unicode_D NVARCHAR(MAX) = N'<Manufacturer Name="Lenovo"><Product Name="ThinkPad E460"><Model Name="20ETS03100"><CPU>i7-6500U</CPU><Memory>16</Memory><SSD>256</SSD></Model><Model Name="20ETS02W00"><CPU>i5-6200U</CPU><Memory>8</Memory><HDD>1000</HDD></Model><Model Name="20ETS02V00"><CPU>i5-6200U</CPU><Memory>4</Memory><HDD>500</HDD></Model></Product></Manufacturer>'
, @JSON_Unicode_D NVARCHAR(MAX) = N'[{"Manufacturer":{"Name":"Lenovo","Product":{"Name":"ThinkPad E460","Model":[{"Name":"20ETS03100","CPU":"Intel Core i7-6500U","Memory":16,"SSD":"256"},{"Name":"20ETS02W00","CPU":"Intel Core i5-6200U","Memory":8,"HDD":"1000"},{"Name":"20ETS02V00","CPU":"Intel Core i5-6200U","Memory":4,"HDD":"500"}]}}}]'
DECLARE @XML XML = @XML_Unicode
, @XML_ANSI VARCHAR(MAX) = @XML_Unicode
, @XML_D XML = @XML_Unicode_D
, @XML_ANSI_D VARCHAR(MAX) = @XML_Unicode_D
, @JSON_ANSI VARCHAR(MAX) = @JSON_Unicode
, @JSON_ANSI_D VARCHAR(MAX) = @JSON_Unicode_D
SELECT *
FROM (
VALUES ('XML Unicode', DATALENGTH(@XML_Unicode), DATALENGTH(@XML_Unicode_D))
, ('XML ANSI', DATALENGTH(@XML_ANSI), DATALENGTH(@XML_ANSI_D))
, ('XML', DATALENGTH(@XML), DATALENGTH(@XML_D))
, ('JSON Unicode', DATALENGTH(@JSON_Unicode), DATALENGTH(@JSON_Unicode_D))
, ('JSON ANSI', DATALENGTH(@JSON_ANSI), DATALENGTH(@JSON_ANSI_D))
) t(DataType, Delimeters, NoDelimeters)
При выполнении получим следующие результаты:
DataType Delimeters NoDelimeters
------------ ----------- --------------
XML Unicode 914 674
XML ANSI 457 337
XML 398 398
JSON Unicode 1274 604
JSON ANSI 637 302
Может показаться, что самый выгодный вариант — нативный XML. Это отчасти правда, но есть нюансы. XML всегда хранится как Unicode. Кроме того, за счет того, что SQL Server использует бинарный формат хранения этих данных — все сжимается в некий стандартизированный словарь с указателями. Именно поэтому форматирование внутри XML не влияет на конечный размер данных.
Со строками все иначе, поэтому я не стал бы рекомендовать хранить форматированный JSON. Лучший вариант — вырезать все лишние символы при сохранении и форматировать данные по запросу уже на клиенте.
Если хочется еще сильнее сократить размер JSON данных, то в нашем распоряжении несколько возможностей.
3. Compress/Decompress
В SQL Server 2016 реализовали новые функции COMPRESS/DECOMPRESS, которые добавляют поддержку GZIP сжатия:
SELECT *
FROM (
VALUES ('XML Unicode', DATALENGTH(COMPRESS(@XML_Unicode)), DATALENGTH(COMPRESS(@XML_Unicode_D)))
, ('XML ANSI', DATALENGTH(COMPRESS(@XML_ANSI)), DATALENGTH(COMPRESS(@XML_ANSI_D)))
, ('JSON Unicode', DATALENGTH(COMPRESS(@JSON_Unicode)), DATALENGTH(COMPRESS(@JSON_Unicode_D)))
, ('JSON ANSI', DATALENGTH(COMPRESS(@JSON_ANSI)), DATALENGTH(COMPRESS(@JSON_ANSI_D)))
) t(DataType, CompressDelimeters, CompressNoDelimeters)
Результаты для предыдущего примера:
DataType CompressDelimeters CompressNoDelimeters
------------ -------------------- --------------------
XML Unicode 244 223
XML ANSI 198 180
JSON Unicode 272 224
JSON ANSI 221 183
Все хорошо ужимается, но нужно помнить об одной особенности. Предположим, что изначально данные приходили в ANSI, а потом тип переменной поменялся на Unicode:
DECLARE @t TABLE (val VARBINARY(MAX))
INSERT INTO @t
VALUES (COMPRESS('[{"Name":"ThinkPad E460"}]')) -- VARCHAR(8000)
, (COMPRESS(N'[{"Name":"ThinkPad E460"}]')) -- NVARCHAR(4000)
SELECT val
, DECOMPRESS(val)
, CAST(DECOMPRESS(val) AS NVARCHAR(MAX))
, CAST(DECOMPRESS(val) AS VARCHAR(MAX))
FROM @t
Функция COMPRESS возвращает разные бинарные последовательности для ANSI/Unicode и при последующем чтении мы столкнемся с ситуацией, что часть данных сохранено как ANSI, а часть — в Unicode. Крайне тяжело потом угадать, к какому типу делать приведение:
---------------------------- -------------------------------------------------------
筛丢浡≥∺桔湩偫摡䔠㘴∰嵽 [{"Name":"ThinkPad E460"}]
[{"Name":"ThinkPad E460"}] [ { " N a m e " : " T h i n k P a d E 4 6 0 " } ]
Если мы захотим построить нагруженную систему, то использование функции COMPRESS замедлит вставку:
USE tempdb
GO
DROP TABLE IF EXISTS #Compress
DROP TABLE IF EXISTS #NoCompress
GO
CREATE TABLE #NoCompress (DatabaseLogID INT PRIMARY KEY, JSON_Val NVARCHAR(MAX))
CREATE TABLE #Compress (DatabaseLogID INT PRIMARY KEY, JSON_CompressVal VARBINARY(MAX))
GO
SET STATISTICS IO, TIME ON
INSERT INTO #NoCompress
SELECT DatabaseLogID
, JSON_Val = (
SELECT PostTime, DatabaseUser, [Event], [Schema], [Object], [TSQL]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
FROM AdventureWorks2014.dbo.DatabaseLog
OPTION(MAXDOP 1)
INSERT INTO #Compress
SELECT DatabaseLogID
, JSON_CompressVal = COMPRESS((
SELECT PostTime, DatabaseUser, [Event], [Schema], [Object], [TSQL]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
))
FROM AdventureWorks2014.dbo.DatabaseLog
OPTION(MAXDOP 1)
SET STATISTICS IO, TIME OFF
Причем очень существенно:
#NoCompress: CPU time = 15 ms, elapsed time = 25 ms
#Compress: CPU time = 218 ms, elapsed time = 280 ms
При этом размер таблицы сократится:
SELECT obj_name = OBJECT_NAME(p.[object_id])
, a.[type_desc]
, a.total_pages
, total_mb = a.total_pages * 8 / 1024.
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE p.[object_id] IN (OBJECT_ID('#Compress'), OBJECT_ID('#NoCompress'))
obj_name type_desc total_pages total_mb
-------------- ------------- ------------ ---------
#NoCompress IN_ROW_DATA 204 1.593750
#NoCompress LOB_DATA 26 0.203125
#Compress IN_ROW_DATA 92 0.718750
#Compress LOB_DATA 0 0.000000
Кроме того, чтение из таблицы сжатых данных потом сильно замедляет функция DECOMPRESS:
SET STATISTICS IO, TIME ON SELECT * FROM #NoCompress WHERE JSON_VALUE(JSON_Val, '$.Event') = 'CREATE_TABLE' SELECT DatabaseLogID, [JSON] = CAST(DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX)) FROM #Compress WHERE JSON_VALUE(CAST(DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX)), '$.Event') = N'CREATE_TABLE' SET STATISTICS IO, TIME OFF
Логические чтения сократятся, но скорость выполнения останется крайне низкой:
Table '#NoCompress'. Scan count 1, logical reads 187, ...
CPU time = 16 ms, elapsed time = 37 ms
Table '#Compress'. Scan count 1, logical reads 79, ...
CPU time = 109 ms, elapsed time = 212 ms
Как вариант, можно добавить PERSISTED вычисляемый столбец:
ALTER TABLE #Compress ADD EventType_Persisted
AS CAST(JSON_VALUE(CAST(
DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX)), '$.Event')
AS VARCHAR(200)) PERSISTED
Либо создать вычисляемый столбец и на основе него индекс:
ALTER TABLE #Compress ADD EventType_NonPersisted
AS CAST(JSON_VALUE(CAST(
DECOMPRESS(JSON_CompressVal) AS NVARCHAR(MAX)), '$.Event')
AS VARCHAR(200))
CREATE INDEX ix ON #Compress (EventType_NonPersisted)
Иногда задержки по сети намного сильнее влияют на производительность, нежели те примеры, что я привел выше. Представьте, что на клиенте мы можем ужать JSON данные GZIP и отправить их на сервер:
DECLARE @json NVARCHAR(MAX) = (
SELECT t.[name]
, t.[object_id]
, [columns] = (
SELECT c.column_id, c.[name], c.system_type_id
FROM sys.all_columns c
WHERE c.[object_id] = t.[object_id]
FOR JSON AUTO
)
FROM sys.all_objects t
FOR JSON AUTO
)
SELECT InitialSize = DATALENGTH(@json) / 1048576.
, CompressSize = DATALENGTH(COMPRESS(@json)) / 1048576.
Для меня это стало «спасительный кругом», когда пытался сократить сетевой трафик на одном из проектов:
InitialSize CompressSize
-------------- -------------
1.24907684 0.10125923
4. Compression
Чтобы уменьшить размер таблиц, можно также воспользоваться сжатием данных. Ранее сжатие было доступно только в Enterprise редакции. Но с выходом SQL Server 2016 SP1 использовать данную функциональность можно хоть на Express-е:
USE AdventureWorks2014
GO
DROP TABLE IF EXISTS #InitialTable
DROP TABLE IF EXISTS #None
DROP TABLE IF EXISTS #Row
DROP TABLE IF EXISTS #Page
GO
CREATE TABLE #None (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED (ID) WITH (DATA_COMPRESSION = NONE))
CREATE TABLE #Row (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED (ID) WITH (DATA_COMPRESSION = ROW))
CREATE TABLE #Page (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED (ID) WITH (DATA_COMPRESSION = PAGE))
GO
SELECT h.SalesOrderID
, JSON_Data =
(
SELECT p.[Name]
FROM Sales.SalesOrderDetail d
JOIN Production.Product p ON d.ProductID = p.ProductID
WHERE d.SalesOrderID = h.SalesOrderID
FOR JSON AUTO
)
INTO #InitialTable
FROM Sales.SalesOrderHeader h
SET STATISTICS IO, TIME ON
INSERT INTO #None
SELECT *
FROM #InitialTable
OPTION(MAXDOP 1)
INSERT INTO #Row
SELECT *
FROM #InitialTable
OPTION(MAXDOP 1)
INSERT INTO #Page
SELECT *
FROM #InitialTable
OPTION(MAXDOP 1)
SET STATISTICS IO, TIME OFF
#None: CPU time = 62 ms, elapsed time = 68 ms
#Row: CPU time = 94 ms, elapsed time = 89 ms
#Page: CPU time = 125 ms, elapsed time = 126 ms
Сжатие на уровне страниц использует алгоритмы, которые находят похожие куски данных и заменяют их на меньшие по объёму значения. Сжатие на уровне строк урезает типы до минимально необходимых, а также обрезает лишние символы. Например, у нас столбец имеет тип INT, который занимает 4 байта, но хранятся там значения меньше 255. Для таких записей тип усекается, и данные на диске занимают место как будто это TINYINT.
USE tempdb
GO
SELECT obj_name = OBJECT_NAME(p.[object_id])
, a.[type_desc]
, a.total_pages
, total_mb = a.total_pages * 8 / 1024.
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE p.[object_id] IN (OBJECT_ID('#None'), OBJECT_ID('#Page'), OBJECT_ID('#Row'))
obj_name type_desc total_pages total_mb
---------- ------------- ------------ ---------
#None IN_ROW_DATA 1156 9.031250
#Row IN_ROW_DATA 1132 8.843750
#Page IN_ROW_DATA 1004 7.843750
5. ColumnStore
Но что мне нравится больше всего — это ColumnStore индексы, которые от версии к версии в SQL Server становятся все лучше и лучше.
Главная идея ColumnStore — разбивать данные в таблице на RowGroup-ы примерно по 1 миллиону строк и в рамках этой группы сжимать данные по столбцам. За счет этого достигается существенная экономия дискового пространства, сокращение логических чтений и ускорение аналитических запросов. Поэтому если есть необходимость хранения архива с JSON информацией, то можно создать кластерный ColumnStore индекс:
USE AdventureWorks2014
GO
DROP TABLE IF EXISTS #CCI
DROP TABLE IF EXISTS #InitialTable
GO
CREATE TABLE #CCI (ID INT, Val NVARCHAR(MAX), INDEX ix CLUSTERED COLUMNSTORE)
GO
SELECT h.SalesOrderID
, JSON_Data = CAST(
(
SELECT p.[Name]
FROM Sales.SalesOrderDetail d
JOIN Production.Product p ON d.ProductID = p.ProductID
WHERE d.SalesOrderID = h.SalesOrderID
FOR JSON AUTO
)
AS VARCHAR(8000)) -- SQL Server 2012..2016
INTO #InitialTable
FROM Sales.SalesOrderHeader h
SET STATISTICS TIME ON
INSERT INTO #CCI
SELECT *
FROM #InitialTable
SET STATISTICS TIME OFF
Скорость вставки в таблицу при этом будет примерно соответствовать PAGE сжатию. Кроме того, можно более тонко настроить процесс под OLTP нагрузку за счет опции COMPRESSION_DELAY.
#CCI: CPU time = 140 ms, elapsed time = 136 ms
До SQL Server 2017 ColumnStore индексы не поддерживали типы данных [N]VARCHAR(MAX), но вместе с релизом новой версии нам разрешили хранить строки любой длины в ColumnStore.
USE tempdb
GO
SELECT o.[name]
, s.used_page_count / 128.
FROM sys.indexes i
JOIN sys.dm_db_partition_stats s ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE i.[object_id] = OBJECT_ID('#CCI')
Выигрыш от этого иногда бывает очень внушительный:
------ ---------
#CCI 0.796875
6. Create JSON
Теперь рассмотрим, каким образом можно сгенерировать JSON. Если вы уже работали с XML в SQL Server, то здесь все делается по аналогии.
Для формирования JSON проще всего использовать FOR JSON AUTO. В этом случае будет сгенерирован массив JSON из объектов:
DROP TABLE IF EXISTS #Users
GO
CREATE TABLE #Users (
UserID INT
, UserName SYSNAME
, RegDate DATETIME
)
INSERT INTO #Users
VALUES (1, 'Paul Denton', '20170123')
, (2, 'JC Denton', NULL)
, (3, 'Maggie Cho', NULL)
SELECT *
FROM #Users
FOR JSON AUTO
[
{
"UserID":1,
"UserName":"Paul Denton",
"RegDate":"2029-01-23T00:00:00"
},
{
"UserID":2,
"UserName":"JC Denton"
},
{
"UserID":3,
"UserName":"Maggie Cho"
}
]
Важно заметить, что NULL значения игнорируются. Если мы хотим их включать в JSON, то можем воспользоваться опцией INCLUDE_NULL_VALUES:
SELECT UserID, RegDate FROM #Users FOR JSON AUTO, INCLUDE_NULL_VALUES
[
{
"UserID":1,
"RegDate":"2017-01-23T00:00:00"
},
{
"UserID":2,
"RegDate":null
},
{
"UserID":3,
"RegDate":null
}
]
Если нужно избавиться от квадратных скобок, то в этом нам поможет опция WITHOUT_ARRAY_WRAPPER:
SELECT TOP(1) UserID, UserName FROM #Users FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
{
"UserID":1,
"UserName":"Paul Denton"
}
Если же мы хотим объединить результаты с корневым элементом, то для этого предусмотрена опция ROOT:
SELECT UserID, UserName
FROM #Users
FOR JSON AUTO, ROOT('Users')
{
"Users":[
{
"UserID":1,
"UserName":"Paul Denton"
},
{
"UserID":2,
"UserName":"JC Denton"
},
{
"UserID":3,
"UserName":"Maggie Cho"
}
]
}
Если требуется создать JSON с более сложной структурой, присвоить нужные название свойствам, сгруппировать их, то необходимо использовать выражение FOR JSON PATH:
SELECT TOP(1) UserID
, UserName AS [Detail.FullName]
, RegDate AS [Detail.RegDate]
FROM #Users
FOR JSON PATH
[
{
"UserID":1,
"Detail":{
"FullName":"Paul Denton",
"RegDate":"2017-01-23T00:00:00"
}
}
]
SELECT t.[name]
, t.[object_id]
, [columns] = (
SELECT c.column_id, c.[name]
FROM sys.columns c
WHERE c.[object_id] = t.[object_id]
FOR JSON AUTO
)
FROM sys.tables t
FOR JSON AUTO
[
{
"name":"#Users",
"object_id":1483152329,
"columns":[
{
"column_id":1,
"name":"UserID"
},
{
"column_id":2,
"name":"UserName"
},
{
"column_id":3,
"name":"RegDate"
}
]
}
]
7. Check JSON
Для проверки правильности JSON формата существует функция ISJSON, которая возвращает 1, если это JSON, 0 — если нет и NULL, если был передан NULL.
DECLARE @json1 NVARCHAR(MAX) = N'{"id": 1}'
, @json2 NVARCHAR(MAX) = N'[1,2,3]'
, @json3 NVARCHAR(MAX) = N'1'
, @json4 NVARCHAR(MAX) = N''
, @json5 NVARCHAR(MAX) = NULL
SELECT ISJSON(@json1) -- 1
, ISJSON(@json2) -- 1
, ISJSON(@json3) -- 0
, ISJSON(@json4) -- 0
, ISJSON(@json5) -- NULL
8. JsonValue
Чтобы извлечь скалярное значение из JSON, можно воспользоваться функцией JSON_VALUE:
DECLARE @json NVARCHAR(MAX) = N'
{
"UserID": 1,
"UserName": "JC Denton",
"IsActive": true,
"Date": "2016-05-31T00:00:00",
"Settings": [
{
"Language": "EN"
},
{
"Skin": "FlatUI"
}
]
}'
SELECT JSON_VALUE(@json, '$.UserID')
, JSON_VALUE(@json, '$.UserName')
, JSON_VALUE(@json, '$.Settings[0].Language')
, JSON_VALUE(@json, '$.Settings[1].Skin')
, JSON_QUERY(@json, '$.Settings')
9. OpenJson
Для парсинга табличных данных используется табличная функция OPENJSON. Сразу стоит заметить, что она будет работать только на базах с уровнем совместимости 130 и выше.
Существует 2 режима работы функции OPENSON. Самый простой — без указания схемы для результирующей выборки:
DECLARE @json NVARCHAR(MAX) = N'
{
"UserID": 1,
"UserName": "JC Denton",
"IsActive": true,
"RegDate": "2016-05-31T00:00:00"
}'
SELECT * FROM OPENJSON(@json)
Во втором режиме мы можем сами описать, как будет выглядеть возвращаемый результат: названия столбцов, их количество, откуда брать для них значения:
DECLARE @json NVARCHAR(MAX) = N'
[
{
"User ID": 1,
"UserName": "JC Denton",
"IsActive": true,
"Date": "2016-05-31T00:00:00",
"Settings": [
{
"Language": "EN"
},
{
"Skin": "FlatUI"
}
]
},
{
"User ID": 2,
"UserName": "Paul Denton",
"IsActive": false
}
]'
SELECT * FROM OPENJSON(@json)
SELECT * FROM OPENJSON(@json, '$[0]')
SELECT * FROM OPENJSON(@json, '$[0].Settings[0]')
SELECT *
FROM OPENJSON(@json)
WITH (
UserID INT '$."User ID"'
, UserName SYSNAME
, IsActive BIT
, RegDate DATETIME '$.Date'
, Settings NVARCHAR(MAX) AS JSON
, Skin SYSNAME '$.Settings[1].Skin'
)
Если в нашем документе есть вложенная иерархия, то поможет следующий пример:
DECLARE @json NVARCHAR(MAX) = N'
[
{
"FullName": "JC Denton",
"Children": [
{ "FullName": "Mary", "Male": "0" },
{ "FullName": "Paul", "Male": "1" }
]
},
{
"FullName": "Paul Denton"
}
]'
SELECT t.FullName, c.*
FROM OPENJSON(@json)
WITH (
FullName SYSNAME
, Children NVARCHAR(MAX) AS JSON
) t
OUTER APPLY OPENJSON(Children)
WITH (
ChildrenName SYSNAME '$.FullName'
, Male TINYINT
) c
10. Lax & strict
Начиная с SQL Server 2005, появилась возможность валидации XML со стороны базы за счет использования XML SCHEMA COLLECTION. Мы описываем схему для XML, а затем на ее основе можем проверять корректность данных. Такого функционала в явном виде для JSON нет, но есть обходной путь.
Насколько я помню, для JSON существует 2 типа выражений: strict и lax (используется по умолчанию). Отличие заключается в том, что если мы указываем несуществующие или неправильные пути при парсинге, то для lax выражения мы получим NULL, а в случае strict — ошибку:
DECLARE @json NVARCHAR(MAX) = N'
{
"UserID": 1,
"UserName": "JC Denton"
}'
SELECT JSON_VALUE(@json, '$.IsActive')
, JSON_VALUE(@json, 'lax$.IsActive')
, JSON_VALUE(@json, 'strict$.UserName')
SELECT JSON_VALUE(@json, 'strict$.IsActive')
Msg 13608, Level 16, State 2, Line 12
Property cannot be found on the specified JSON path.
11. Modify
Для модификации данных внутри JSON присутствует функция JSON_MODIFY. Примеры достаточно простые, поэтому нет смысла их детально расписывать:
DECLARE @json NVARCHAR(MAX) = N'
{
"FirstName": "JC",
"LastName": "Denton",
"Age": 20,
"Skills": ["SQL Server 2014"]
}'
SET @json = JSON_MODIFY(@json, '$.Age', CAST(JSON_VALUE(@json, '$.Age') AS INT) + 2) -- 20 -> 22
SET @json = JSON_MODIFY(@json, '$.Skills[0]', 'SQL 2016') -- "SQL 2014" -> "SQL 2016"
SET @json = JSON_MODIFY(@json, 'append $.Skills', 'JSON')
SELECT * FROM OPENJSON(@json)
SELECT * FROM OPENJSON(JSON_MODIFY(@json, 'lax$.Age', NULL)) -- delete Age
SELECT * FROM OPENJSON(JSON_MODIFY(@json, 'strict$.Age', NULL)) -- set NULL
GO
DECLARE @json NVARCHAR(100) = N'{ "price": 105.90 }' -- rename
SET @json =
JSON_MODIFY(
JSON_MODIFY(@json, '$.Price',
CAST(JSON_VALUE(@json, '$.price') AS NUMERIC(6,2))),
'$.price', NULL)
SELECT @json
12. Convert implicit
И вот мы начинаем добираться до самого интересного, а именно вопросов, связанных с производительностью.
При парсинге JSON нужно помнить об одном нюансе — OPENJSON и JSON_VALUE возвращают результат в Unicode, если мы это не переопределяем. В базе AdventureWorks столбец AccountNumber имеет тип данных VARCHAR:
USE AdventureWorks2014
GO
DECLARE @json NVARCHAR(MAX) = N'{ "AccountNumber": "AW00000009" }'
SET STATISTICS IO ON
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE AccountNumber = JSON_VALUE(@json, '$.AccountNumber')
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE AccountNumber = CAST(JSON_VALUE(@json, '$.AccountNumber') AS VARCHAR(10))
SET STATISTICS IO OFF
Разница в логических чтениях:
Table 'Customer'. Scan count 1, logical reads 37, ...
Table 'Customer'. Scan count 0, logical reads 2, ...
Из-за того, что типы данных между столбцом и результатом функции у нас не совпадают, SQL Server приходится выполнять неявное преобразование типа, исходя из старшинства. В нашем случае к NVARCHAR. Увы, но все вычисления и преобразования на индексном столбце чаще всего приводят к IndexScan:
Если же указать явно тип, как и у столбца, то мы получим IndexSeek:
13. Indexes
Теперь рассмотрим, как можно индексировать JSON объекты. Как я уже говорил вначале, в SQL Server 2016 не был добавлен отдельный тип данных для JSON, в отличие от XML. Поэтому для его хранения вы можете использовать любые строковые типы данных.
Если кто-то имеет опыт работы с XML, то помнит, что для этого формата в SQL Server существует несколько типов индексов, позволяющих ускорить определенные выборки. Для строковых же типов, в которых предполагается хранение JSON, таких индексов просто не существует.
Увы, но JSONB не завезли. Команда разработки торопилась при релизе JSON функционала и сказала буквально следующее: «Если вам будет не хватать скорости, то мы добавим JSONB в следующей версии». С релизом SQL Server 2017 этого не произошло.
И тут нам на помощь приходят вычисляемые столбцы, которые могут представлять из себя определенные свойства из JSON документов, по которым нужно делать поиск, а индексы создать уже на основе этих столбцов.
USE AdventureWorks2014
GO
DROP TABLE IF EXISTS #JSON
GO
CREATE TABLE #JSON (
DatabaseLogID INT PRIMARY KEY
, InfoJSON NVARCHAR(MAX) NOT NULL
)
GO
INSERT INTO #JSON
SELECT DatabaseLogID
, InfoJSON = (
SELECT PostTime, DatabaseUser, [Event], [Schema], [Object], [TSQL]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
FROM dbo.DatabaseLog
Каждый раз парсить один и те же данные не очень рационально:
SET STATISTICS IO, TIME ON SELECT * FROM #JSON WHERE JSON_VALUE(InfoJSON, '$.Schema') + '.' + JSON_VALUE(InfoJSON, '$.Object') = 'Person.Person' SET STATISTICS IO, TIME OFF
Table '#JSON'. Scan count 1, logical reads 187, ...
CPU time = 16 ms, elapsed time = 29 ms
Поэтому создание вычисляемого столбца и последующее включение его в индекс бывает иногда оправданным:
ALTER TABLE #JSON
ADD ObjectName AS JSON_VALUE(InfoJSON, '$.Schema') + '.' + JSON_VALUE(InfoJSON, '$.Object')
GO
CREATE INDEX IX_ObjectName ON #JSON (ObjectName)
GO
SET STATISTICS IO, TIME ON
SELECT *
FROM #JSON
WHERE JSON_VALUE(InfoJSON, '$.Schema') + '.' + JSON_VALUE(InfoJSON, '$.Object') = 'Person.Person'
SELECT *
FROM #JSON
WHERE ObjectName = 'Person.Person'
SET STATISTICS IO, TIME OFF
При этом оптимизатор SQL Server весьма умный, поэтому менять в коде ничего не потребуется:
Table '#JSON'. Scan count 1, logical reads 13, ...
CPU time = 0 ms, elapsed time = 1 ms
Table '#JSON'. Scan count 1, logical reads 13, ...
CPU time = 0 ms, elapsed time = 1 ms
Кроме того, можно создавать как обычные индексы, так и полнотекстовые, если мы хотим получить поиск по содержимому массивов или целых частей объектов.
При этом полнотекстовый индекс не имеет каких-то специальных правил обработки JSON, он всего лишь разбивает текст на отдельные токены, используя в качестве разделителей двойные кавычки, запятые, скобки — то из чего состоит сама структура JSON:
USE AdventureWorks2014
GO
DROP TABLE IF EXISTS dbo.LogJSON
GO
CREATE TABLE dbo.LogJSON (
DatabaseLogID INT
, InfoJSON NVARCHAR(MAX) NOT NULL
, CONSTRAINT pk PRIMARY KEY (DatabaseLogID)
)
GO
INSERT INTO dbo.LogJSON
SELECT DatabaseLogID
, InfoJSON = (
SELECT PostTime, DatabaseUser, [Event], ObjectName = [Schema] + '.' + [Object]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
FROM dbo.DatabaseLog
GO
IF EXISTS(
SELECT *
FROM sys.fulltext_catalogs
WHERE [name] = 'JSON_FTC'
)
DROP FULLTEXT CATALOG JSON_FTC
GO
CREATE FULLTEXT CATALOG JSON_FTC WITH ACCENT_SENSITIVITY = ON AUTHORIZATION dbo
GO
IF EXISTS (
SELECT *
FROM sys.fulltext_indexes
WHERE [object_id] = OBJECT_ID(N'dbo.LogJSON')
) BEGIN
ALTER FULLTEXT INDEX ON dbo.LogJSON DISABLE
DROP FULLTEXT INDEX ON dbo.LogJSON
END
GO
CREATE FULLTEXT INDEX ON dbo.LogJSON (InfoJSON) KEY INDEX pk ON JSON_FTC
GO
SELECT *
FROM dbo.LogJSON
WHERE CONTAINS(InfoJSON, 'ALTER_TABLE')
14. Parser performance
И наконец мы подошли, пожалуй, к самой интересной части этой статьи. Насколько быстрее парсится JSON по сравнению с XML на SQL Server? Чтобы ответить на этот вопрос, я подготовил серию тестов.
Подготавливаем 2 больших файла в JSON и XML формате:
/*
EXEC sys.sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sys.sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
*/
USE AdventureWorks2014
GO
DROP PROCEDURE IF EXISTS ##get_xml
DROP PROCEDURE IF EXISTS ##get_json
GO
CREATE PROCEDURE ##get_xml
AS
SELECT r.ProductID
, r.[Name]
, r.ProductNumber
, d.OrderQty
, d.UnitPrice
, r.ListPrice
, r.Color
, r.MakeFlag
FROM Sales.SalesOrderDetail d
JOIN Production.Product r ON d.ProductID = r.ProductID
FOR XML PATH ('Product'), ROOT('Products')
GO
CREATE PROCEDURE ##get_json
AS
SELECT (
SELECT r.ProductID
, r.[Name]
, r.ProductNumber
, d.OrderQty
, d.UnitPrice
, r.ListPrice
, r.Color
, r.MakeFlag
FROM Sales.SalesOrderDetail d
JOIN Production.Product r ON d.ProductID = r.ProductID
FOR JSON PATH
)
GO
DECLARE @sql NVARCHAR(4000)
SET @sql = 'bcp "EXEC ##get_xml" queryout "X:\sample.xml" -S ' + @@servername + ' -T -w -r -t'
EXEC sys.xp_cmdshell @sql
SET @sql = 'bcp "EXEC ##get_json" queryout "X:\sample.txt" -S ' + @@servername + ' -T -w -r -t'
EXEC sys.xp_cmdshell @sql
Проверяем производительность OPENJSON, OPENXML и XQuery:
SET NOCOUNT ON
SET STATISTICS TIME ON
DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'X:\sample.xml', SINGLE_BLOB) x
DECLARE @jsonu NVARCHAR(MAX)
SELECT @jsonu = BulkColumn
FROM OPENROWSET(BULK 'X:\sample.txt', SINGLE_NCLOB) x
/*
XML: CPU = 891 ms, Time = 886 ms
NVARCHAR: CPU = 141 ms, Time = 166 ms
*/
SELECT ProductID = t.c.value('(ProductID/text())[1]', 'INT')
, [Name] = t.c.value('(Name/text())[1]', 'NVARCHAR(50)')
, ProductNumber = t.c.value('(ProductNumber/text())[1]', 'NVARCHAR(25)')
, OrderQty = t.c.value('(OrderQty/text())[1]', 'SMALLINT')
, UnitPrice = t.c.value('(UnitPrice/text())[1]', 'MONEY')
, ListPrice = t.c.value('(ListPrice/text())[1]', 'MONEY')
, Color = t.c.value('(Color/text())[1]', 'NVARCHAR(15)')
, MakeFlag = t.c.value('(MakeFlag/text())[1]', 'BIT')
FROM @xml.nodes('Products/Product') t(c)
/*
CPU time = 6203 ms, elapsed time = 6492 ms
*/
DECLARE @doc INT
EXEC sys.sp_xml_preparedocument @doc OUTPUT, @xml
SELECT *
FROM OPENXML(@doc, '/Products/Product', 2)
WITH (
ProductID INT
, [Name] NVARCHAR(50)
, ProductNumber NVARCHAR(25)
, OrderQty SMALLINT
, UnitPrice MONEY
, ListPrice MONEY
, Color NVARCHAR(15)
, MakeFlag BIT
)
EXEC sys.sp_xml_removedocument @doc
/*
CPU time = 2656 ms, elapsed time = 3489 ms
CPU time = 3844 ms, elapsed time = 4482 ms
CPU time = 0 ms, elapsed time = 4 ms
*/
SELECT *
FROM OPENJSON(@jsonu)
WITH (
ProductID INT
, [Name] NVARCHAR(50)
, ProductNumber NVARCHAR(25)
, OrderQty SMALLINT
, UnitPrice MONEY
, ListPrice MONEY
, Color NVARCHAR(15)
, MakeFlag BIT
)
/*
CPU time = 1359 ms, elapsed time = 1642 ms
*/
SET STATISTICS TIME, IO OFF
Теперь проверим производительность скалярной функции JSON_VALUE относительно XQuery:
SET NOCOUNT ON
DECLARE @jsonu NVARCHAR(MAX) = N'[
{"User":"Sergey Syrovatchenko","Age":28,"Skills":["SQL Server","T-SQL","JSON","XML"]},
{"User":"JC Denton","Skills":["Microfibral Muscle","Regeneration","EMP Shield"]},
{"User":"Paul Denton","Age":32,"Skills":["Vision Enhancement"]}]'
DECLARE @jsonu_f NVARCHAR(MAX) = N'[
{
"User":"Sergey Syrovatchenko",
"Age":28,
"Skills":[
"SQL Server",
"T-SQL",
"JSON",
"XML"
]
},
{
"User":"JC Denton",
"Skills":[
"Microfibral Muscle",
"Regeneration",
"EMP Shield"
]
},
{
"User":"Paul Denton",
"Age":32,
"Skills":[
"Vision Enhancement"
]
}
]'
DECLARE @json VARCHAR(MAX) = @jsonu
, @json_f VARCHAR(MAX) = @jsonu_f
DECLARE @xml XML = N'
<Users>
<User Name="Sergey Syrovatchenko">
<Age>28</Age>
<Skills>
<Skill>SQL Server</Skill>
<Skill>T-SQL</Skill>
<Skill>JSON</Skill>
<Skill>XML</Skill>
</Skills>
</User>
<User Name="JC Denton">
<Skills>
<Skill>Microfibral Muscle</Skill>
<Skill>Regeneration</Skill>
<Skill>EMP Shield</Skill>
</Skills>
</User>
<User Name="Paul Denton">
<Age>28</Age>
<Skills>
<Skill>Vision Enhancement</Skill>
</Skills>
</User>
</Users>'
DECLARE @i INT
, @int INT
, @varchar VARCHAR(100)
, @nvarchar NVARCHAR(100)
, @s DATETIME
, @runs INT = 100000
DECLARE @t TABLE (
iter INT IDENTITY PRIMARY KEY
, data_type VARCHAR(100)
, [path] VARCHAR(1000)
, [type] VARCHAR(1000)
, time_ms INT
)
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @int = JSON_VALUE(@jsonu, '$[0].Age')
, @i += 1
INSERT INTO @t
SELECT '@jsonu', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @int = JSON_VALUE(@jsonu_f, '$[0].Age')
, @i += 1
INSERT INTO @t
SELECT '@jsonu_f', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @int = JSON_VALUE(@json, '$[0].Age')
, @i += 1
INSERT INTO @t
SELECT '@json', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @int = JSON_VALUE(@json_f, '$[0].Age')
, @i += 1
INSERT INTO @t
SELECT '@json_f', '$[0].Age', 'INT', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @int = @xml.value('(Users/User[1]/Age/text())[1]', 'INT')
, @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[1]/Age/text())[1]', 'INT', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @nvarchar = JSON_VALUE(@jsonu, '$[1].User')
, @i += 1
INSERT INTO @t
SELECT '@jsonu', '$[1].User', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @nvarchar = JSON_VALUE(@jsonu_f, '$[1].User')
, @i += 1
INSERT INTO @t
SELECT '@jsonu_f', '$[1].User', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @varchar = JSON_VALUE(@json, '$[1].User')
, @i += 1
INSERT INTO @t
SELECT '@json', '$[1].User', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @varchar = JSON_VALUE(@json_f, '$[1].User')
, @i += 1
INSERT INTO @t
SELECT '@json_f', '$[1].User', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @nvarchar = @xml.value('(Users/User[2]/@Name)[1]', 'NVARCHAR(100)')
, @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[2]/@Name)[1]', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @varchar = @xml.value('(Users/User[2]/@Name)[1]', 'VARCHAR(100)')
, @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[2]/@Name)[1]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @nvarchar = JSON_VALUE(@jsonu, '$[2].Skills[0]')
, @i += 1
INSERT INTO @t
SELECT '@jsonu', '$[2].Skills[0]', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @nvarchar = JSON_VALUE(@jsonu_f, '$[2].Skills[0]')
, @i += 1
INSERT INTO @t
SELECT '@jsonu_f', '$[2].Skills[0]', 'NVARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @varchar = JSON_VALUE(@json, '$[2].Skills[0]')
, @i += 1
INSERT INTO @t
SELECT '@json', '$[2].Skills[0]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @varchar = JSON_VALUE(@json_f, '$[2].Skills[0]')
, @i += 1
INSERT INTO @t
SELECT '@json_f', '$[2].Skills[0]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT @i = 1, @s = GETDATE()
WHILE @i <= @runs
SELECT @varchar = @xml.value('(Users/User[3]/Skills/Skill/text())[1]', 'VARCHAR(100)')
, @i += 1
INSERT INTO @t
SELECT '@xml', '(Users/User[3]/Skills/Skill/text())[1]', 'VARCHAR', DATEDIFF(ms, @s, GETDATE())
SELECT * FROM @t
Полученные результаты:
iter data_type path type 2016 SP1 2017 RTM
------ ---------- --------------------------------------- --------- ----------- -----------
1 @jsonu $[0].Age INT 830 273
2 @jsonu_f $[0].Age INT 853 300
3 @json $[0].Age INT 963 374
4 @json_f $[0].Age INT 987 413
5 @xml (Users/User[1]/Age/text())[1] INT 23333 24717
6 @jsonu $[1].User NVARCHAR 1047 450
7 @jsonu_f $[1].User NVARCHAR 1153 567
8 @json $[1].User VARCHAR 1177 570
9 @json_f $[1].User VARCHAR 1303 693
10 @xml (Users/User[2]/@Name)[1] NVARCHAR 18864 20070
11 @xml (Users/User[2]/@Name)[1] VARCHAR 18913 20117
12 @jsonu $[2].Skills[0] NVARCHAR 1347 746
13 @jsonu_f $[2].Skills[0] NVARCHAR 1563 980
14 @json $[2].Skills[0] VARCHAR 1483 860
15 @json_f $[2].Skills[0] VARCHAR 1717 1094
16 @xml (Users/User[3]/Skills/Skill/text())[1] VARCHAR 19510 20767
Краткие выводы
- Извлечение данных из JSON происходит от 2 до 10 раз быстрее, чем из XML.
- Хранение JSON зачастую более избыточное, нежели в XML формате.
- Процессинг JSON данных в Unicode происходит на
5-15% быстрее. - При использовании JSON можно существенно снизить нагрузку на CPU сервера.
- В SQL Server 2017 существенно ускорили парсинг скалярных значений из JSON.
Все тесты проводились:
Windows 8.1 Pro 6.3×64
Core i5 3470 3.2GHz, 32Gb, SSD 850 Evo 250Gb
SQL Server 2016 SP1 Developer (13.0.4001.0)
SQL Server 2017 RTM Developer (14.0.1000.169)
И небольшое послесловие...
Так уж вышло, что я очень надолго забросил написание статей. Смена работы, два проекта 24/7, периодическая фрустрация за чашечкой какао и собственный пет-проект, который скоро отправится на GitHub. И вот пришел к осознанию того, что мне снова хочется поделиться чем-то полезным с комьюнити и увлечь читателя больше, чем на две страницы технической информации.
Знаю, что краткость — не мой конек. Но если вы дочитали до конца, то надеюсь, это было полезным. В любом случае буду рад конструктивным комментариям о вашем жизненном опыте использования JSON на SQL Server 2016/2017. Отдельная благодарность, если вы проверите скорость последних двух примеров. Есть подозрение, что JSON не всегда такой быстрый, и интересно найти репро.
Для тех, кому хочется продолжения:
