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