Select recordIDs for Bill of Materials 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:


Have you tried reverse order in your joins?  I.e. start with PS=&gt;PSH=&gt;Item.
Have you tried inner joins versus left join?  Could see speed improvement (less data for sure)
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.