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/

Go to Top of Page

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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

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

Go to Top of Page
   

- Advertisement -