Help Eliminating Loop in Query

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

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-03-06 : 12:25:51

I have a 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.

Any help is in this regard would be greatly appreciated.

Here is the loop...


Declare @RDID int
Set @RDID = 1

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

While @RDID <= @MaxRDID
Begin

	-- 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, OHID, GFEID)
	Select @RDID MRDID, s.ID RDID, ohd.ID OHID, ohd.GFEID
	From SubAssemblyLevels s
		Inner Join dbo.OverhaulData ohd
			On ohd.RDID = s.ID
	Order By ohd.ID

	Set @RDID = @RDID + 1

End

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 12:36:09

seems like this


;With SubAssemblyLevels As
	(
		Select e.ID,e.piD, 0 As Level 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 From dbo.RawData e Inner Join SubAssemblyLevels sal On e.pID = sal.ID 
	)

	Insert Into dbo.RawDataCrossJoin (MRDID, RDID, OHID, GFEID)
	Select  s.pID, s.ID RDID, ohd.ID OHID, ohd.GFEID
	From SubAssemblyLevels s
		Inner Join dbo.OverhaulData ohd
			On ohd.RDID = s.ID
	Order By ohd.ID

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

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-03-06 : 12:42:31

Hi visakh16,

I'm not sure that is correct. My loops gives me 110798 rows while your query give me only 23080 rows...

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

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-03-06 : 12:49:01

OK,

Sorry but your query only gives me exactly what is in the RawData table.

I need....

For each ID in the RawData Table a result set of parent/child resultsets for that ID.

That is what my loop is doing....

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 12:53:54

your query was taking each record and getting their child which means some records will repeat. mine will take them only once

What should be your required output?

show some sample data and your required output for it to make me understand your exact scenario

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 12:56:18

in that case wont this suffice?


;With SubAssemblyLevels As
	(
		Select e.ID,e.piD, 0 As Level From dbo.RawData e 
		Union All
		Select e.ID,e.pID, Level + 1 From dbo.RawData e Inner Join SubAssemblyLevels sal On e.pID = sal.ID 
	)

	Insert Into dbo.RawDataCrossJoin (MRDID, RDID, OHID, GFEID)
	Select  s.pID, s.ID RDID, ohd.ID OHID, ohd.GFEID
	From SubAssemblyLevels s
		Inner Join dbo.OverhaulData ohd
			On ohd.RDID = s.ID
	Order By ohd.ID

in any case some sample data and output you want will help

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

JBelthoff
Posting Yak Master

173 Posts

Posted - 2013-03-06 : 13:05:52

Hi again visakh16,

This looks a little closer to what I need.

Give me some time to put together some sample data for you and I will try starting a new thread.

Thanks for your help with this.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 13:38:23

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources