Подводные камни INSERT EXEC | Олонцев Сергей

Выражение 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.