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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Help Eliminating Loop in Query 2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 03/06/2013 :  14:09:14  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
I have a nested Parent/Child table holding raw data that joins into lots of other tables using recursive common table expressions.

After testing I can get better performance if I create a table holding all possible Parent/Child sets in one properly indexed table as opposed to using a dynamic recursive common table expression each time I need it.

I can create the data in the holding table looping through all ID’s from the raw data table however I would like to know how I can eliminate the loop in the following query. I have given sample data and table structure in the second code block.

The query without the loop should create the result set found in Select * From dbo.RawDataCrossJoin from the code....


Use [Test]
Go

Declare @RDID int
Set @RDID = 1

Declare @MaxRDID int
Select @MaxRDID = Max(ID) From dbo.RawData

While @RDID <= @MaxRDID
Begin

	-- This is set to prevent looping rows that don't exists
	-- Real data has all ID in sequence
	If ( @RDID = 2 )
	Begin
		Set @RDID = 4640 -- The Next Row ID
	End

	-- Assemble the Recursive Join
	;With SubAssemblyLevels As
	(
		Select e.ID, 0 As Level From dbo.RawData e Where e.pID = @RDID
		Union All
		Select e.ID, Level + 1 From dbo.RawData e Inner Join SubAssemblyLevels sal On e.pID = sal.ID 
	)

	Insert Into dbo.RawDataCrossJoin (MRDID, RDID)
	Select @RDID MRDID, s.ID RDID
	From SubAssemblyLevels s
	Order By RDID

	Set @RDID = @RDID + 1

End

Select *
From dbo.RawDataCrossJoin



Tables and Sample Data

USE [Test]
GO
/****** Object:  Table [dbo].[RawData]    Script Date: 03/06/2013 13:38:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RawData]') AND type in (N'U'))
DROP TABLE [dbo].[RawData]
GO

USE [Test]
GO
/****** Object:  Table [dbo].[RawData]    Script Date: 03/06/2013 13:38:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RawData](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[PID] [int] NULL,
	[PartNo] [varchar](255) NULL,
	[Description] [varchar](255) NULL,
 CONSTRAINT [PK_RawData] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET NOCOUNT ON
 
SET IDENTITY_INSERT [dbo].[RawData] ON
GO
 
PRINT 'Inserting values into [RawData]'

INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(1,0,'120200-3','b44930f5-6308-4c35-9838-fd08d2e1bb31')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4640,1,'122634-1','f3f9a695-c5e1-4ab3-997f-ef2a6714b681')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4641,4640,'120921-6','9bc78f76-b832-4a6f-8d5e-53f70cb680d9')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4642,4640,'120915-3','c874bd17-76d9-47e0-a41c-7e822bf9cf35')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4643,4642,'120915-9','b0ae38a9-f4cd-43e5-b470-ab38679d0bb7')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4644,4642,'122608-3','177acab1-517a-4c61-8c51-e49691b2a411')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4645,4642,'122610-1','bb0c6c2b-8b64-4646-9b36-e86bc3d45c9c')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4646,4640,'122634-4','fac4c991-0d03-4045-ae9a-d00d4d75d7bc')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4647,4640,'122634-5','6e715cbc-e102-419a-a472-bb2c65e126b0')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4648,4640,'122634-6','bfd6127b-a334-47d9-b899-b810ed6ac919')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4649,4640,'NAS501-3-5A','e458c577-5e22-4f62-908d-4f38a9f7ca02')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4650,4640,'NAS1149C0332R','0659956c-4b23-41fb-bf9b-c20f91522e37')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4651,4640,'109040-1','d6228b8a-4d65-477f-b8f0-4a32d41c905f')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4652,1,'122389-1','9375fbb0-5bf8-4d83-9035-e2d46515f244')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4653,4652,'121436-1','78022521-f836-47d8-a021-8f66720885f4')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4654,4653,'121436-3','e52ffc42-5a34-4ecc-b906-6abd76d01da3')
INSERT INTO [RawData] ([ID],[PID],[PartNo],[Description])VALUES(4655,4654,'121436-9','263c5e59-6a90-426f-974a-aa6f28a67500')

PRINT 'Done'
 
SET IDENTITY_INSERT [dbo].[RawData] OFF
GO

USE [Test]
GO
/****** Object:  Table [dbo].[RawDataCrossJoin]    Script Date: 03/06/2013 14:05:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RawDataCrossJoin]') AND type in (N'U'))
DROP TABLE [dbo].[RawDataCrossJoin]
GO

USE [Test]
GO
/****** Object:  Table [dbo].[RawDataCrossJoin]    Script Date: 03/06/2013 14:05:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RawDataCrossJoin](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MRDID] [int] NOT NULL,
	[RDID] [int] NOT NULL,
 CONSTRAINT [PK_RawDataCrossJoin] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET NOCOUNT OFF


JBelthoff
› As far as myself... I do this for fun!

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/06/2013 :  23:59:39  Show Profile  Reply with Quote
First of all many thanks for this post with data and required output. It really helps us to help you quickly and accurately!
Here you go with solution without a loop


Select *
From dbo.RawDataCrossJoin

;With SubAssemblyLevels As
	(
		Select e.ID,e.piD, 0 As Level,CAST(e.ID AS varchar(2000)) AS [Path] From dbo.RawData e --LEFT JOIN  dbo.RawData e1 ON e1.ID = e.Pid WHERE e1.ID IS NULL
		Union All
		Select e.ID,e.pID, Level + 1,CAST(CAST(sal.[Path] AS varchar(1000)) + '/' + CAST(e.ID AS varchar(10)) AS varchar(2000))
		From dbo.RawData e Inner Join SubAssemblyLevels sal On e.pID = sal.ID 
	)

	--Insert Into dbo.RawDataCrossJoin (MRDID, RDID, OHID, GFEID)
	Select  ROW_NUMBER() OVER (ORDER BY LEFT(Path,CHARINDEX('/',Path+'/')-1),s.ID) AS ID, LEFT(Path,CHARINDEX('/',Path+'/')-1) AS MRDID, s.ID RDID--, ohd.ID OHID, ohd.GFEID
	From SubAssemblyLevels s
	WHERE LEFT(Path,CHARINDEX('/',Path+'/')-1) <> s.ID
	Order By MRDID,RDID


output
-------------------------------------------------------
RawDataCrossJoin
ID          MRDID       RDID
----------- ----------- -----------
1           1           4640
2           1           4641
3           1           4642
4           1           4643
5           1           4644
6           1           4645
7           1           4646
8           1           4647
9           1           4648
10          1           4649
11          1           4650
12          1           4651
13          1           4652
14          1           4653
15          1           4654
16          1           4655
17          4640        4641
18          4640        4642
19          4640        4643
20          4640        4644
21          4640        4645
22          4640        4646
23          4640        4647
24          4640        4648
25          4640        4649
26          4640        4650
27          4640        4651
28          4642        4643
29          4642        4644
30          4642        4645
31          4652        4653
32          4652        4654
33          4652        4655
34          4653        4654
35          4653        4655
36          4654        4655

(36 row(s) affected)

solution without loop
ID                   MRDID      RDID
-------------------- ---------- -----------
1                    1          4640
2                    1          4641
3                    1          4642
4                    1          4643
5                    1          4644
6                    1          4645
7                    1          4646
8                    1          4647
9                    1          4648
10                   1          4649
11                   1          4650
12                   1          4651
13                   1          4652
14                   1          4653
15                   1          4654
16                   1          4655
17                   4640       4641
18                   4640       4642
19                   4640       4643
20                   4640       4644
21                   4640       4645
22                   4640       4646
23                   4640       4647
24                   4640       4648
25                   4640       4649
26                   4640       4650
27                   4640       4651
28                   4642       4643
29                   4642       4644
30                   4642       4645
31                   4652       4653
32                   4652       4654
33                   4652       4655
34                   4653       4654
35                   4653       4655
36                   4654       4655

(36 row(s) affected)





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 03/07/2013 :  08:34:42  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
Visakh,

Many thanks for this. It never occurred to me to build out a path and use that for the MRDID!!!! Brilliant!

Thanks again,

JBelthoff
› As far as myself... I do this for fun!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/07/2013 :  09:08:42  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.09 seconds. Powered By: Snitz Forums 2000