Блог

Главная - Вопросы по программированию - Выполняется ли использование T-SQL OPENJSON быстрее, чем возврат необработанного значения nvarchar при запросе больших массивов JSON?

Выполняется ли использование T-SQL OPENJSON быстрее, чем возврат необработанного значения nvarchar при запросе больших массивов JSON?

#sql #performance #tsql #azure-sql-database #azure-sql-server

#sql #Производительность #tsql #azure-sql-database #azure-sql-server

Вопрос:

У меня есть таблица, в которой один из столбцов используется для сохранения большого массива JSON.

Запрос необработанного значения, по-видимому, намного медленнее, чем обработка значения столбца с помощью OPENJSON перед возвратом.

Вопрос:

Является ли OPENJSON фактически более производительным, чем возврат большого значения nvarchar?

Почему в данном случае это быстрее?

Пример схемы:

 CREATE TABLE [ExampleTable] (
    [Id]                [UNIQUEIDENTIFIER]  NOT NULL,
    [Timestamp]         [DATETIME2](7)      NOT NULL,
    [PreviousObjects]   [NVARCHAR](MAX)     NOT NULL
)

PreviousObjects Значение каждой строки представляет собой массив JSON, обычно содержащий около 10 000 элементов.

Id является ли первичный ключ таблицы

Id имеет уникальный кластеризованный индекс

Timestamp имеет неуникальный некластеризованный индекс

Пример запроса 1:

 SELECT TOP 1 [PreviousObjects]
FROM [ExampleTable]
ORDER BY [Timestamp] DESC

Приведенный выше запрос, как и следовало ожидать, был моей первой попыткой получить JSON в моем приложении.

Для массива JSON, содержащего 10k элементов, в моей среде SQL Azure время отклика обычно составляло 10-15 секунд.

В локальной среде с использованием экземпляра mcr.microsoft.com/mssql/server:2017-latest , размещенного в docker, этот запрос может занять до 50 секунд.

Профиль ввода-вывода: 

 Table 'ExampleTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 6972, lob physical reads 0, lob read-ahead reads 10908.

Профиль статистики:

 Rows,Executes,StmtText,StmtId,NodeId,Parent,PhysicalOp,LogicalOp,Argument,DefinedValues,EstimateRows,EstimateIO,EstimateCPU,AvgRowSize,TotalSubtreeCost,OutputList,Warnings,Type,Parallel,EstimateExecutions
1,1,"SELECT TOP 1 [PreviousObjects]
FROM [ExampleTable]
ORDER BY [Timestamp] DESC",1,1,0,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,0.00671277,NULL,NULL,SELECT,0,NULL
1,1,"  |--Top(TOP EXPRESSION:((1)))",1,2,1,Top,Top,TOP EXPRESSION:((1)),NULL,1,0,1E-07,4035,0.00671277,[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],NULL,ExampleTable_ROW,0,1
1,1,"       |--Nested Loops(Inner Join, OUTER REFERENCES:([LocalDatabase].[dbo].[ExampleTable].[Id]))",1,3,2,Nested Loops,Inner Join,OUTER REFERENCES:([LocalDatabase].[dbo].[ExampleTable].[Id]),NULL,1,0,4.18E-05,4043,0.00671267,"[LocalDatabase].[dbo].[ExampleTable].[Timestamp], [LocalDatabase].[dbo].[ExampleTable].[PreviousObjects]",NULL,ExampleTable_ROW,0,1
1,1,"            |--Index Scan(OBJECT:([LocalDatabase].[dbo].[ExampleTable].[IX_ExampleTable_Timestamp]), ORDERED BACKWARD)",1,4,3,Index Scan,Index Scan,"OBJECT:([LocalDatabase].[dbo].[ExampleTable].[IX_ExampleTable_Timestamp]), ORDERED BACKWARD","[LocalDatabase].[dbo].[ExampleTable].[Id], [LocalDatabase].[dbo].[ExampleTable].[Timestamp]",1,0.003125,0.000168,31,0.0032831,"[LocalDatabase].[dbo].[ExampleTable].[Id], [LocalDatabase].[dbo].[ExampleTable].[Timestamp]",NULL,ExampleTable_ROW,0,1
1,1,"            |--Clustered Index Seek(OBJECT:([LocalDatabase].[dbo].[ExampleTable].[PK_ExampleTable]), SEEK:([LocalDatabase].[dbo].[ExampleTable].[Id]=[LocalDatabase].[dbo].[ExampleTable].[Id]) LOOKUP ORDERED FORWARD)",1,6,3,Clustered Index Seek,Clustered Index Seek,"OBJECT:([LocalDatabase].[dbo].[ExampleTable].[PK_ExampleTable]), SEEK:([LocalDatabase].[dbo].[ExampleTable].[Id]=[LocalDatabase].[dbo].[ExampleTable].[Id]) LOOKUP ORDERED FORWARD",[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],1,0.003125,0.0001581,4035,0.0034412,[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],NULL,ExampleTable_ROW,0,2

Пример запроса 2:

 DECLARE @json NVARCHAR(MAX) = (
    SELECT TOP 1 [PreviousObjects]
    FROM [ExampleTable]
    ORDER BY [Timestamp] DESC
)


SELECT *
FROM OPENJSON(@json) 
WITH ( 
    [Id] NVARCHAR(100),
    [ElementTimestamp] DATETIME2,
    [Hash] NVARCHAR(500)
)

Это был второй запрос, который я попробовал, и, вопреки интуиции, я обнаружил, что он возвращает значительно быстрее, чем пример запроса 1.

Для одного и того же набора данных в моей среде SQL Azure время отклика обычно составляло 1-4 секунды.

В локальной среде с использованием экземпляра mcr.microsoft.com/mssql/server:2017-latest , размещенного в docker, этот запрос, как правило, последовательно возвращался менее чем за 2 секунды.

Оба результата, например, запроса 2, были на удивление более производительными, несмотря на то, что содержали один и тот же запрос, сохраненный в памяти. albiet.

Профиль ввода-вывода: 

 Table 'ExampleTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Профиль статистики:

 Rows,Executes,StmtText,StmtId,NodeId,Parent,PhysicalOp,LogicalOp,Argument,DefinedValues,EstimateRows,EstimateIO,EstimateCPU,AvgRowSize,TotalSubtreeCost,OutputList,Warnings,Type,Parallel,EstimateExecutions
1,1,"DECLARE @json NVARCHAR(MAX) = (
    SELECT TOP 1 [PreviousObjects]
    FROM [ExampleTable]
    ORDER BY [Timestamp] DESC
)",1,1,0,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,0.006718207,NULL,NULL,SELECT,0,NULL
0,0,"  |--Compute Scalar(DEFINE:([Expr1003]=[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects]))",1,2,1,Compute Scalar,Compute Scalar,DEFINE:([Expr1003]=[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects]),[Expr1003]=[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],1,0,1E-07,4035,0.006718207,[Expr1003],NULL,ExampleTable_ROW,0,1
1,1,"       |--Nested Loops(Left Outer Join)",1,3,2,Nested Loops,Left Outer Join,NULL,NULL,1,0,4.18E-06,4035,0.006718107,[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],NULL,ExampleTable_ROW,0,1
1,1,"            |--Constant Scan",1,4,3,Constant Scan,Constant Scan,NULL,NULL,1,0,1.157E-06,9,1.157E-06,NULL,NULL,ExampleTable_ROW,0,1
1,1,"            |--Top(TOP EXPRESSION:((1)))",1,5,3,Top,Top,TOP EXPRESSION:((1)),NULL,1,0,1E-07,4035,0.00671277,[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],NULL,ExampleTable_ROW,0,1
1,1,"                 |--Nested Loops(Inner Join, OUTER REFERENCES:([LocalDatabase].[dbo].[ExampleTable].[Id]))",1,6,5,Nested Loops,Inner Join,OUTER REFERENCES:([LocalDatabase].[dbo].[ExampleTable].[Id]),NULL,1,0,4.18E-05,4043,0.00671267,"[LocalDatabase].[dbo].[ExampleTable].[Timestamp], [LocalDatabase].[dbo].[ExampleTable].[PreviousObjects]",NULL,ExampleTable_ROW,0,1
1,1,"                      |--Index Scan(OBJECT:([LocalDatabase].[dbo].[ExampleTable].[IX_ExampleTable_Timestamp]), ORDERED BACKWARD)",1,7,6,Index Scan,Index Scan,"OBJECT:([LocalDatabase].[dbo].[ExampleTable].[IX_ExampleTable_Timestamp]), ORDERED BACKWARD","[LocalDatabase].[dbo].[ExampleTable].[Id], [LocalDatabase].[dbo].[ExampleTable].[Timestamp]",1,0.003125,0.000168,31,0.0032831,"[LocalDatabase].[dbo].[ExampleTable].[Id], [LocalDatabase].[dbo].[ExampleTable].[Timestamp]",NULL,ExampleTable_ROW,0,1
1,1,"                      |--Clustered Index Seek(OBJECT:([LocalDatabase].[dbo].[ExampleTable].[PK_ExampleTable]), SEEK:([LocalDatabase].[dbo].[ExampleTable].[Id]=[LocalDatabase].[dbo].[ExampleTable].[Id]) LOOKUP ORDERED FORWARD)",1,9,6,Clustered Index Seek,Clustered Index Seek,"OBJECT:([LocalDatabase].[dbo].[ExampleTable].[PK_ExampleTable]), SEEK:([LocalDatabase].[dbo].[ExampleTable].[Id]=[LocalDatabase].[dbo].[ExampleTable].[Id]) LOOKUP ORDERED FORWARD",[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],1,0.003125,0.0001581,4035,0.0034412,[LocalDatabase].[dbo].[ExampleTable].[PreviousObjects],NULL,ExampleTable_ROW,0,2

---

Rows,Executes,StmtText,StmtId,NodeId,Parent,PhysicalOp,LogicalOp,Argument,DefinedValues,EstimateRows,EstimateIO,EstimateCPU,AvgRowSize,TotalSubtreeCost,OutputList,Warnings,Type,Parallel,EstimateExecutions
14000,1,"SELECT *
FROM OPENJSON(@json) 
WITH ( 
    [Id] NVARCHAR(100),
    [ElementTimestamp] DATETIME2,
    [Hash] NVARCHAR(500)
)",2,1,0,NULL,NULL,NULL,NULL,50,NULL,NULL,NULL,5.0157E-05,NULL,NULL,SELECT,0,NULL
14000,1,"  |--Table-valued function",2,2,1,Table-valued function,Table-valued function,NULL,NULL,50,0,5.0157E-05,621,5.0157E-05,"OPENJSON_EXPLICIT.[Id], OPENJSON_EXPLICIT.[ElementTimestamp], OPENJSON_EXPLICIT.[Hash]",NULL,PLAN_ROW,0,1

Примечание: В моей локальной настройке у меня фактически было 14 тысяч строк, а не 10 тысяч, как описано.

Оба набора профилей статистики / ввода-вывода были записаны в локальной настройке docker, однако аналогичные результаты наблюдались в среде SQL Azure

Что происходит?

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

1. Индексируется ли столбец Timestamp? Является ли столбец Id первичным ключом?

2. Возможно, ваша среда ограничена пропускной способностью сети (а не скоростью чтения с процессора или диска). В этом случае было бы уместно, чтобы размер второго результирующего набора был меньше размера всего JSON (поскольку он не содержит тегов для каждой строки).

3. Требуется сортировка. Вы можете посмотреть на затраты операторов запроса, используя облегченную функцию профилирования, чтобы увидеть, где все дорого

4. @ConorCunninghamMSFT: оба запроса требуют сортировки.

5. Есть разница между чтением большого двоичного объекта (что требует особого внимания для эффективной работы) и чтением множества строк меньшего размера (чего не происходит). Неэффективность должна быть на стороне клиента, а не на стороне сервера. Для начала попробуйте добавить Packet Size=32767 в строку подключения, чтобы снизить чувствительность TDS к данным, выходящим за границы пакетов. В остальном вам придется углубиться в то, как обрабатываются значения. Наивный клиентский код, который извлекает все это в виде огромной строки, будет медленным. DBCC DROPCLEANBUFFERS позволит вам сэкономить время на холодном кэше.