sql - Condicionante UNION ALL en función de tabla

sql-server tsql

Question

Entonces, el caso de uso es el siguiente: hay algunos parámetros, según los cuales quiero seleccionar datos de una tabla u otra.

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

Así que he creado una función como esta:

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
)

Cuando lo ejecuto con constante, el plan de ejecución es excelente: solo se escanea una tabla

select * from dbo.f_TEST(1)

Pero, entonces, cuando uso la variable, el plan no es tan bueno, se escanean ambas tablas

declare @test bit = 1

select * from dbo.f_TEST(@test)

Entonces, ¿hay sugerencias (o trucos) para obligar a SQL Server a comprender que en una consulta determinada solo se debe escanear una tabla?

Answer 1

Funciona bien como es. Observe "Número de ejecuciones" en la tabla correspondiente, a medida que cambia el valor de su parámetro. Las tablas que se excluirán aparecen en el plan, ya que deben considerarse, pero eso no significa que se escanearán.

También, mira la expresión de inicio en el filtro:

Answer 2

La OPCIÓN (RECOMPILE) no lo ayudará aquí cuando su función se use contra una tabla. Esta consulta, por ejemplo, escaneará ambas tablas.

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

Eso está bien, sin embargo. No tengo mucho tiempo (de lo contrario, incluiría una captura de pantalla), pero si ejecuta estas tres consultas con el plan de ejecución real verá que el optimizador considera que éstas tienen el mismo costo:

DECLARE @test int = 1

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

Aparecerá la segunda consulta que es el doble de cara que la primera y la última, pero cuando se desplaza sobre el operador SELECT, verá que se debe a que el optimizador está estimando dos filas en lugar de 1 (como es el caso de las otras dos). ).

Si realiza algunas pruebas de rendimiento, verá que, en este caso , el optimizador probablemente sea el correcto.

El problema más grande con su código es que se garantiza una exploración de tabla para cada tabla porque no tiene filtros en ninguna de las consultas. Agregar un filtro, si es posible, le permitirá indexar estas dos tablas de una manera en la que se realice una búsqueda en lugar de una exploración.

Answer 3

Puede que desee probar

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

union all

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

Answer 4

Si su función es inline-TVP (como en el ejemplo), entonces podría usar:

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

Luego, en ambos casos, obtendrá una exploración de índice agrupado único.

Demostración de DBFiddle

Desde la opción RECOMPILE :

Al compilar planes de consulta, la sugerencia de consulta RECOMPILE utiliza los valores actuales de cualquier variable local en la consulta y, si la consulta está dentro de un procedimiento almacenado, los valores actuales pasaron a cualquier parámetro.

Answer 5

Utilice un procedimiento almacenado en lugar de la función de tabla, pero tenga cuidado con el rastreo de parámetros. Puede usar SQL dinámico en lugar de un procedimiento almacenado para producir el mismo resultado que busca usando una función de tabla.

Este artículo explicará por qué lo que estás haciendo funciona de la manera que lo hace. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions

Estoy seguro de que desea hacer algo más con la función, por lo que es posible que no desee crear un procedimiento almacenado. Hay una manera de usar los resultados de una ejecución de sporc en una consulta. Pero, esa sería una pregunta diferente a la que has iniciado sesión aquí.