Комментарии 15
В тоже время, есть некоторые обходные пути как можно ускорить поиск по произвольной JSON структуре. Можно создать кластерный ColumnStore и хранить в нем JSON. При парсинге значений будет использоваться batch режим вместо построчной обработки — это даст выигрыш при парсинге. Опять же тестировал у себя данный пример и не могу сказать, что batch режим кардинально быстрее. Репро на работе нет, но смогу вечером добавить.
SET NOCOUNT ON
USE AdventureWorks2014 -- SQL Server 2017
GO
DROP TABLE IF EXISTS #CCI
DROP TABLE IF EXISTS #Heap
GO
CREATE TABLE #CCI (JSON_Data NVARCHAR(4000))
GO
SELECT JSON_Data =
(
SELECT h.SalesOrderID
, h.OrderDate
, Product = p.[Name]
, d.OrderQty
, p.ListPrice
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
INTO #Heap
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
JOIN Production.Product p ON d.ProductID = p.ProductID
INSERT INTO #CCI
SELECT * FROM #Heap
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON #CCI
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] IN (OBJECT_ID('#CCI'), OBJECT_ID('#Heap'))
------- -------------
#CCI 10.687500
#Heap 30.859375
Режим batch для колумнстора при последовательном плане более эффективный. Параллельный план дает одинаковую производительностью с поправкой на размер данных:
SET STATISTICS IO, TIME ON
SELECT JSON_VALUE(JSON_Data, '$.OrderDate')
, AVG(CAST(JSON_VALUE(JSON_Data, '$.ListPrice') AS MONEY))
FROM #CCI
GROUP BY JSON_VALUE(JSON_Data, '$.OrderDate')
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SELECT JSON_VALUE(JSON_Data, '$.OrderDate')
, AVG(CAST(JSON_VALUE(JSON_Data, '$.ListPrice') AS MONEY))
FROM #Heap
GROUP BY JSON_VALUE(JSON_Data, '$.OrderDate')
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SET STATISTICS IO, TIME ON
/*
OPTION(MAXDOP 1)
#CCI: CPU = 516 ms, Elapsed = 568 ms
#Heap: CPU = 1015 ms, Elapsed = 1137 ms
OPTION(RECOMPILE, QUERYTRACEON 8649)
#CCI: CPU = 531 ms, Elapsed = 569 ms
#Heap: CPU = 828 ms, Elapsed = 511 ms
*/
При использовании OPENJSON при последовательном плане различий никаких. При параллельном выполнении на моем компе чтение в batch режиме менее эффективное:
SET STATISTICS IO, TIME ON
SELECT OrderDate, AVG(ListPrice)
FROM #CCI
CROSS APPLY OPENJSON(JSON_Data)
WITH (
OrderDate DATE
, ListPrice MONEY
)
GROUP BY OrderDate
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SELECT OrderDate, AVG(ListPrice)
FROM #Heap
CROSS APPLY OPENJSON(JSON_Data)
WITH (
OrderDate DATE
, ListPrice MONEY
)
GROUP BY OrderDate
OPTION(MAXDOP 1)
--OPTION(RECOMPILE, QUERYTRACEON 8649)
SET STATISTICS IO, TIME OFF
/*
OPTION(MAXPOD 1)
#CCI: CPU = 875 ms, Elapsed = 902 ms
#Heap: CPU = 812 ms, Elapsed = 927 ms
OPTION(RECOMPILE, QUERYTRACEON 8649)
#CCI: CPU = 875 ms, Elapsed = 909 ms
#Heap: CPU = 859 ms, Elapsed = 366 ms
*/
или для нагруженных систем более чем актуально?
Тоже думаем уходить от ORM-a из-за трудностей с динамической схемой. Фактически планируем создавать записи хранящие JSON + некоторое количество полей по которым ведется связывание и выборки (вычисляются на основе самого объекта который храним). NoSQL использовать не можем из-за регуляций. Пока прототип выглядит очень достойно в плане использования (код контроллеров сильно похудел, много boilerplate code вроде PUT, PATCH, валидации и прочего ушло из контроллеров) и производительности (для сильно связанных данных, которые были разнесены по десятку таблиц из-за нормализации, разница в десятки раз, главным образом из-за отсутствия дорогих джойнов).
Кто так пробовал делать, какие подводные камни?
Относительно JSON, очень хотелось бы хранить данные в BSON (с упаковкой). Много не-текста в схеме.
1. Datatypes:
varchar: CPU time = 93 ms, elapsed time = 28 ms
nvarchar: CPU time = 94 ms, elapsed time = 92 ms
ntext: CPU time = 469 ms, elapsed time = 1397 ms
2. Storage:
DataType Delimeters NoDelimeters
— XML Unicode 914 674
XML ANSI 457 337
XML 398 398
JSON Unicode 1274 604
JSON ANSI 637 302
3. Compress/Decompress:
DataType CompressDelimeters CompressNoDelimeters
— XML Unicode 244 223
XML ANSI 198 180
JSON Unicode 272 224
JSON ANSI 221 183
10. String Split:
CTE = 5817 ms
XML = 5461 ms
STRING_SPLIT = 5239 ms
OPENJSON = 5304 ms
15. Parser performance (последний скрипт ток на 2016 скуле, т к 2017 пока не используется в продакшене-ток как тест):
CPU time = 1763 ms, elapsed time = 1809 ms
CPU time = 1072 ms, elapsed time = 1079 ms
CPU time = 3028 ms, elapsed time = 3082 ms
Получается, что JSON рулит. Но я бы посоветовал все же всегда проводить тесты именно под требуемые задачи. На данный момент придерживаюсь мнения хранить все в XML, а передавать по сети в JSON. Пока так и работаем.
Прошу прощение за редактирование, просто времени было мало-кинул с студии как было(
Отличная статья, большое спасибо за большое количество кода для собственного тестирования (было бы совсем шоколадно, если добавить весь код в виде одного скрипта на Github для удобства тестирования).
Можно больше подробностей про личный проект на Github?
Желание есть, буду ждать ссылку на Github. А какие критические ошибки есть у конкурирующих решений? Я так понимаю это https://github.com/olahallengren/sql-server-maintenance-solution и http://www.minionware.net/products/reindex/?
github.com/whitebeast/LazyDBA
P.S. а есть ссылка на ваш репозиторий? :)
SQL Server JSON