Performance challenge with UNION ALL

Hi all

I have encountered a challenge while working with a UNION ALL statement. At first I was really happy, I had my tsql query go from 1min+ to just above 1sec. Nice...

But my joy didn't last. I have boiled my issue down to this. If I use a variable in my UNION ALL statement it will take approx 50 sec, if I enter a number it will take approx 1 sec. Below is the two queries, the table used contains parent-child relations and my query wan't to recursively fetch all childs and their children etc from the parent with ID = 3939.

My question is now, since this is to be used in a SP, are there any way I can use a variable value in a UNION ALL without my query will be that much slower?

Best regards

Anders

-- Fast example, approx 1 sec

DECLARE @.tempTable TABLE(relationId int);
WITH usesRelations (relationId, childVersionId) AS
(SELECT Id, childVersionID
FROM dbo.relations relation
WHERE relation.ParentVersionID = 3939
UNION ALL
SELECT p.Id, p.childVersionID
FROM dbo.relations AS p INNER JOIN
usesRelations AS A on A.childversionId = p.ParentVersionId
)
INSERT INTO @.temptable SELECT distinct relationID from usesRelations

-- Slow example, approx 50 sec
DECLARE @.ObjectID int
SET @.ObjectID = 3939
DECLARE @.tempTable TABLE(relationId int);
WITH usesRelations (relationId, childVersionId) AS
(SELECT Id, childVersionID
FROM dbo.relations relation
WHERE relation.ParentVersionID = @.ObjectId
UNION ALL
SELECT p.Id, p.childVersionID
FROM dbo.relations AS p INNER JOIN
usesRelations AS A on A.childversionId = p.ParentVersionId
)
INSERT INTO @.temptable SELECT distinct relationID from usesRelations

That is not a fair comparison. May be SQL Server autoparameterized the first statement and used the constant value as the value for the parameter. In the second batch you are using a variable and SQL Server does not uses variables to estimate cardinality. You can use "OPTION (RECOMPILE)" in the "select" statement, or create a stored procedure with parameters, or use sp_executesql also with parameters (I see you are using table variables so this will not be an option).

Code Snippet

create procedure dbo.p1

@.ObjectID int

as

set nocount on

DECLARE @.tempTable TABLE(relationId int);

WITH usesRelations (relationId, childVersionId)

AS

(

SELECT Id, childVersionID

FROM dbo.relations relation

WHERE relation.ParentVersionID = @.ObjectId

UNION ALL

SELECT p.Id, p.childVersionID

FROM dbo.relations AS p INNER JOIN usesRelations AS A on A.childversionId = p.ParentVersionId

)

INSERT INTO @.temptable SELECT distinct relationID from usesRelations

go

exec dbo.p1 3939

go

AMB

|||Thanks for the reply.

The query was to become a stored procedure anyway. I was only having it as a query while i tried optimizing it - but I see now, that it might be an idea to work with it as a stored procedure all the way.

Im not sure i totally understand why SQL server why it has such a huge impact changing a constant with a variable - but anyways...

Making the statement a stored procedure with a parameter instead makes it perfectly fit and fast.

Thanks for helping me out on this one...

/Anders
|||

> Im not sure i totally understand why SQL server why it has such a huge impact changing a constant with a variable - but anyways...

I shouldn't have said that SQL Server does not use variables to estimate cardinality, instead, I should have said that the estimation is not as accurate as when parameters are present, together with useful indexes or statistics.

SQL server uses the values of the parameters during compilation time (when you invoque the sp and there is not a plan in cache) to estimate cardinality based on the expressions in the "where" and "join" clauses. SQL Server does not know the value of variables during that time, or may be is affraid that those values could change before execution the statement, so it uses other estimations, like the value from "All Density" from the statistics. If there is an index or statistic that is useful to estimate cardinality for those expressions, SQL Server uses the histogram when those values are known, like when you recompile the statement (SS 2005).

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

AMB