Optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optimization

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2009-03-27 : 04:04:22

I am running this query

WITH relations
AS
(
SELECT
T1.id,
T1.parentid
FROM
fullcases T1
WHERE
T1.id = {0}

UNION ALL
SELECT
T1.id,
T1.parentid
FROM
fullcases T1
INNER JOIN relations TH ON TH.id = T1.parentid
)
SELECT * FROM fullcases WHERE id = {0}
UNION
SELECT * FROM fullcases WHERE id in (SELECT DISTINCT id from relations)";

.....but it takes too long to finish.
Any ideas on how to make it more efficient?
Thank you very much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-03-27 : 04:19:00

My experience with using IN (...) or also NOT IN (...) in SQL Server 2005 says: don't use it or only for few data...


WITH relations
AS
(
SELECT 
T1.id,
T1.parentid
FROM
fullcases T1
WHERE
T1.id = {0}

UNION ALL
SELECT 
T1.id,
T1.parentid
FROM
fullcases T1
INNER JOIN relations TH ON TH.id = T1.parentid
)
SELECT * FROM fullcases WHERE id = {0}
UNION
SELECT f.* 
FROM fullcases f
JOIN relations r on r.id = f.id

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 04:42:16

[code];WITH Relations
AS (
SELECT ID,
ParentID
FROM FullCases
WHERE ID = {0}

UNION ALL

SELECT fc.ID,
fc.ParentID
FROM FullCases AS fc
INNER JOIN Relations AS r ON r.ID = fc.ParentID
)

SELECT fc.*
FROM Relations AS r
INNER JOIN FullCases AS fc ON fc.ID = r.ID[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources