Alternative to Mutli-line Table Valued Functions

by Krishn   Last Updated May 21, 2018 12:06 PM

I currently have a relatively large MTVF returning a set of data, the code I have provided below is a small subsection of what I have in place. All but 1 part of my function is built from a Cte chain with the initial Insert calling another function to return the base set of data. I have found that using a Cte Chain seem to scale rather well however it does struggle when I replace the table variable with a cte calling the function. Execution time goes from 4 seconds to 2+ minutes. I was curious as to what a scaleable alternative method to MTVF would be as I'll be potentially using much larger datasets to call the function.

    DECLARE @FilterFsoID INT = 1121938;
    DECLARE @EffectiveRights TABLE
        (
                 RowNum             INT IDENTITY(1,1)       NOT NULL
                ,AllowRights        INT                     NOT NULL
                ,DenyRights         INT                     NOT NULL
                ,DomainName         VARCHAR(50)             NULL
                ,DomainID           TINYINT                 NULL
                ,NtName             VARCHAR(50)             NULL
                ,DisplayName        VARCHAR(50)             NULL
                ,SidID              INT                     NOT NULL
                ,SidValue           VARCHAR(50)             NOT NULL
                ,AdoTypeID          TINYINT                 NOT NULL
                ,DirectTrustee      TINYINT                 NOT NULL
                ,FilterFsoID        INT                     NOT NULL
                ,DataSource         TINYINT                 NOT NULL
        )
    INSERT INTO @EffectiveRights 
    SELECT
        *
    FROM
        EAVIEW.TVF_SA_FSAA_GetEffectiveRights_SourceData(@FilterFsoID)

    ;WITH   BaseCte AS
        (
            SELECT
                 Sd.RowNum
                ,Sd.DomainID
                ,Sd.SidID
                ,Sd.DomainName
                ,Sd.AllowRights
                ,Sd.DenyRights
                ,Sd.DataSource
                ,Sd.FilterFsoID
    --          ,Sd.TrueFilterFsoID
                ,Sd.DirectTrustee
            FROM
                @EffectiveRights Sd
            JOIN
                (
                    SELECT
                        SidID
                    FROM
                        Domain.tbl_SIDs Sids
                    WHERE
                        SidValue  NOT LIKE 'S-1-5-21-%-501' 
                )Dt
            ON
                Sd.SidID = Dt.SidID
            WHERE
                AdoTypeID = 4
        )
    , SpecialPermissions AS
        (
            SELECT
                 Er.FilterFsoID
                ,Er.AllowRights
                ,Er.DenyRights
                ,Er.DomainID
                ,Er.DomainName
                ,Er.DataSource
                --,Er.SidValue  
            FROM    
                @EffectiveRights Er
            JOIN
                (
                    SELECT
                        Sids.SidID
                    FROM
                        Domain.tbl_SIDs Sids
                    WHERE
                        Sids.SidValue
                    IN 
                        ('S-1-1-0', 'S-1-5-11')
                    OR
                        Sids.SidValue LIKE 'S-1-5-21%-513'
                )Dt
            ON
                Er.SidID = Dt.SidID
            GROUP BY
                 Er.FilterFsoID
                ,Er.AllowRights
                ,Er.DenyRights
                ,Er.DomainID
                ,Er.DomainName
                ,Er.DataSource
        )
    ,   PrepCte AS
        (
            SELECT
                 Sd.RowNum
                ,Sd.DomainID
                ,Sd.SidID
                ,Sd.DomainName
                ,Sd.AllowRights
                ,Sd.DenyRights
                ,Sd.DataSource
                ,Sd.FilterFsoID
                ,Sd.DirectTrustee
            FROM
                BaseCte Sd
            UNION
            SELECT
                 MIN(Sd.RowNum)
                ,Sd.DomainID
                ,Sd.SidID
                ,Sd.DomainName
                ,Sp.AllowRights
                ,Sp.DenyRights
                ,Sp.DataSource
                ,Sd.FilterFsoID
                ,Sd.DirectTrustee
            FROM
                SpecialPermissions Sp
             JOIN
                @EffectiveRights Sd
            ON
                (Sp.DomainID = Sd.DomainID)
            OR
                (Sp.DomainName = 'NT AUTHORITY')
            OR
                Sp.DomainID IS NULL
            JOIN
                Domain.tbl_SIDs Sids
            ON
                Sd.SidID = Sids.SidID
            GROUP BY
                 Sd.DomainID
                ,Sd.SidID
                ,Sd.DomainName
                ,Sp.AllowRights
                ,Sp.DenyRights
                ,Sp.DataSource
                ,Sd.FilterFsoID
                ,Sd.DirectTrustee
        )
        SELECT
            *
        FROM
            PrepCte


Related Questions



Create function for Date Range

Updated February 16, 2018 23:06 PM



Getting Running Total from user defined function

Updated October 17, 2016 09:02 AM