Query Tuning - 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

General SQL Server Forums

New to SQL Server Programming

Query Tuning

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-06-18 : 20:19:32

Hi,
For building the below hierachy,it takes 10 minutes for 8383 records.

is it possible to correct/rewrite my query:


declare @intSeqNum int,@intSubSeqNum int,@vcAppName varchar(10),@vcDBName varchar(10)
set @intSeqNum=100040
set @intSubSeqNum=10
set @vcAppName='MRC'
SET @vcDBName='MRC'

 SELECT c.AppName,c.DbName,c.TargetDimName,u.Parent AS Parent,
        u.Child AS Child,u.Alias AS Alias,u.LevelNum AS LevelNo,
        ROW_NUMBER() OVER (ORDER BY u.Sequence) AS Sequence,
        @intSeqNum, @intSubSeqNum 
  FROM ( 
  
  
		SELECT  CASE WHEN ISNULL(h.Parent,'') = '' THEN 'Customer' ELSE ISNULL(c.NodeMemberNamePrefix, '') + h.Parent + ISNULL(c.NodeMemberNameSuffix, '') END AS Parent,
				 ISNULL(c.NodeMemberNamePrefix, '') + h.ChildFrom + ISNULL(c.NodeMemberNameSuffix, '') AS Child,
				 dbo.DescFormat(c.NodeMemberNamePrefix,h.ChildFrom,c.NodeMemberNameSuffix,h.NodeDescription,c.NodeDescSeparator,c.NodeDescStyleCode) AS Alias,
				 h.Sequence   AS Sequence,
				 6-h.LevelNum AS LevelNum
		   FROM dbo.DimHierarchyAllVw h JOIN dbo.cntrl_CubeBuildControl c ON  c.AppName = @vcAppName 
		    AND c.DbName = @vcDBName AND c.SeqNum = @intSeqNum 
		    AND c.SubSeqNum = @intSubSeqNum
		  WHERE h.HierType = 'Customer' AND h.HierName = 'Customer'
			AND h.LevelNum != 99  
	   UNION   
	   SELECT ISNULL(c.NodeMemberNamePrefix, '') + h.Parent + ISNULL(c.NodeMemberNameSuffix, '') AS Parent,
	          ISNULL(c.LeafMemberNamePrefix, '') + d.Customer + ISNULL(c.LeafMemberNameSuffix, '')AS Child,
	          dbo.DescFormat(c.LeafMemberNamePrefix, d.Customer,c.LeafMemberNameSuffix,d.Description,c.LeafDescSeparator,c.LeafDescStyleCode) AS Alias,
	          999999999 + ROW_NUMBER() OVER (ORDER BY d.Customer) AS Sequence,
	          0 AS LevelNum   
	     FROM DimHierarchyAllVw h JOIN DimDetailCustomerVw d 
	       ON (h.ChildFrom <= d.Customer AND h.ChildTo >= d.Customer) OR
	          (h.ChildFrom = d.Customer AND h.ChildTo IS NULL) JOIN dbo.cntrl_CubeBuildControl c 
	       ON (c.AppName = @vcAppName AND c.DbName = @vcDBName 
	          AND c.SeqNum = @intSeqNum AND c.SubSeqNum = @intSubSeqNum)
	    WHERE h.HierType = 'Customer'   AND h.HierName = 'Customer'
	
	    ) u 
 JOIN dbo.cntrl_CubeBuildControl c 
   ON c.AppName = @vcAppName AND c.DbName = @vcDBName AND c.SeqNum = @intSeqNum AND c.SubSeqNum = @intSubSeqNum

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-06-19 : 00:51:01

hi anyupdate

quote:

Originally posted by sent_sara

Hi,
For building the below hierachy,it takes 10 minutes for 8383 records.

is it possible to correct/rewrite my query:


declare @intSeqNum int,@intSubSeqNum int,@vcAppName varchar(10),@vcDBName varchar(10)
set @intSeqNum=100040
set @intSubSeqNum=10
set @vcAppName='MRC'
SET @vcDBName='MRC'

 SELECT c.AppName,c.DbName,c.TargetDimName,u.Parent AS Parent,
        u.Child AS Child,u.Alias AS Alias,u.LevelNum AS LevelNo,
        ROW_NUMBER() OVER (ORDER BY u.Sequence) AS Sequence,
        @intSeqNum, @intSubSeqNum 
  FROM ( 
  
  
		SELECT  CASE WHEN ISNULL(h.Parent,'') = '' THEN 'Customer' ELSE ISNULL(c.NodeMemberNamePrefix, '') + h.Parent + ISNULL(c.NodeMemberNameSuffix, '') END AS Parent,
				 ISNULL(c.NodeMemberNamePrefix, '') + h.ChildFrom + ISNULL(c.NodeMemberNameSuffix, '') AS Child,
				 dbo.DescFormat(c.NodeMemberNamePrefix,h.ChildFrom,c.NodeMemberNameSuffix,h.NodeDescription,c.NodeDescSeparator,c.NodeDescStyleCode) AS Alias,
				 h.Sequence   AS Sequence,
				 6-h.LevelNum AS LevelNum
		   FROM dbo.DimHierarchyAllVw h JOIN dbo.cntrl_CubeBuildControl c ON  c.AppName = @vcAppName 
		    AND c.DbName = @vcDBName AND c.SeqNum = @intSeqNum 
		    AND c.SubSeqNum = @intSubSeqNum
		  WHERE h.HierType = 'Customer' AND h.HierName = 'Customer'
			AND h.LevelNum != 99  
	   UNION   
	   SELECT ISNULL(c.NodeMemberNamePrefix, '') + h.Parent + ISNULL(c.NodeMemberNameSuffix, '') AS Parent,
	          ISNULL(c.LeafMemberNamePrefix, '') + d.Customer + ISNULL(c.LeafMemberNameSuffix, '')AS Child,
	          dbo.DescFormat(c.LeafMemberNamePrefix, d.Customer,c.LeafMemberNameSuffix,d.Description,c.LeafDescSeparator,c.LeafDescStyleCode) AS Alias,
	          999999999 + ROW_NUMBER() OVER (ORDER BY d.Customer) AS Sequence,
	          0 AS LevelNum   
	     FROM DimHierarchyAllVw h JOIN DimDetailCustomerVw d 
	       ON (h.ChildFrom <= d.Customer AND h.ChildTo >= d.Customer) OR
	          (h.ChildFrom = d.Customer AND h.ChildTo IS NULL) JOIN dbo.cntrl_CubeBuildControl c 
	       ON (c.AppName = @vcAppName AND c.DbName = @vcDBName 
	          AND c.SeqNum = @intSeqNum AND c.SubSeqNum = @intSubSeqNum)
	    WHERE h.HierType = 'Customer'   AND h.HierName = 'Customer'
	
	    ) u 
 JOIN dbo.cntrl_CubeBuildControl c 
   ON c.AppName = @vcAppName AND c.DbName = @vcDBName AND c.SeqNum = @intSeqNum AND c.SubSeqNum = @intSubSeqNum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 01:18:13

Very difficult to tell without knowing anything on your requirement

Post somes sample data from tables and explain us what you want with required output. This will make it clearer for anyone trying to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-06-19 : 01:58:14

dimhierarchyall table is metadata table consist of parent,childfrom and childto

dimdetail customer view is customer master table

cntrl_CubeBuildControl is the configuration table for a application;

in the query customer is taken from dimdetailcustomervw and
checks in the dimhiearchyall table (ie) childfrom <= customer and childto >=Customer to form the hierachy as shown in expected output.

dimHieararchyall table:


HierType	HierName	AsOf	LevelNum	Parent	ChildFrom	IsLeaf	NodeDescription	Sequence	ChildTo	CreateDt
Cost Center             	COSTCENTER              	49:20.4	5	181	1812	0	Paramount Parks	3	NULL	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56128	1	NULL	1	56128	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56685	1	NULL	2	56685	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56885	1	NULL	3	56885	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56889	1	NULL	4	56889	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56890	1	NULL	5	56890	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56892	1	NULL	6	56892	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56894	1	NULL	7	56894	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56895	1	NULL	8	56895	49:20.4
Cost Center             	COSTCENTER              	49:20.4	99	1810	56899	1	NULL	9	56899	49:20.4


dimdetailcustomervw

Customer Description
78091015 aa
78091014 bb
78091012 cc
78091008 dd
78091006 er
78091005 df
78090033 ere
78090032 dfff
78090031 ee
78090030 xx

EXPECTED OUTPUT:


AppName	DbName	TargetDimName	Parent	Child	Alias	LevelNo	Sequence	SEQ	SUBSEQ
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141991	EUROPRIS 11734 (CN_141991)	0	2023	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141992	EUROPRIS ttr 11735 (CN_141992)	0	2024	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141993	EUROPRgr V 11739 (CN_141993)	0	2025	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141994	EUROPRIS NORDS 11600 (CN_141994)	0	2026	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141995	EUROPRIS REVETAL 11748 (CN_141995)	0	2027	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141996	ANDE (VESTFOLD) 11737 (CN_141996)	0	2028	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141997	TABENE BRYNE (CN_141997)	0	2029	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141998	INESUNDSPARKEN 11736 (CN_141998)	0	2030	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_141999	OLDA 11746 (CN_141999)	0	2031	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_142000	ERIET (CN_142000)	0	2032	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_142001	MER 7 (CN_142001)	0	2033	100040	10
MRC	MRC	Customer	CN_C_UNASSIGNED	CN_142002	TTEN (CN_142002)	0	2034	100040	10

quote:
Originally posted by visakh16

Very difficult to tell without knowing anything on your requirement

Post somes sample data from tables and explain us what you want with required output. This will make it clearer for anyone trying to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 02:03:17

None of values in posted data for parent,childfrom and childto fields are in range of customer field in dimdetailcustomervw so i didnt understand how you relate between them

When you post data post it between code tags and learn to properly indent them otherwise its difficult to correlate between individual columns and their values
Also post related data from all tables (a subset of 10 rows may be) and show output from them. thats the best way to make it clear for someone trying to help you.
Finally refer below link on an illustration on how to post easy consumable data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources