trjhtr

I have a hierarchy of parts in my database. For example, one part (call it 0010102-0112-315) is a resistor. It's used in a billion different top-level assemblies. My final goal is to get all the top level product IDs related to the low level IDs (e.g. 0010102-0112-315).

My current approach is to left join the table onto itself 8 times (one past the max product depth), and when the recordId after is NULL then it must be a top level recordId.

However, the query takes almost 7 minutes to run, and I have a feeling I'm doing something wrong.

I can't use CTEs (to my knowledge) because we're using SQL Server 2003 (before CTEs). Is there a better way of doing this?

Here's my current code:

SELECT I1.IMA_ItemID as ItemID1, 
 I1.IMA_ProdFam as ProdFam1
 ,I1.IMA_RecordID as RecordId1 -- same as PSH.PSH_IMA_RecordID
 ,I2.IMA_RecordID as RecordId2
 ,I3.IMA_RecordID as RecordId3
 ,I4.IMA_RecordID as RecordId4
 ,I5.IMA_RecordID as RecordId5
 ,I6.IMA_RecordID as RecordId6
 ,I7.IMA_RecordID as RecordId7
 ,I8.IMA_RecordID as RecordId8
 ,I9.IMA_RecordID as RecordId9
FROM Item AS I1
 left join ProductStructureHeader AS PSH1 ON PSH1.PSH_IMA_RecordID = I1.IMA_RecordID
 left join ProductStructure AS PS1 on PS1.PST_PSH_RecordID = PSH1.PSH_RecordID
 left join Item AS I2 ON I2.IMA_RecordID = PS1.PST_IMA_RecordID

 left join ProductStructureHeader AS PSH2 ON PSH2.PSH_IMA_RecordID = I2.IMA_RecordID
 left join ProductStructure AS PS2 on PS2.PST_PSH_RecordID = PSH2.PSH_RecordID
 left join Item AS I3 ON I3.IMA_RecordID = PS2.PST_IMA_RecordID

 left join ProductStructureHeader AS PSH3 ON PSH3.PSH_IMA_RecordID = I3.IMA_RecordID
 left join ProductStructure AS PS3 on PS3.PST_PSH_RecordID = PSH3.PSH_RecordID
 left join Item AS I4 ON I4.IMA_RecordID = PS3.PST_IMA_RecordID 

 left join ProductStructureHeader AS PSH4 ON PSH4.PSH_IMA_RecordID = I4.IMA_RecordID
 left join ProductStructure AS PS4 on PS4.PST_PSH_RecordID = PSH4.PSH_RecordID
 left join Item AS I5 ON I5.IMA_RecordID = PS4.PST_IMA_RecordID 

 left join ProductStructureHeader AS PSH5 ON PSH5.PSH_IMA_RecordID = I5.IMA_RecordID
 left join ProductStructure AS PS5 on PS5.PST_PSH_RecordID = PSH5.PSH_RecordID
 left join Item AS I6 ON I6.IMA_RecordID = PS5.PST_IMA_RecordID 

 left join ProductStructureHeader AS PSH6 ON PSH6.PSH_IMA_RecordID = I6.IMA_RecordID
 left join ProductStructure AS PS6 on PS6.PST_PSH_RecordID = PSH6.PSH_RecordID
 left join Item AS I7 ON I7.IMA_RecordID = PS6.PST_IMA_RecordID 

 left join ProductStructureHeader AS PSH7 ON PSH7.PSH_IMA_RecordID = I7.IMA_RecordID
 left join ProductStructure AS PS7 on PS7.PST_PSH_RecordID = PSH7.PSH_RecordID
 left join Item AS I8 ON I8.IMA_RecordID = PS7.PST_IMA_RecordID

 left join ProductStructureHeader AS PSH8 ON PSH8.PSH_IMA_RecordID = I8.IMA_RecordID
 left join ProductStructure AS PS8 on PS8.PST_PSH_RecordID = PSH8.PSH_RecordID
 left join Item AS I9 ON I9.IMA_RecordID = PS8.PST_IMA_RecordID

Some other obscure facts that might be useful:

• 
  RecordId1 above is the highest level RecordId


• Selecting * where IMA_ItemStatusCode = 'Active' and IMA_ItemTypeCode = 'Purchased Item' from the Item table gives the lowest level items


• Can't think of anything else, but feel free to ask as there might be something.

Here are a couple of Questions:

1. Have you tried reverse order in your joins? I.e. start with PS=>PSH=>Item.


2. Have you tried inner joins versus left join? Could see speed improvement (less data for sure)


3. Is there any way to recurse only on item? In other words, can you get PS1.PST_IMA_RecordID from your Item row? If so, then you would only recurse on one table.

Or you could use a temp table and recurse on it. That would save a lot of data fetching. Something like:

use mydb

CREATE TABLE #myTmpTable 
 ( 
 IMA_RecordID uniqueidentifier default null
 , PST_IMA_RecordID uniqueidentifier default null
 )

-- Put recordID and parentID in temp table
insert into #myTmpTable
select 
 I1.IMA_RecordID
 , PS1.PST_IMA_RecordID
FROM Item AS I1
 left join ProductStructureHeader AS PSH1 ON PSH1.PSH_IMA_RecordID = I1.IMA_RecordID
 left join ProductStructure AS PS1 on PS1.PST_PSH_RecordID = PSH1.PSH_RecordID
 --left join Item AS I2 ON I2.IMA_RecordID = PS1.PST_IMA_RecordID

-- Now you can recurse on temp table
SELECT t1.IMA_ItemID as ItemID1, 
 --I1.IMA_ProdFam as ProdFam1 --you can left join this from item
 t1.IMA_RecordID as RecordId1
 ,t2.IMA_RecordID as RecordId2
 ,t3.IMA_RecordID as RecordId3
 ,t4.IMA_RecordID as RecordId4
 ,t5.IMA_RecordID as RecordId5
 ,t6.IMA_RecordID as RecordId6
 ,t7.IMA_RecordID as RecordId7
 ,t8.IMA_RecordID as RecordId8
 ,t9.IMA_RecordID as RecordId9
from #myTmpTable t1
inner join #myTmpTable t2 on t2.IMA_RecordID = t1.PST_IMA_RecordID
inner join #myTmpTable t3 on t3.IMA_RecordID = t2.PST_IMA_RecordID
inner join #myTmpTable t4 on t4.IMA_RecordID = t3.PST_IMA_RecordID
inner join #myTmpTable t5 on t5.IMA_RecordID = t4.PST_IMA_RecordID
inner join #myTmpTable t6 on t6.IMA_RecordID = t5.PST_IMA_RecordID
inner join #myTmpTable t7 on t7.IMA_RecordID = t6.PST_IMA_RecordID
inner join #myTmpTable t8 on t8.IMA_RecordID = t7.PST_IMA_RecordID
inner join #myTmpTable t9 on t9.IMA_RecordID = t8.PST_IMA_RecordID

DROP TABLE #myTmpTable