| Author |
 Topic  |
|
|
JBelthoff
Posting Yak Master
USA
171 Posts |
 Posted - 03/06/2013 : 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
India
47023 Posts |
Posted - 03/06/2013 : 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
USA
171 Posts |
Posted - 03/06/2013 : 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
USA
171 Posts |
Posted - 03/06/2013 : 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
India
47023 Posts |
Posted - 03/06/2013 : 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
India
47023 Posts |
Posted - 03/06/2013 : 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
USA
171 Posts |
Posted - 03/06/2013 : 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
India
47023 Posts |
Posted - 03/06/2013 : 13:38:23
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Help Eliminating Loop in Query
