Когда много лет подряд Microsoft лихорадит из одной крайности в другую, то понемногу начинаешь привыкать к этому и все новое ждешь с неким скепсисом. Со временем это чувство становится только сильнее и подсознательно ничего хорошего уже не ожидаешь.
Но иногда все получается в точности да наоборот. Microsoft вываливает из коробки идеально работающий функционал, который рвет все устоявшиеся жизненные стереотипы. Ты ждешь от новой функционала очередных граблей, но, с каждой минутой, все больше понимаешь, что именно этого тебе не хватало все эти годы.
Такое пафосное вступление имеет определенные на то основания, поскольку долгое время на Microsoft Connect поддержка работы с JSON на SQL Server была одной из самых востребованных фич. Шли годы и неожиданно данный функционал реализовали вместе с релизом SQL Server 2016. Забегая вперед скажу, что вышло очень даже хорошо, но Microsoft не остановилась на этом и в SQL Server 2017 существенно улучшили производительность и без того быстрого JSON парсера.
Содержание:
1. Datatypes
2. Storage
3. Compress/Decompress
4. Compression
5. ColumnStore
6. Create JSON
7. Check JSON
8. JsonValue
9. OpenJson
10. String Split
11. Lax & strict
12. Modify
13. Convert implicit
14. Indexes
15. Parser performance
Видео
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 DATALENGTH(@JSON_ANSI), @JSON_ANSI
UNION ALL
SELECT DATALENGTH(@JSON_Unicode), @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.101259234. 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 OFFNone: 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.8437505. 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.7968756. 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) -- NULL8. 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
) c10. String Split
Вместе с релизом SQL Server 2016 появилась функция STRING_SPLIT. И все вздохнули с облегчением, что теперь не надо придумывать велосипед для разделения строки на токены. Однако, есть еще одна альтернатива — конструкция OPENJSON, который мы рассматривали ранее. Давайте протестируем несколько вариантов сплита строки:
SET NOCOUNT ON
SET STATISTICS TIME OFF
DECLARE @x VARCHAR(MAX) = '1' + REPLICATE(CAST(',1' AS VARCHAR(MAX)), 1000)
SET STATISTICS TIME ON
;WITH cte AS
(
SELECT s = 1
, e = COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1)
, v = SUBSTRING(@x, 1, COALESCE(NULLIF(CHARINDEX(',', @x, 1), 0), LEN(@x) + 1) - 1)
UNION ALL
SELECT s = CONVERT(INT, e) + 1
, e = COALESCE(NULLIF(CHARINDEX(',', @x, e + 1), 0), LEN(@x) + 1)
, v = SUBSTRING(@x, e + 1, COALESCE(NULLIF(CHARINDEX(',', @x, e + 1), 0), LEN(@x) + 1)- e - 1)
FROM cte
WHERE e < LEN(@x) + 1
)
SELECT v
FROM cte
WHERE LEN(v) > 0
OPTION (MAXRECURSION 0)
SELECT t.c.value('(./text())[1]', 'INT')
FROM (
SELECT x = CONVERT(XML, '<i>' + REPLACE(@x, ',', '</i><i>') + '</i>').query('.')
) a
CROSS APPLY x.nodes('i') t(c)
SELECT *
FROM STRING_SPLIT(@x, N',') -- NCHAR(1)/CHAR(1)
SELECT [value]
FROM OPENJSON(N'[' + @x + N']') -- [1,2,3,4]
SET STATISTICS TIME OFFЕсли посмотреть на результаты, то можно заметить что OPENJSON в некоторых случаях может быть быстрее функции STRING_SPLIT не говоря уже о костылях с XML и CTE:
500k 100k 50k 1000
------------- ------- ------ ------ ------
CTE 29407 2406 1266 58
XML 6520 1084 553 259
STRING_SPLIT 4665 594 329 27
OPENJSON 2606 506 273 19При этом если у нас высоконагруженный OLTP, то явной разницы OPENJSON и STRING_SPLIT не наблюдается (1000 итераций + 10 значений через запятую):
CTE = 4629 ms
XML = 4397 ms
STRING_SPLIT = 4011 ms
OPENJSON = 4047 ms11. 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.12. Modify
Для модификации данных внутри JSON присутствует функция JSON_MODIFY. Примеры достаточно простые, поэтому нет смысла их детально расписывать:
DECLARE @json NVARCHAR(MAX) = N'
{
"FirstName": "JC",
"LastName": "Denton",
"Age": 20,
"Skills": ["SQL Server 2014"]
}'
-- 20 -> 22
SET @json = JSON_MODIFY(@json, '$.Age', CAST(JSON_VALUE(@json, '$.Age') AS INT) + 2)
-- "SQL 2014" -> "SQL 2016"
SET @json = JSON_MODIFY(@json, '$.Skills[0]', 'SQL 2016')
SET @json = JSON_MODIFY(@json, 'append $.Skills', 'JSON')
SELECT * FROM OPENJSON(@json)
-- delete Age
SELECT * FROM OPENJSON(JSON_MODIFY(@json, 'lax$.Age', NULL))
-- set NULL
SELECT * FROM OPENJSON(JSON_MODIFY(@json, 'strict$.Age', 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 @json13. 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:
14. 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 OFFTable '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')15. 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_VALUE и OPENJSON. Кроме того старайтесь указывать только те столбцы, которые реально нужны после парсинга.
C JSON все предельно просто — чем меньше столбцов необходимо парсить, тем быстрее мы получим результат:
SET NOCOUNT ON
SET STATISTICS TIME ON
DECLARE @json NVARCHAR(MAX)
SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'X:sample.txt', SINGLE_NCLOB) x
SELECT COUNT_BIG(*)
FROM OPENJSON(@json)
WITH (
ProductID INT
, ProductNumber NVARCHAR(25)
, OrderQty SMALLINT
, UnitPrice MONEY
, ListPrice MONEY
, Color NVARCHAR(15)
)
WHERE Color = 'Black'
SELECT COUNT_BIG(*)
FROM OPENJSON(@json) WITH (Color NVARCHAR(15))
WHERE Color = 'Black'
SELECT COUNT_BIG(*)
FROM OPENJSON(@json)
WHERE JSON_VALUE(value, '$.Color') = 'Black'
/*
2016 SP1:
CPU time = 1140 ms, elapsed time = 1144 ms
CPU time = 781 ms, elapsed time = 789 ms
CPU time = 2157 ms, elapsed time = 2144 ms
2017 RTM:
CPU time = 1016 ms, elapsed time = 1034 ms
CPU time = 718 ms, elapsed time = 736 ms
CPU time = 1282 ms, elapsed time = 1286 ms
*/Краткие выводы
- Извлечение данных из JSON происходит от 2 до 10 раз быстрее, чем из XML.
- Хранение JSON зачастую более избыточное, нежели в XML формате.
- Процессинг JSON данных в Unicode происходит на 5-15% быстрее.
- При использовании JSON можно существенно снизить нагрузку на CPU сервера.
- В SQL Server 2017 существенно ускорили парсинг скалярных значений из JSON.
Железо / софт
Windows 8.1 Pro 6.3 x64
Core i5 3470 3.2GHz, DDR3-1600 32Gb, Samsung 850 Evo 250Gb
SQL Server 2016 SP1 Developer (13.0.4001.0)
SQL Server 2017 RTM Developer (14.0.1000.169)Видео
Читать всю эту информацию весьма утомительно, поэтому для любителей «послушать» есть видео с недавней конфы: SQL Server 2016 / 2017: JSON. Видео отличается от поста отсутствием лишь пары примеров.
И небольшое послесловие...
Так уж вышло, что я очень надолго забросил написание статей. Смена работы, два проекта 24/7, периодическая фрустрация за чашечкой какао и собственный пет-проект, который скоро отправится на GitHub. И вот пришел к осознанию того, что мне снова хочется поделиться чем-то полезным с комьюнити и увлечь читателя больше, чем на две страницы технической информации.
Знаю, что краткость — не мой конек. Но если вы дочитали до конца, то надеюсь, это было полезным. В любом случае буду рад конструктивным комментариям о вашем жизненном опыте использования JSON на SQL Server 2016 / 2017. Отдельная благодарность, если вы проверите скорость последних двух примеров. Есть подозрение, что JSON не всегда такой быстрый, и интересно найти репро.
Автор: AlanDenton
