Comments 15
Латентное хейтерство не приведет к улучшению качества данного поста. Можно попросить, когда минусуете написать по какой причине. Заранее спасибо.
Обычно подобные преждевременные сообщения, вызванные парой минусов-выбросов, тоже не помогают. Что про посты, что про комменты.
То есть если сравнивать с постгресом, то никакого аналога GIN пока нет? Так, чтобы без всяких колонок кинуть индекс на json(b) поле, и получить индексированный поиск по произвольным структурам?

PS Отличная статья, сразу захотелось аналогичной глубины описания на PostgreSQL :)
Пока что в SQL Server 2016 / 2017 не предусмотрена возможность создания индексов по аналогии с GIN. Аргументация у разработчиков примерно такая: «все и так быстро, но если не устраивает скорость, то может в следующей версии добавим». Примерно по такому принципу в SQL Server 2012 SP1 добавили селективные XML индексы.

В тоже время, есть некоторые обходные пути как можно ускорить поиск по произвольной JSON структуре. Можно создать кластерный ColumnStore и хранить в нем JSON. При парсинге значений будет использоваться batch режим вместо построчной обработки — это даст выигрыш при парсинге. Опять же тестировал у себя данный пример и не могу сказать, что batch режим кардинально быстрее. Репро на работе нет, но смогу вечером добавить.
Небольшое репро с использованием кластерного ColumnStore:

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
*/

интересная статья, спасибо, но насколько это востребовано — напрямую брать json из базы?
или для нагруженных систем более чем актуально?
У нас вот REST API, приходит JSON и уходит JSON, все делается в базе, и входной JSON там парсится и выходные данные сразу конвертятся в него, время выполнения уменьшилось в 2-3 раза по сравнению с использование Entity Framework.

Тоже думаем уходить от 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?

По правде, глобальная задумка сделать набор бесплатных тулов для обслуживания SQL Server, которыми сам же и буду пользоваться. Но на эту задачу требуется уж очень много времени, потому и решил начать с чего-то относительно простого — сделать тул для анализа и обслуживания индексов с учетом ошибок конкурирующих решений. А потом на основе этого клепать следующие тулы. Если будет желание поучаствовать в бета-тестировании или просто советом помочь, то буду рад.
Думал о том же. Если есть возможность и желание — посмотрите на проект. Хотелось бы услышать конструктивную критику.

github.com/whitebeast/LazyDBA

P.S. а есть ссылка на ваш репозиторий? :)
Only those users with full accounts are able to leave comments. Log in, please.