SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Tuning
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
370 Posts

Posted - 06/18/2013 :  20:19:32  Show Profile  Reply with Quote
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

India
370 Posts

Posted - 06/19/2013 :  00:51:01  Show Profile  Reply with Quote
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
   



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/19/2013 :  01:18:13  Show Profile  Reply with Quote
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
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
370 Posts

Posted - 06/19/2013 :  01:58:14  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/19/2013 :  02:03:17  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000