Условное объединение Все в табличной функции

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

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 понять, что в определенном запросе должна быть проверена только одна таблица?

5 16
sql sql-server tsql sql-server-2016

5 ответов:

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

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

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

Демо DBFiddle

Из вариант перекомпиляции :

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

13

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

Количество казней: 0

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

Введите описание изображения здесь

3

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

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

union all

select top ((1-@test)*100) percent id, name from TEST2
2

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

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

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

1

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

-- 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)

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

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

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

-2