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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 03/06/2013 :  12:25:51  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
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

India
52309 Posts

Posted - 03/06/2013 :  12:36:09  Show Profile  Reply with Quote
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/

Go to Top of Page

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 03/06/2013 :  12:42:31  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
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!
Go to Top of Page

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 03/06/2013 :  12:49:01  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/06/2013 :  12:53:54  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/06/2013 :  12:56:18  Show Profile  Reply with Quote
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/

Go to Top of Page

JBelthoff
Posting Yak Master

USA
173 Posts

Posted - 03/06/2013 :  13:05:52  Show Profile  Visit JBelthoff's Homepage  Send JBelthoff an AOL message  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/06/2013 :  13:38:23  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.08 seconds. Powered By: Snitz Forums 2000