Производительность запросов с табличными переменными

Табличные переменные хранятся в оперативной памяти SQL Server, и только большие объемы данных могут вынудить сервер задействовать жесткий диск посредством tempdb.  Известно, что оперативная память это быстро и хорошо. Но не все так просто, иногда можно получить очень медленный запрос в неожиданном месте… В каких случаях? Рассмотрим это более предметно, с использованием примера.

Во-первых, нам понадобится постоянная таблица, содержащая большое количество строк:
set statistics profile off
go
use tempdb 
go
if object_id ('PermanentTable') is not null
   drop table PermanentTable 
go
create table PermanentTable (id int, name nvarchar(1000))
go

--вставка 100,000 строк во временную таблицу

set nocount on
begin tran
   declare @i int
   set @i = 0
   while @i < 100000
   begin
       insert into PermanentTable values (@i, 'something')
       set @i = @i + 1
   end
commit tran
go
update statistics PermanentTable with fullscan 
go

Во-вторых, создадим табличную переменную и заполним ее тестовыми данными:

set nocount on

go 

declare @VariableTable table (id int)

 begin tran
   declare @i int
   set @i = 0
   while @i < 100
   begin
       insert into @VariableTable values (@i)
       set @i = @i + 1
   end
commit tran

Теперь сделаем обычных join между таблицей и табличной переменной:

set statistics profile on
set statistics time on
   select pt.id from @VariableTable vt
                inner join PermanentTable pt on pt.id=vt.id

set statistics profile off
set statistics time off

В итоге этот запрос выполнялся около 3-х с половиной секунд на моей машине:


Посмотрим на план запроса, который даст нам все объяснения:

 

Из плана видно, что Sql Server предположил, что в табличной переменной находится одна запись. Однако в действительности там находится 100 записей, т.е. при осуществлении join сервер в цикле 100 раз пробежал по таблице PermanentTable, что и послужило причиной его выполнения за 3,5 секунды.

Избежать этого можно добавив либо хинты (option (recompile) или option (hash join)) к запросу, либо используя временную таблицу вместо табличной переменной. Ниже показаны план и время выполнения запроса с хином option (recompile).

В любом случае, имея дело с табличными переменными нужно быть начеку.