Tag Archives: T-SQL

SQL Server 2016: JSON, часть 6, сравнение производительности с XML

3 Replies

Мы подошли, пожалуй, к самой интересной части серии статей про работу с JSON в SQL Server 2016. Я уже упоминал, что разработчики полюбили JSON за его ясную и понятную глазу структуру, а также за меньший размер данных, чем XML. Осталось проверить самое главное, работает ли SQL Server с JSON быстрее, чем с XML. Для этого я написал небольшой тест на замер скорости работы функции JSON_VALUE и метода value() у типа XML. Для JSON я решил проверить, как работает извлечение данных как из типа varchar(max), так и из nvarchar(max). Я составил похожие по структуре JSON и XML и попробую выбирать из них разные типы данных, числовой и строковый и из разных частей документов. Ну и давайте посмотрим, что же получилось.

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';
 
declare @json_u nvarchar(max) = N'[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]},
	{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]},
	{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}]';
 
declare @xml xml = N'
	32SQL Server 2016T-SQLJSON
	mSQL Server 2014In-Memory OLTP
	25SQL Server 2016In-Memory OLTP
';
 
declare
	@i int,
	@v1 int,
	@v2 varchar(100),
	@start_time datetime,
	@end_time datetime,
	@iterations int = 1000000,
	@path_expression nvarchar(1000),
	@returned_type varchar(100);
 
declare @results table (
	data_type varchar(100) not null,
	test_id tinyint not null,
	path_expression varchar(1000) not null,
	returned_type varchar(1000),
	elapsed_time_ms int not null
);
 
 
set @returned_type = 'int';
set @path_expression = '$[0].age'
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'int';
set @path_expression = '$[0].age'
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = json_value(@json_u, '$[0].age');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2]."first name"';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2]."first name"');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '$[2].skills[0]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = json_value(@json_u, '$[2].skills[0]');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'json u', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'int';
set @path_expression = '(/root/rec/age)[1]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v1 = @xml.value('(/root/rec/age)[1]', 'int');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 1, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '(/root/rec/@first_name)[3]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('(/root/rec/@first_name)[3]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 2, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
set @returned_type = 'varchar';
set @path_expression = '/root[1]/rec[3]/skills[1]/skill[1]';
 
set @i = 1;
set @start_time = getutcdate();
while @i <= @iterations
begin
	select @v2 = @xml.value('/root[1]/rec[3]/skills[1]/skill[1]', 'varchar(100)');
	set @i += 1;
end
set @end_time = getutcdate();
 
insert into @results (data_type, test_id, path_expression, returned_type, elapsed_time_ms)
select 'xml', 3, @path_expression, @returned_type, datediff(ms, @start_time, @end_time);
 
 
select *
from @results;

Результаты, если честно, удивили меня самого. В первую очередь стоит отметить, что выбор данных из JSON, хранящегося в типе nvarchar(max), проиходит быстрее на 5-15%, чем из обычного типа без поддержки Unicode. Хотя, должно было бы быть наоборот, т.к. этот тип занимает в 2 раза больше. Но результаты теста опровергают догадки. Выходит, что выгоднее обрабатывать JSON, который хранится в unicode формате. С чем это связано, мне пока не ясно. Ну и, что особенно радует, извлечение данных из JSON происходит от 2-3 до почти 10 раз быстрее, чем из XML. Поэтому можно смело рекомендовать использовать JSON вместо XML там, где это возможно.

SQL Server 2016: JSON, часть 5, хранение и индексирование

Leave a reply

Продолжаем серию статей про поддержку JSON в SQL Server 2016. В этой части мы рассмотрим, как можно хранить и индексировать JSON объекты в базе данных.

Как я уже раньше упоминал, в SQL Server 2016 не был добавлен отдельный тип для JSON, в отличие от XML. Поэтому для его хранения вы можете использовать любые строковые типы данных. Лучше всего, пожалуй, подойдут varchar(max) и nvarchar(max) в зависимости от того, есть ли у вас Unicode символы в JSON документах или нет. Однако, вы можете использовать типы данных с меньше длиной, если вы точно уверены, что ваши JSON объекты не выйдут по размеру за их пределы.

Если кто-то работал с XML, то помнит, что для этого формата в SQL Server существует несколько типов индексов, позволяющих ускорить определенные выборки. Для строковых же типов, в которых предполагается хранение JSON таких индексов просто не существует. Тут нам на помощь приходят вычисляемые столбцы, которые могут представлять из себя определенные свойства из JSON документов, по которым мы хотим производить поиск, а индексы создать уже на этих столбцах.

use test;
go
 
drop table if exists dbo.test_table;
go
 
create table dbo.test_table (
	id int not null,
	json_data varchar(max) null,
 
	constraint pk_test_table primary key clustered (id)
);
go
 
insert into dbo.test_table (
	id,
	json_data
)
values
	(1, '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"]}'),
	(2, '{"first name":"John","last name":"Smith","sex":"m","skills":["SQL Server 2014","In-Memory OLTP"]}'),
	(3, '{"first name":"Mary","last name":"Brown","age":25,"skills":["SQL Server 2016","In-Memory OLTP"]}');
go
 
alter table dbo.test_table
add v_age as json_value(json_data, '$.age') persisted;
go
 
alter table dbo.test_table
add v_skills as json_query(json_data, '$.skills') persisted;
go
 
 
create nonclustered index ix_nc_test_table_v_age on [dbo].[test_table] (v_age);
go
 
create fulltext catalog [jsonFullTextCatalog] with accent_sensitivity = on authorization [dbo];
go
 
create fulltext index on [dbo].[test_table] (v_skills)
key index pk_test_table ON jsonFullTextCatalog;
go
select *
from [dbo].[test_table] as t
where
	t.[v_age] = 32;
 
select *
from [dbo].[test_table] as t
where
	contains(t.v_skills, 'OLTP');

Вычисляемые столбцы лучше создать как persisted, иначе теряется весь смысл в индексировании этих столбцов. Также, можно создавать как обычные индексы, так и полнотекстовые, если мы хотим получить чуть более гибкий поиск по содержимому массивов или целых частей объектов. При этом полнотекстовые индексы не имеют каких-то специальных правил обработки JSON, они всего лишь разбивают текст на отдельные слова используя в качестве разделителей двойные кавычки, запятые, скобки и т.п.

Вот и все, что я хотел рассказать про хранение и индексирование JSON в SQL Server 2016. В следующей части серии я попробую провести сравнение хранения и обработки JSON и XML в SQL Server и выяснить опытным путем, какой же формат лучше использовать для хранения и передачи неструктурированных данных.

SQL Server 2016: JSON, часть 4, модификация данных

Leave a reply

Продолжаем серию статей про работу с JSON в SQL Server 2016. В предыдущей части я показал, как можно JSON преобразовать в реляционный набор данных. В целом, мы уже рассмотрели все основные действия, которые могут производиться с JSON. В этой части мы рассмотрим, как можно изменять JSON документы встроенными в SQL Server средствами.

Для модификации данных присутствует функция JSON_MODIFY, которой на вход передается JSON документ, пусть до нужного свойства и новое значение.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';
 
set @json = json_modify(@json, 'lax$.age', json_value(@json, '$.age') + 2);
set @json = json_modify(@json, '$.skills[0]', 'SQL Server 2016');
set @json = json_modify(@json, 'append strict$.skills', 'In-Memory OLTP');
 
select * from openjson(@json);

Как мы видим, все достаточно просто. Дополнительно хотелось бы сразу отметить, что можно использовать ключевое слово append, которое вы уже, наверное, заметили выше, если мы хотим добавить новое значение в массив данных.

Как же можно удалить какое свойство в существующем документе? Для этого нужно присвоить ему значение null в lax режиме.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';
 
select * from openjson(json_modify(@json, 'lax$.age', null));
select * from openjson(json_modify(@json, 'strict$.age', null));


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

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":30,"skills":["SQL Server 2014","T-SQL","JSON"]}';
 
select * from openjson(json_modify(@json, 'strict$.hobby', null));

Msg 13608, Level 16, State 2, Line 3
Property cannot be found on the specified JSON path.

Вот и все про модификацию JSON документов. В следующей статье мы рассмотрим, как можно хранить JSON объекты в базе данных и создавать на них индексы.

SQL Server 2016: STRING_SPLIT, описание и сравнение производительности

3 Replies

Свершилось! Наконец-то в SQL Server 2016 появляется встроенная функция для разбивки строк. Пожалуй, это одна из самых распространённых функций, которые, пожалуй, присутствуют на каждом SQL Server, но до текущей версии были реализованы своими силами. Это та функция, для которой существует множество самых различных реализаций, и, зачастую, не самых быстрых. Давайте поближе взглянем на возможности функции STRING_SPLIT, ее ограничения и сравним ее по скорости с самыми распространенными решениями.

Функция принимает всего 2 параметра: собственно, саму строку, которую необходимо разбить и разделитель. Существует одно очень важное и не очень понятное ограничение, разделитель может быть только длиной один символ. Поэтому, если вам требуется разбить строку по большему разделителю, то эта функция для вас не подойдет, и вам нужно искать другое решение.

На текущий момент одним из самых быстрых решений по разбивки строк считается CLR функция, написанная Adam Machanic (ссылка на статью). Также я решил привести пару других примеров функции, реализация в лоб, а также через inline функцию с соединением с таблицей чисел. Последний из этих вариантов на строках небольшой длины обычно показывает неплохие результаты по сравнению с реализацией в лоб. Ну и сравним скорость со стандартной функцией, входящей в SQL Server 2016. Для начала подготовим таблицу с числами и добавим 2 вышеуказанные функции. Как скомпилировать и добавить CLR функцию, я не привожу, т.к. надеюсь, каждый сможет это сделать самостоятельно.

use test;
go
 
if object_id('dbo.num', 'U') is not null
	drop table dbo.num;
go
 
create table dbo.num (
	n int primary key clustered
);
go
 
insert into dbo.num(n)
select top (1000000) row_number() over (order by (select 1)) as rn
from sys.all_columns as t1
cross apply sys.all_columns as t2
go
 
if object_id('dbo.fn_split', N'TF') is not null
	drop function dbo.fn_split;
go
 
create function dbo.fn_split
(
    @str nvarchar(max),
	@del nchar(1)
)
returns @returntable table 
(
	st nvarchar(4000)
)
as
begin
 
	while charindex(@del, @str) > 0
	begin
		insert into @returntable select substring(@str, 1, charindex(@del, @str) - 1)
		set @str = substring(@str, charindex(@del, @str) + 1, len(@str))
	end
	if @str <> ''
		insert into @returntable values(@str)
 
	return
end
go
 
if object_id('dbo.fn_split2', 'if') is not null
	drop function dbo.fn_split2;
go
 
create function dbo.fn_split2 (
	@str nvarchar(max),
	@del nchar(1) = ','
) returns table
as
return
	select
		substring(@str, n, case when charindex(@del, @str, n) = 0 then len(@str) else charindex(@del, @str, n) - n end) as st
	from dbo.num
	where
		n <= len(@str)
		and (n = 1 or substring(@str, n - 1, 1) = @del)
go

И, собственно, сам код для теста, в котором я сначала подготавливаю текст из 20 тысяч слов разделенных запятой, а потом проверяю его разбивку различными методами.

set statistics time on
 
declare @str varchar(max) = (select top (20000) t1.name + ',' as 'text()'
from sys.all_columns as t1
cross apply sys.all_columns as t2
for xml path(''))
 
set @str = substring(@str, 1, len(@str) - 1)
 
select *
from dbo.split_string_clr(@str, ',');
 
select *
from dbo.fn_split(@str, ',') as f;
 
select *
from dbo.fn_split2(@str, ',') as f;
 
select *
from string_split(@str, ',');

Ну и приведу один из полученных результатов.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 113 ms.

SQL Server Execution Times:
CPU time = 13468 ms, elapsed time = 13759 ms.

SQL Server Execution Times:
CPU time = 143875 ms, elapsed time = 143989 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 216 ms.

Как мы видим, CLR функция и STRING_SPLIT оставляют далеко позади остальные решения, а вот выбрать, кто из них быстрее становится проблематично, т.к. их время запуска практически не отличается и варьируется от теста к тесту. В данном случае расходы на CPU одинаковые, а время выполнения медленнее. Однако, в каких-то запусках, время выполнения становится сравнимым, поэтому я не берусь отдать пальму первенства ни одной из них. Это действительно круто, что наконец-то появляется встроенное решение с превосходной производительностью. Но, если вам потребуется использовать разделитель больше чем в один символ, то рекомендую пользоваться CLR решением.

SQL Server 2016: JSON, часть 3, преобразование в реляционные данные

Leave a reply

Это статья является продолжением серии про работу с JSON в SQL Server 2016. В предыдущей части я показал, как можно из реляционных данных сгенерировать JSON. В этой мы рассмотрим обратную операцию, как JSON преобразовать в реляционную структуру. Опять же, если вы уже когда-либо работали с XML в SQL Server, то здесь будет похожий принцип: для этой операции используется функция OPENJSON.

Сразу стоит отметить, что OPENJSON будет работать только в базах данных с уровнем совместимости 130.

Существует 2 режима работы функции OPENSON. Самый простой способ – это без указания схемы для результирующей выборки.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json);

В данном случае результат будет представлен в виде трех столбцов: key, value и type. Соответственно в первом будет выведено название свойства, во втором его значение, а в последнем – тип. Т.к. JSON в приведенном выше примере иерархический, да еще и с массивом для одного из свойств, то функция OPENJSON просто вытащила все свойства с первого уровня и вывела их в виде списка. Мы также можем вытащить отдельно все элементы массива или же свойства с нужного нам уровня документа указав необходимый путь.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select * from openjson(@json, '$.skills');
select * from openjson(@json, 'strict$."additional info"');

Данные в столбце type могут принимать следующие значения.

Значение Тип данных JSON
0 null
1 string
2 int
3 true/false
4 array
5 object

Во втором режиме работы функции OPENJSON вы можете сами описать, как будет выглядеть возвращаемый результат: названия столбцов, их количество, откуда из JSON брать для них значения.

declare @json varchar(max) = '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}}';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

Если же у вас в JSON документе несколько объектов, то для каждого из них будет сгенерирована отдельная строка, а значения столбцов выбраны по соответствующим путям для каждого из этих объектов.

declare @json varchar(max) = '[
	{"first name":"Sergey","last name":"Olontsev","age":32,"skills":["SQL Server 2016","T-SQL","JSON"],"additional info":{"data1":1,"data2":2}},
	{"first name":"John","last name":"Smith","age":18,"skills":["SQL Server 2014","In-Memory OLTP"],"additional info":{"data2":4}}
]';
 
select *
from openjson(@json)
with (
	fisrt_name varchar(100) '$."first name"',
	last_name varchar(100) '$."last name"',
	age tinyint '$.age',
	skill1 varchar(50) '$.skills[0]',
	skill2 varchar(50) '$.skills[1]',
	data1 varchar(50) '$."additional info".data1'
);

Вот и все про то, как можно выбрать данные из JSON объекта и представить их в виде реляционной структуры. В следующей статье я опишу, как можно изменять JSON документы.

SQL Server 2016: JSON, часть 2, формирование данных

Leave a reply

Это статья является продолжением серии про работу с JSON в SQL Server 2016. В предыдущей части я рассказал о том, как можно извлекать данные, а сейчас мы поговорим о том, как можно из реляционных данных сгенерировать JSON. Если вы уже когда-либо работали с XML в SQL Server, то здесь почти то же самое. Для формирования можно выражение FOR JSON.

Самый простой вариант – это использовать FOR JSON AUTO. В этом случае будет сгенерирован массив JSON объектов, где каждая строка в выборке будет отдельным объектом, а колонка свойством.

use tempdb;
go
 
drop table if exists dbo.test_table;
go
 
create table dbo.test_table (
	id int not null,
	name varchar(100) null,
	dt datetime null
);
go
 
insert into dbo.test_table (id, name, dt)
values
	(1, 'qwe', '19000101'),
	(2, 'asd', null),
	(3, null, '20000101');
go
 
select id, name, dt
from dbo.test_table
for json auto;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

Как мы видим, null значения игнорируются. Если мы хотим их включать в JSON, то можем воспользоваться дополнительной опцией INCLUDE_NULL_VALUES.

select id, name, dt
from dbo.test_table
for json auto, include_null_values;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”,”dt”:null},

{“id”:3,”name”:null,”dt”:”2000-01-01T00:00:00″}]

Опция WITHOUT_ARRAY_WRAPPER поможет, если мы хотим вывести JSON без квадратных скобок.

select id, name, dt
from dbo.test_table
for json auto, without_array_wrapper;
go

{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}

Если же мы хотим объединить результаты с корневым элементом, то необходимо воспользоваться опцией ROOT и передать ей нужное название.

select id, name, dt
from dbo.test_table
for json auto, root('root');
go

{“root”:

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

}

Ну и наконец, если мы хотим создать JSON с более сложной структурой, самим присвоить нужные название свойствам, сгруппировать их, то необходимо использовать выражение FOR JSON PATH. Я рекомендую обратиться к первой части цикла статей про JSON, чтобы узнать поподробнее про path выражения для JSON. Ниже я приведу просто пример запроса, который будет генерировать JSON с более сложной структурой.

select
	id,
	name as 'data.full_name',
	dt as 'data.add date'
from dbo.test_table
for json path;
go

[{“id”:1,”data”:{“full_name”:”qwe”,”add date”:”1900-01-01T00:00:00″}},

{“id”:2,”data”:{“full_name”:”asd”}},

{“id”:3,”data”:{“add date”:”2000-01-01T00:00:00″}}]

Ну вот и все про формирование JSON из реляционный данных. В следующей статье мы рассмотрим обратную операцию, как можно преобразовать JSON объект в реляционный набор данных.

SQL Server 2016: JSON, часть 1, извлечение данных

Leave a reply

JSON сейчас является одним из самых широко распространённых форматов обмена данными. Он используется как формат хранения в нескольких NoSQL решениях, в частности, в Microsoft Azure DocumentDB. На мой взгляд, сегодня, он стал даже популярнее XML. Одной из причин, почему так сложилось, является более простая форма и читаемость по сравнению с XML. Поэтому назрела острая необходимость иметь возможность обрабатывать данные в этом формате внутри SQL Server. Поэтому в версии SQL Server 2016 появляется такая возможность.

В первую очередь стоит отметить, что нет отдельного типа для хранения JSON, в отличие от того же XML. Поэтому JSON необходимо хранить в обычных переменных или полях типов varchar или nvarchar.

Для того, чтобы извлечь данных из JSON в SQL Server 2016 добавлены 3 функции: ISJSON, JSON_VALUE, JSON_QUERY.

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

declare
	@json1 varchar(max) = N'{"test": 1}',
	@json2 varchar(max) = N'1',
	@json3 varchar(max) = null;
 
select
	isjson(@json1) as json1,
	isjson(@json2) as json2,
	isjson(@json3) as json3;

Функция возвращает 1, если это JSON, 0 – если нет и null, если в нее был передан null.

Для того, чтобы извлечь конкретное значение, можно воспользоваться функцией JSON_VALUE.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_value(@json, '$.info.specialization') as [specialization],
	json_value(@json, '$.info."course number"') as [course_number],
	json_value(@json, '$.info.address.town') as [town],
	json_value(@json, '$.info.parents[0]') as [mother],
	json_value(@json, '$.info.parents[1]') as [father];

Ну и наконец, извлечь какой-то фрагмент из JSON можно с помощью функции JSON_QUERY.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_query(@json, '$.info.address') as [address],
	json_query(@json, '$.info.parents') as [parents];

И еще хотелось бы сразу рассказать поподробнее про выражения пути в JSON, которые используются в функциях JSON_VALUE и JSON_QUERY. Как мы уже успели убедиться, они достаточно простые.

$ – ссылка на объект JSON в тексте

$.property1 – ссылка на свойство property1

$.array1[0] – ссылка на первый элемент массива array1 (нумерация начинается с нуля, как в JavaScript)

$.property1.property2.property3 – ссылка на свойство property3, которое является вложенными в property2 и property1. Так извлекаются объекты на нескольких уровнях вложенности.

$.”property name 1″ – если имя свойства содержит специальные символы типа пробелов, доллара и т.п., но его имя должно быть заключено в кавычки.

Также существует 2 типа таких выражений: lax и strict. По умолчанию используется lax, ничего дополнительно указывать не надо, но вы можете сделать это принудительно, указав это ключевое слово впереди выражения, например “lax$.property1” или “strict$.property1”. Отличие этих типов заключается в том, что если вы укажете несуществующие или неправильные пути для функций, то в случае lax выражения вы получите NULL, а в случае strict ошибку. Например, если вы укажете выражение, которое возвращает не скалярное значение для функции JSON_VALUE, lax выражение вернет NULL, а при использовании strict вы получите ошибку.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select
	json_value(@json, '$.property.not.exists') as [not exists], -- Свойство не существует
	json_value(@json, 'lax$.property.not.exists') as [not exists], -- Свойство не существует
	json_value(@json, '$.info.address[0]') as [address_0] -- Попытка обратиться к элементу, который не является массивом
;

В случае же использования типа strict мы получим ошибку.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
select json_value(@json, 'strict$.property.not.exists') as [not exists]; -- Свойство не существует

Msg 13608, Level 16, State 5, Line 16
Property cannot be found on the specified JSON path.

Стоит также отметить, что все эти функции могут работать не только с переменными, но со столбцами таблицы.

declare @json varchar(max) =
'{
	"info":{
		"specialization":"computer science",
		"course number":1,
		"address":{
			"town":"Moscow",
			"region":"Moscow",
			"country":"Russia"
		},
		"parents":["Anna", "Peter"]
	},
	"type":"Student"
}';
 
if object_id('tempdb..#test', 'U') is not null
	drop table #test;
 
create table #test (
	json_text varchar(max) null
);
 
insert into #test (json_text) values (@json);
 
select json_value(json_text, 'strict$.info.parents[0]') as [parents]
from #test;

Ну вот и все про извлечение данных из JSON. В следующей статье будет рассмотрено, как можно сформировать JSON из уже имеющихся реляционных данных.

SQL Server 2016: SESSION_CONTEXT

Leave a reply

Немногие знают, но в SQL Server присутствует механизм, когда можно сохранять определенное состояние сессии и передавать сигналы между разными запросами в пределах одной сессии, либо использовать это как дополнительный мониторинг. Ранее я уже писал об этом. Вы можете обратиться к этой статье, чтобы прочитать об этой возможности более подробно. В этой статье я бы хотел рассказать об улучшении этого механизма, особенно, когда нужно было хранить несколько значений, т.к. появляется полноценное хранилище типа key-value.

Для установки значения теперь есть новая хранимая процедура sp_set_session_context. В качестве параметров вы передаете в нее @key – название ключа типа sysname, размером до 128 байт. Также вы передаете параметр @value – значение этого ключа типа sql_variant, размером до 8000 байт. Если вы передадите в параметр @value значение null, то это освободит ранее занимаемую этим значением память. Также имеется опциональный параметр @read_only, который по умолчанию имеет значение 0. Если вы передадите в него 1, то не сможете повторно изменить значение данного параметра. В зависимости от ситуации, это может вам пригодиться.

Ниже я привожу пример установки пары значений для сессии.

exec sp_set_session_context 'rows_per_page', 1000;
exec sp_set_session_context 'user_type', 'simple', @read_only = 1;

Приложение при соединении с базой данных может установить эти настройки, в зависимости от которых будут дальше работать различные хранимые процедуры. В данном случае, в зависимости от типа пользователя возвращать разный набор полей в отчете, а также максимальное количество строк на одну страницу. Это всего лишь один из примеров, как вы можете использовать переменные сессии.

Чтобы в следующем запросе в рамках этой сессии получить значение какого-либо параметра, нужно воспользоваться функцией SESSION_CONTEXT.

select session_context(N'user_type');

А дальше вы уже можете использовать его по своему усмотрению.

Каждый пользователь может устанавливать и изменять настройки в рамках своей сессии, но есть ограничение в 256 кб на одну сессию (согласно документации). Если оно будет превышено, возникнет ошибка. Одна тест показывает, что ошибка возникает, если размер превышает 1 Мб. Так что, скорее всего, размер был расширен, но документация пока не обновлена.

Msg 15665, Level 16, State 1, Procedure sp_set_session_context, Line 1 [Batch Start Line 0]
The value was not set for key ‘125’ because the total size of keys and values in the session context would exceed the 1 MB limit.

Также с помощью системного представления sys.dm_os_memory_objects вы можете посмотреть, сколько всего памяти занято на вашем сервере переменными сессий.

select *
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SESSION_CONTEXT';

Как мы видим, функционал может быть полезен в различных сценариях, а улучшение действительно заметное как в плане удобства, так и в плане объема хранимых данных.

Подводные камни INSERT EXEC

Leave a reply

Выражение INSERT EXEC достаточно удобно во многих отношениях, т.к. позволяет вставить в таблицу данные из динамического запроса или хранимой процедуры. Это довольно привлекательный способ, т.к. позволяет один раз написать логику выборки и предоставить интерфейс в виде хранимой процедуры, который уже будет вызываться в остальных местах: других хранимых процедур, для получения данных с удаленного сервера и т.п. Логика находится только в одном месте и ее достаточно удобно изменить при необходимости. Однако, в этом решении есть свои подводные камни, и я предлагаю посмотреть на них на примере.

Для начала мы создадим пару таблиц: источник и приемник, добавим в источник около 100 тысяч записей, а также процедуру, которая возвращает данные из источника.

use [tempdb];
go
 
if object_id('dbo.test_table_src', 'U') is not null
	drop table dbo.test_table_src;
go
 
if object_id('dbo.test_table_tgt', 'U') is not null
	drop table dbo.test_table_tgt;
go
 
if object_id('dbo.get_test_table_src', 'P') is not null
	drop procedure dbo.get_test_table_src;
go
 
create table dbo.test_table_src (
	[id] int not null identity(1, 1),
	[c1] int not null,
	[c2] varchar(255) not null,
	[c3] datetime not null,
 
	constraint [pk_test_table_src] primary key clustered ([id])
);
go
 
create procedure [dbo].[get_test_table_src]
as
begin
	set nocount on;
 
	select
		[id],
		[c1],
		[c2],
		[c3]
	from [dbo].[test_table_src];
end;
go
 
create table dbo.test_table_tgt (
	[id] int not null,
	[c1] int not null,
	[c2] varchar(255) not null,
	[c3] datetime not null,
 
	constraint [pk_test_table_tgt] primary key clustered ([id])
);
go
 
insert into [dbo].[test_table_src] (
	[c1],
	[c2],
	[c3]
)
select top (100000)
	cast(rand(checksum(newid())) * 1000000 as int),
	cast(newid() as varchar(100)) + '_' + cast(newid() as varchar(100)),
	dateadd(second, cast(rand(checksum(newid())) * 1000000 as int), '19000101')
from sys.all_columns as t1
cross join sys.all_columns as t2
go

Для начала давайте включим статистику времени выполнения и IO операций и просто сделаем вставку в таблицу приемник из источника.

set statistics io on;
set statistics time on;
go
insert into [dbo].[test_table_tgt] (
	[id],
	[c1],
	[c2],
	[c3]
)
select
	[id],
	[c1],
	[c2],
	[c3]
from [dbo].[test_table_src];
go

Ниже я приведу статистику, которая получилась на моей машине.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table ‘test_table_tgt’. Scan count 0, logical reads 257193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘test_table_src’. Scan count 1, logical reads 1272, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 188 ms,  elapsed time = 226 ms.

(100000 row(s) affected)

А теперь посмотрим, за сколько отработает запрос INSERT EXEC в таблицу приемник с помощью хранимой процедуры. Перед запуском я предварительно очищаю таблицу от результатов предыдущего теста.

truncate table [dbo].[test_table_tgt];
alter table [dbo].[test_table_tgt] rebuild;
go
insert into [dbo].[test_table_tgt]
exec [dbo].[get_test_table_src];
go

И статистика для этого запроса.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Table ‘test_table_src’. Scan count 1, logical reads 1271, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 671 ms,  elapsed time = 669 ms.

Table ‘test_table_tgt’. Scan count 0, logical reads 257193, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 1, logical reads 306169, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 921 ms,  elapsed time = 927 ms.

(100000 row(s) affected)

Как мы видим в статистике, появляется Worktable (временная таблица в tempdb), с которой происходит очень большое количество операций. Это получается из-за того, что перед тем, как вставить данные в таблицу приемник, они сначала временно материализуются в tempdb, а уже только потом вставляются. В итоге на моей машине, да и на нескольких других, где я проводил подобный тест, INSERT EXEC работает примерно в 4 раза медленнее, чем обычный INSERT. Да, мы лишаемся удобного интерфейса для получения данных, но если для нас важнее скорость, то лучше всеми способами избегать использовать INSERT EXEC.

Про скорость поиска по строкам и таблицу с настройками

Leave a reply

Довольно широко распространена практика, хранить настройки приложений в базе данных в отдельной таблице примерно следующей структуры.

use [tempdb];
go
 
 
if object_id('dbo.settings', 'U') is not null
	drop table dbo.settings;
go
 
create table dbo.settings (
	[name] varchar(128) not null,
	[value] sql_variant null,
 
	constraint [pk_settings] primary key clustered ([name])
);
go

Настройки могут быть самые разные: какие-то процессы могут постоянно обновлять свой статус, хранить дату последней обработки данных; также это могут быть какие-то параметры для внешних сервисов или ETL процессов. В общем, способов применения очень много, и такой подход встречается очень часто. Но я, конкретно, хотел бы остановиться на одной довольно распространенной ошибке, которая встречается в этом случае, да и в некоторых других. Очень часто названия параметров содержат довольно большой одинаковый префикс, например: Process1_config_param1, Process1_last_active_time, Process2_config_param1, Process2_last_active_time и т.п. Я надеюсь, вы уловили смысл. У нас в базе данных могут быть десятки, сотни или даже тысячи строк, у которых 5-30 первых символов совпадает. И это может повлиять на производительность, т.к. при поиске по названию придется проверять все эти строки. Давайте устроим небольшой тест на производительность и посмотрим, как себя ведет SQL Server.

В первом тесте я вставлю 1000 значений в таблицу, у которых первые 10 символов будут одинаковые, а последние 4 уникальные, и запущу подряд 3 миллиона выборок по одному из значений.

truncate table dbo.settings;
alter table dbo.settings rebuild;
 
insert into dbo.settings (name, value)
select replicate('A', 10) + replicate('0', 4 - ceiling(log(rn + 1, 10))) + cast(rn as varchar(10)), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;
 
declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin
 
	select @test = value
	from dbo.settings
	where name = 'AAAAAAAAAA0100'
 
	set @i += 1
 
end

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

truncate table dbo.settings;
alter table dbo.settings rebuild;
 
insert into dbo.settings (name, value)
select format(rn, '0000') + replicate('A', 10), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;
 
 
 
declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin
 
	select @test = value
	from dbo.settings
	where name = '0100AAAAAAAAAA'
 
	set @i += 1
 
end

Ну и в финальном тесте я вообще убрал повторяющиеся значения, а оставил только короткие уникальные наименования.

truncate table dbo.settings;
alter table dbo.settings rebuild;
 
insert into dbo.settings (name, value)
select format(rn, '0000'), null
from (
	select top (1000) row_number() over (order by (select 1)) as rn
	from sys.all_columns
) as num;
 
 
 
declare @test sql_variant, @i int = 1;
while @i <= 3000000
begin
 
	select @test = value
	from dbo.settings
	where name = '0100'
 
	set @i += 1
 
end

Вы можете запустит все 3 теста у себя и сравнить результаты. Я же хочу поделиться своими. Тесты запускались несколько раз, результаты были достаточно стабильны. Точное время выполнения приводить не имеет смысла, поделюсь лишь сравнительными итогами. Если взять за основу первое решение, то второй вариант относительно него выполняется на 10% быстрее, что очень существенно. Если взять третий тест, то он будет приблизительно на 15% быстрее чем первый и чуть больше, чем на 5% быстрее, чем второй.

Итого, получается, что следует использовать максимально короткие названия параметров и называть их так, чтобы уже с первых символов они были бы уникально идентифицированы. Разница, как мы видим, довольно существенна и поможет значительно ускорить поиск.