Help Eliminating Loop in Query 2

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 Eliminating Loop in Query 2

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-03-06 : 14:09:14

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

52326 Posts

Posted - 2013-03-06 : 23:59:39

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/

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-03-07 : 08:34:42

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 09:08:42

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources