Как стать автором
Поиск
Написать публикацию
Обновить

Комментарии 15

Латентное хейтерство не приведет к улучшению качества данного поста. Можно попросить, когда минусуете написать по какой причине. Заранее спасибо.
Всего голосов 4: ↑2 и ↓20
Обычно подобные преждевременные сообщения, вызванные парой минусов-выбросов, тоже не помогают. Что про посты, что про комменты.
Всего голосов 1: ↑1 и ↓0+1
То есть если сравнивать с постгресом, то никакого аналога GIN пока нет? Так, чтобы без всяких колонок кинуть индекс на json(b) поле, и получить индексированный поиск по произвольным структурам?

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

В тоже время, есть некоторые обходные пути как можно ускорить поиск по произвольной JSON структуре. Можно создать кластерный ColumnStore и хранить в нем JSON. При парсинге значений будет использоваться batch режим вместо построчной обработки — это даст выигрыш при парсинге. Опять же тестировал у себя данный пример и не могу сказать, что batch режим кардинально быстрее. Репро на работе нет, но смогу вечером добавить.
Всего голосов 2: ↑2 и ↓0+2
Небольшое репро с использованием кластерного 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
*/

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

Тоже думаем уходить от ORM-a из-за трудностей с динамической схемой. Фактически планируем создавать записи хранящие JSON + некоторое количество полей по которым ведется связывание и выборки (вычисляются на основе самого объекта который храним). NoSQL использовать не можем из-за регуляций. Пока прототип выглядит очень достойно в плане использования (код контроллеров сильно похудел, много boilerplate code вроде PUT, PATCH, валидации и прочего ушло из контроллеров) и производительности (для сильно связанных данных, которые были разнесены по десятку таблиц из-за нормализации, разница в десятки раз, главным образом из-за отсутствия дорогих джойнов).


Кто так пробовал делать, какие подводные камни?


Относительно JSON, очень хотелось бы хранить данные в BSON (с упаковкой). Много не-текста в схеме.

Всего голосов 1: ↑1 и ↓0+1
Спасибо. Как всегда очень интересно, полезно, и без «воды»
Всего голосов 1: ↑1 и ↓0+1
Проверил на одной из сильно нагруженных машин (некоторые примеры). Вот результаты:
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. Пока так и работаем.

Прошу прощение за редактирование, просто времени было мало-кинул с студии как было(
Всего голосов 1: ↑1 и ↓0+1

Отличная статья, большое спасибо за большое количество кода для собственного тестирования (было бы совсем шоколадно, если добавить весь код в виде одного скрипта на Github для удобства тестирования).
Можно больше подробностей про личный проект на Github?

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

Желание есть, буду ждать ссылку на Github. А какие критические ошибки есть у конкурирующих решений? Я так понимаю это https://github.com/olahallengren/sql-server-maintenance-solution и http://www.minionware.net/products/reindex/?

Всего голосов 1: ↑1 и ↓0+1
Нет. За основу брал тулы от RedGate, DevArt и других коллег по цеху.
Всего голосов 1: ↑1 и ↓0+1
Думал о том же. Если есть возможность и желание — посмотрите на проект. Хотелось бы услышать конструктивную критику.

github.com/whitebeast/LazyDBA

P.S. а есть ссылка на ваш репозиторий? :)
Комментарий пока не оценивали0
Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Публикации

Показать лучшие за всё время
Изменить настройки темы

Истории

Ваш аккаунт

Разделы

Информация

Услуги

Техническая поддержка
© 2006–2024, Habr