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!
Help Eliminating Loop in Query 2
Posted - 03/06/2013 : 14:09:14
