Условный UNION ALL в функции таблицы

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

create table dbo.TEST1 (id int primary key, name nvarchar(128))
create table dbo.TEST2 (id int primary key, name nvarchar(128))

Поэтому я создал такую функцию:

create function [dbo].[f_TEST]
(
    @test bit
)
returns table
as
return (
    select id, name from TEST1 where @test = 1

    union all

    select id, name from TEST2 where @test = 0
)

Когда я запускаю его с константой, план выполнения велик - проверяется только одна таблица

select * from dbo.f_TEST(1)

Но тогда, когда я использую переменную, план не так хорош - обе таблицы сканируются

declare @test bit = 1

select * from dbo.f_TEST(@test)

Итак, есть ли какие-либо подсказки (или трюки), чтобы заставить SQL Server понять, что в определенном запросе нужно сканировать только одну таблицу?

Ответ 1

Если ваша функция является встроенным TVP (как в примере), вы можете использовать:

declare @test bit = 1
select * from dbo.f_TEST(@test) OPTION (RECOMPILE);

Затем в обоих случаях вы получите однострочный сканирование индексов.

Демоверсия DBFiddle

Из опции RECOMPILE:

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

Ответ 2

Он отлично работает, как есть. Взгляните на "Количество заклинаний" в соответствующей таблице, так как вы измените значение параметра. Таблицы, которые будут исключены, появятся в плане, потому что их нужно учитывать, но это не значит, что они будут отсканированы.

Кроме того, посмотрите на выражение запуска в фильтре:

Ответ 3

Вы можете попробовать

select top (@test*100) percent id, name from TEST1 

union all

select top (([email protected])*100) percent id, name from TEST2

Ответ 4

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

В этой статье объясняется, почему то, что вы делаете, работает так, как оно есть. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions

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

Ответ 5

OPTION (RECOMPILE) не поможет вам здесь, когда ваша функция используется против таблицы. Это будет запрашивать, например, сканирование обеих таблиц

-- 3rd table to test against
create table dbo.TEST3 (id int primary key, test bit);
insert dbo.TEST3 values(1,1),(2,1),(3,0),(4,1);
GO

select TEST3.* 
from TEST3 
CROSS APPLY dbo.f_TEST(test3.test) 
OPTION (RECOMPILE);

Но все в порядке. У меня короткое время (в противном случае я бы включил скриншот), но если вы запустите эти три запроса с фактическим планом выполнения, вы увидите, что оптимизатор видит в них такую же стоимость:

DECLARE @test int = 1

select * from dbo.f_TEST(1)
select * from dbo.f_TEST(@test)
select * from dbo.f_TEST(@test) OPTION (RECOMPILE)

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

Если вы проведете некоторое тестирование производительности, вы увидите, что в этом случае оптимизатор, вероятно, правильный.

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