sqlt-sqldatabase-tuning

Change the Table-Value Function to Inline from Multi-line


I'm trying to change a Table-Valued Function to improve the performance of a Stored Procedure but I can't get the same result. Can you tell me where I'm going wrong?

Original:

create FUNCTION [dbo].[fun_eess1] (
@institucion int ,
@disa int,
@red int,
@mred int) RETURNS @tbl TABLE (idlista [int] IDENTITY, institucion [int], disa [int], red [int], mred [int], establecimiento varchar(10)) AS BEGIN
IF (@institucion=1) -- First Condition */ where @institucion=1 then UNION ALL
BEGIN
    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT DISTINCT [c_institucion], [c_disa], [c_red], [c_microred], [codrenae] FROM [dbo].[RENAE]
    WHERE 

    [c_institucion]=@institucion AND [c_disa]=(CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END)
    AND [c_red]=(CASE @red WHEN -1 THEN [c_red] ELSE @red END)
    AND [c_microred]=(CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END)
END  ELSE
BEGIN
IF (@institucion=15) -- Second Condition */ where @institucion=15 then UNION ALL
BEGIN
    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT DISTINCT [c_institucion], [c_disa], [c_red], [c_microred], [codrenae] FROM [dbo].[RENAE]
    WHERE [c_institucion]='1' AND [c_disa]=(CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END)
    AND [c_red]=(CASE @red WHEN -1 THEN [c_red] ELSE @red END)
    AND [c_microred]=(CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END)

    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT [org].[CodigoOrganizacion], [di].[iddisa], [r].[idred], [mi].[idmred], CAST ([es].[idestablecimiento] AS varchar) FROM [dbo].[Establecimientos] es
    INNER JOIN [dbo].[microred] mi ON [mi].[idmred]=[es].[microred]
    INNER JOIN [dbo].[Red] r ON [mi].[red] = [r].[idred]
    INNER JOIN [dbo].[Disa] di ON [r].[disa] = [di].[iddisa]
    INNER JOIN [dbo].[Organizacion] org ON [di].[institucion] = [org].[CodigoOrganizacion]
    WHERE [org].[CodigoOrganizacion] in ('2','3','4','5','6','13') AND [di].[iddisa]=(case @disa when -1 then [iddisa] else @disa end) AND [r].[idred]=(case @red when -1 then [idred] else @red end)  AND [mi].[idmred]=(case @mred when -1 then [idmred] else @mred end)
END
ELSE
BEGIN  -- Third Condition? */ where @institucion is not (1,15) then UNION ALL
    INSERT INTO @tbl ([institucion], [disa], [red], [mred], [establecimiento])
    SELECT [org].[CodigoOrganizacion], [di].[iddisa], [r].[idred], [mi].[idmred], CAST ([es].[idestablecimiento] AS varchar) FROM [dbo].[Establecimientos] es
    INNER JOIN [dbo].[microred] mi ON [mi].[idmred]=[es].[microred]
    INNER JOIN [dbo].[Red] r ON [mi].[red] = [r].[idred]
    INNER JOIN [dbo].[Disa] di ON [r].[disa] = [di].[iddisa]
    INNER JOIN [dbo].[Organizacion] org ON [di].[institucion] = [org].[CodigoOrganizacion]
    WHERE [org].[CodigoOrganizacion]=@institucion AND [di].[iddisa]=(case @disa when -1 then [iddisa] else @disa end) AND [r].[idred]=(case @red when -1 then [idred] else @red end)  AND [mi].[idmred]=(case @mred when -1 then [idmred] else @mred end) 
END END

Hope someone can help me!


Solution

  • You can change it by creating one big query using union all, and moving the conditions from the if expressions to the where clauses of the inner queries. Also, instead of the Identity column in the return table, wrap the entire union all in another query and select row_number over(order by @@spid) (which returns an arbitrary raw number since it's ordered by a constant):

    CREATE FUNCTION [dbo].[fun_eess1] 
    (
        @institucion int ,
        @disa int,
        @red int,
        @mred int
    )
    
    RETURNS TABLE 
    
    AS BEGIN
    
        SELECT ROW_NUMBER() OVER(ORDER BY @@SPID) As idlista, *
        FROM (
            SELECT DISTINCT 
                    [c_institucion] As [institucion], 
                    [c_disa] As [disa], 
                    [c_red] AS [red], 
                    [c_microred] AS [mred], 
                    [codrenae] AS [establecimiento]
            FROM [dbo].[RENAE]
            WHERE @institucion=1 -- First condition
            AND [c_institucion]=@institucion 
            AND [c_disa] = CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END
            AND [c_red] = CASE @red WHEN -1 THEN [c_red] ELSE @red END
            AND [c_microred] = CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END
    
            UNION ALL
    
            SELECT DISTINCT [c_institucion], [c_disa], [c_red], [c_microred], [codrenae] 
            FROM [dbo].[RENAE]
            WHERE @institucion=15 -- Second condition
            AND [c_institucion]='1' 
            AND [c_disa] = CASE @disa WHEN -1 THEN [c_disa] ELSE @disa END
            AND [c_red] = CASE @red WHEN -1 THEN [c_red] ELSE @red END
            AND [c_microred] = CASE @mred WHEN -1 THEN [c_microred] ELSE @mred END
    
            UNION ALL
    
            SELECT [org].[CodigoOrganizacion], [di].[iddisa], [r].[idred], [mi].[idmred], CAST ([es].[idestablecimiento] AS varchar) 
            FROM [dbo].[Establecimientos] es
            INNER JOIN [dbo].[microred] mi ON [mi].[idmred]=[es].[microred]
            INNER JOIN [dbo].[Red] r ON [mi].[red] = [r].[idred]
            INNER JOIN [dbo].[Disa] di ON [r].[disa] = [di].[iddisa]
            INNER JOIN [dbo].[Organizacion] org ON [di].[institucion] = [org].[CodigoOrganizacion]
            WHERE 
            (
                (
                    @institucion=15 -- Second condition
                    AND [org].[CodigoOrganizacion] in ('2','3','4','5','6','13')
                )
                OR 
                (
                    @institucion NOT IN(1, 15) -- Third condition
                    AND [org].[CodigoOrganizacion] = @institucion
                )
            )
            AND [di].[iddisa] = case @disa when -1 then [iddisa] else @disa end  
            AND [r].[idred] = case @red when -1 then [idred] else @red end 
            AND [mi].[idmred]= case @mred when -1 then [idmred] else @mred end
    
        )
    
    END