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.