Help needed Optimizing Query - 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)

Help needed Optimizing Query

Author

Topic

Kobojunkie
Starting Member

8 Posts

Posted - 2008-08-25 : 17:29:16

I have the query below which works well for what I need it to accomplish. However, I would like to optimize it the best possible. How can I accomplish this? Thanks in advance

SELECT @DHierarchyID = MAP_HierarchyID FROM Mapper WITH(NOLOCK) WHERE MAP_MapperID = @MapperID

SELECT ReferenceHierarchyId, HierarchyID, [Name], NestLevel INTO #TmpHierarchy FROM ufn_PaymentNet_Hierarchy_Select(@DHierarchyID, 0, 1,2)

SELECT
COALESCE((CASE

WHEN Hie.NestLevel = 0 THEN Hie.[Name]
WHEN (Hie.ReferenceHierarchyID <> NULL) THEN

(SELECT DISTINCT A.[Name] FROM #TmpHierarchy A WHERE
A.HierarchyID = Hie.ReferenceHierarchyID AND A.NestLevel = 0 )

ELSE (SELECT DISTINCT A.[Name] FROM #TmpHierarchy A WHERE
A.ReferenceHierarchyID IS NULL AND A.NestLevel = 0 )

END), '00000') as 'HierarchyLevel1'

,

COALESCE((

CASE
WHEN Hie.NestLevel = 1 THEN Hie.[Name]

ELSE (SELECT U.[Name] FROM #TmpHierarchy U WHERE
U.HierarchyID =Hie.ReferenceHierarchyID AND U.NestLevel = 1 )

END), '00000') as 'HierarchyLevel2',

COALESCE((

CASE

WHEN Hie.NestLevel = 2 THEN Hie.[Name]
WHEN Hie.ReferenceHierarchyID <> NULL

THEN (SELECT DISTINCT V.[Name] FROM #TmpHierarchy V WHERE
V.HierarchyID =Hie.ReferenceHierarchyID AND V.NestLevel = 2 )

END) , '00000') as 'HierarchyLevel3'

,COALESCE((CASE
WHEN Hie.NestLevel = 3 THEN Hie.[Name]

WHEN Hie.ReferenceHierarchyID <> NULL
THEN (SELECT W.[Name] FROM #TmpHierarchy W WHERE

W.HierarchyID = Hie.ReferenceHierarchyID AND W.NestLevel = 3 ) END)

, '00000') as 'HierarchyLevel4',

COALESCE((CASE
WHEN Hie.NestLevel = 4 THEN Hie.[Name]

WHEN Hie.ReferenceHierarchyID <> NULL
THEN (SELECT X.[Name] FROM #TmpHierarchy X WHERE

X.HierarchyID =Hie.ReferenceHierarchyID AND X.NestLevel = 4)
END) , '00000') as 'HierarchyLevel5',

COALESCE((CASE

WHEN Hie.NestLevel = 5 THEN Hie.[Name]
WHEN Hie.ReferenceHierarchyID <> NULL

THEN (SELECT Y.[Name] FROM #TmpHierarchy Y WHERE
Y.HierarchyID =Hie.ReferenceHierarchyID AND Y.NestLevel = 5 )

END) , '00000') as 'HierarchyLevel6'

FROM #TmpHierarchy Hie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 00:04:59

Explain your requirement giving some sample data. Thats much easier than reading and understanding the posted code.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-26 : 00:43:41

Don't use Sub-Query, use JOIN instead. Try this . . .

SELECT	COALESCE(CASE 	WHEN Hie.NestLevel 		= 0 		THEN Hie.[Name]
			WHEN Hie.ReferenceHierarchyID 	IS NOT NULL	THEN HN.[Name]
									ELSE NL.[Name]
			END, '00000') AS [HierarchyLevel1],
	COALESCE(CASE	WHEN Hie.NestLevel		= 1		THEN Hie.[Name]
									ELSE HN.[Name]
			END, '00000') AS [HierarchyLevel2]
	COALESCE(CASE 	WHEN Hie.NestLevel 		= 2 		THEN Hie.[Name]
			WHEN Hie.ReferenceHierarchyID 	IS NOT NULL	THEN HN.[Name]
			END, '00000') AS [HierarchyLevel3],
	. . . .
FROM 	#TmpHierarchy Hie
	LEFT JOIN
	(
		SELECT	ReferenceHierarchyID, NestLevel, [Name] = MAX([Name])
		FROM	#TmpHierarchy
		WHERE	ReferenceHierarchyID	IS NOT NULL
		GROUP BY ReferenceHierarchyID, NestLevel
	) HN	ON	Hie.ReferenceHierarchyID	= HN.ReferenceHierarchyID
		AND	Hie.NestLevel			= HN.NestLevel
	LEFT JOIN
	(
		SELECT	NestLevel, [Name] = MAX([Name])
		FROM	#TmpHierarchy
		WHERE	ReferenceHierarchyID	IS NULL
		GROUP BY NestLevel
	) NL	ON	Hie.NestLevel			= NL.NestLevel

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources