0

I have a query that executes in 4 seconds. When executing it, I hard code the Id of the item I am basing my query on:

DECLARE @Id UNIQUEIDENTIFIER = 'A130CD89-E3C6-4EB5-8CF5-A9142EAAA3DF'

I then convert the exact same SQL into a scalar function:

CREATE FUNCTION dbo.tmp ( @Id UNIQUEIDENTIFIER )
RETURNS INT
AS
BEGIN

--DECLARE @Id UNIQUEIDENTIFIER = 'A130CD89-E3C6-4EB5-8CF5-A9142EAAA3DF'


    -- Declare variable to work with.
    DECLARE @Layer0 VARCHAR(300), 
        @Layer1 VARCHAR(300), 
        @Layer2 VARCHAR(300), 
        @Layer3 VARCHAR(300), 
        @Layer4 VARCHAR(300), 
        @Layer5 VARCHAR(300), 
        @Layer6 VARCHAR(300), 
        @Layer7 VARCHAR(300), 
        @Layer8 VARCHAR(300), 
        @Layer9 VARCHAR(300),
        @FunctionalLayer0 VARCHAR(300), 
        @FunctionalLayer1 VARCHAR(300), 
        @FunctionalLayer2 VARCHAR(300), 
        @FunctionalLayer3 VARCHAR(300), 
        @FunctionalLayer4 VARCHAR(300), 
        @FunctionalLayer5 VARCHAR(300), 
        @FunctionalLayer6 VARCHAR(300), 
        @FunctionalLayer7 VARCHAR(300), 
        @FunctionalLayer8 VARCHAR(300), 
        @FunctionalLayer9 VARCHAR(300),
        @LayerIndex INT,
        @IsLocationRow BIT,
        @IsFunctionRow BIT,
        @IsPhysical BIT,
        @Result INT 

    -- Populate the variables to make querying easier to work with below.
    SELECT 
        @Layer0 = LocationLayer0, @Layer1 = LocationLayer1, @Layer2 = LocationLayer2, @Layer3 = LocationLayer3, 
        @Layer4 = LocationLayer4, @Layer5 = LocationLayer5, @Layer6 = LocationLayer6, @Layer7 = LocationLayer7, 
        @Layer8 = LocationLayer8, @Layer9 = LocationLayer9, 
        @FunctionalLayer0 = FunctionalLayer0, @FunctionalLayer1 = FunctionalLayer1, @FunctionalLayer2 = FunctionalLayer2, 
        @FunctionalLayer3 = FunctionalLayer3, @FunctionalLayer4 = FunctionalLayer4, @FunctionalLayer5 = FunctionalLayer5, 
        @FunctionalLayer6 = FunctionalLayer6, @FunctionalLayer7 = FunctionalLayer7, @FunctionalLayer8 = FunctionalLayer8, 
        @FunctionalLayer9 = FunctionalLayer9, @LayerIndex = LayerIndex, @IsPhysical = IsPhysical,
        @IsLocationRow = CASE WHEN @Layer0 IS NOT NULL THEN 1 ELSE 0 END,
        @IsFunctionRow = CASE WHEN @FunctionalLayer0 IS NOT NULL THEN 1 ELSE 0 END 
    FROM dbo.Asset
    WHERE Id = @Id

        -- Get the count.
        ;with deletedItems AS 
        (
            SELECT *
            FROM dbo.Asset 
            WHERE IsActive = 0 AND IsPhysical=0
        )
        SELECT @Result = (SELECT SUM(IIF(d.Id IS NULL, 1, 0)) 
        FROM dbo.Asset a
        LEFT JOIN deletedItems d
        ON     (d.LayerIndex = 0 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,''))
            OR (d.LayerIndex = 1 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,''))
            OR (d.LayerIndex = 2 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,''))
            OR (d.LayerIndex = 3 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'')
            OR (d.LayerIndex = 4 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,''))
            OR (d.LayerIndex = 5 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,''))
            OR (d.LayerIndex = 6 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,''))
            OR (d.LayerIndex = 7 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(d.LocationLayer7,''))
            OR (d.LayerIndex = 8 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(d.LocationLayer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(d.LocationLayer8,''))
            OR (d.LayerIndex = 9 AND ISNULL(a.LocationLayer0, '')= ISNULL(d.LocationLayer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(d.LocationLayer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(d.LocationLayer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(d.LocationLayer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(d.LocationLayer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(d.LocationLayer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(d.LocationLayer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(d.LocationLayer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(d.LocationLayer8,'') AND ISNULL(a.LocationLayer9,'') = ISNULL(d.LocationLayer9,''))

        ) 
        WHERE
                a.IsPhysical = 1 
            AND a.IsActive = 1
            AND (@Id IS NULL OR (
                    a.Id != @Id AND
                   (
                       (@LayerIndex = 0 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,''))
                    OR (@LayerIndex = 1 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,''))
                    OR (@LayerIndex = 2 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,''))
                    OR (@LayerIndex = 3 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'')
                    OR (@LayerIndex = 4 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,''))
                    OR (@LayerIndex = 5 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,''))
                    OR (@LayerIndex = 6 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,''))
                    OR (@LayerIndex = 7 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(@Layer7,''))
                    OR (@LayerIndex = 8 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(@Layer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(@Layer8,''))
                    OR (@LayerIndex = 9 AND ISNULL(a.LocationLayer0, '')= ISNULL(@Layer0,'') AND ISNULL(a.LocationLayer1,'') = ISNULL(@Layer1,'') AND ISNULL(a.LocationLayer2,'') = ISNULL(@Layer2,'') AND ISNULL(a.LocationLayer3,'') = ISNULL(@Layer3,'') AND ISNULL(a.LocationLayer4,'') = ISNULL(@Layer4,'') AND ISNULL(a.LocationLayer5,'') = ISNULL(@Layer5,'') AND ISNULL(a.LocationLayer6,'') = ISNULL(@Layer6,'') AND ISNULL(a.LocationLayer7,'') = ISNULL(@Layer7,'') AND ISNULL(a.LocationLayer8,'') = ISNULL(@Layer8,'') AND ISNULL(a.LocationLayer9,'') = ISNULL(@Layer9,''))
                    )
                ))
            )
            )


    RETURN ISNULL(@Result, 0)
end
GO

But when I then:

SELECT DBO.tmp('A130CD89-E3C6-4EB5-8CF5-A9142EAAA3DF')

my result (same value) returns in 12 seconds.

What can cause the same code to triple it's execution time, when just changing it to a scalar function?

Edit: I seem to have fixed it. I suspect a parameter sniffing issue. By adding:

DECLARE @UseMe UNIQUEIDENTIFIER
SET @UseMe = @Id

to the top of the function, and then using @UseMe as the ID in the WHERE clauses... I get the performance I get when running as SQL.

Craig
  • 18,074
  • 38
  • 147
  • 248
  • Can you share the query? I'd start by comparing execution plans. – Max Szczurek Jul 17 '18 at 06:02
  • Thanks Max, I'll see if I can put it here. The execution plan for the query is quite complex, but executes in 4s. Execution plan of the function just has 'SELECT 0% ---- Compute Scalar: 8% ------ Constant Scan: 92%'. – Craig Jul 17 '18 at 06:26
  • @Craig . . . Your title pretty much summarizes common knowledge about UDFs in SQL Server. – Gordon Linoff Jul 17 '18 at 11:37
  • That's what I thought, Girdon, but I note the same issues if I attempt to change it to a table function, or a stored proc. Something strange going on. – Craig Jul 17 '18 at 23:01
  • 1
    Must read: [Dynamic Search Conditions in T‑SQL](http://www.sommarskog.se/dyn-search.html) and [Slow in the Application, Fast in SSMS](http://www.sommarskog.se/query-plan-mysteries.html). Add `OPTION(RECOMPILE)` to the "get the count" query and it will be fast. – Vladimir Baranov Jul 17 '18 at 23:29

1 Answers1

1

If possible try to rewrite your code using inline-UDF:

CREATE FUNCTION dbo.tmp ( @Id UNIQUEIDENTIFIER )
RETURNS TABLE
AS
RETURN (SELECT ISNULL(..., 0));

More info: Why do SQL Server Scalar-valued functions get slower?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • I tried to change the function to a procedure, and the procedure yielded the same duration... Between 11 and 12 seconds. I did the same by trying to change to an inline function - same results. – Craig Jul 17 '18 at 06:27
  • @Craig Please post your real query. I guess it may be problem with parameter sniffing. Second thing: did you clear buffers between tests of both solutions? – Lukasz Szozda Jul 17 '18 at 16:26
  • I've added the full function. It's an ugly one, I know... but I am working with a heavily denormalised table. – Craig Jul 17 '18 at 22:56
  • 1
    @Craig Same as Vladimir: use `OPTION(RECOMPILE)` – Lukasz Szozda Jul 18 '18 at 19:10