Matt Miller (11/9/2007)
If it's a process THIS expensive, you should consider making this a permanent table. Meaning - keep the existing table just as it is, and have another to track the atomic parts manifest.I can't imagine that your sub assembly parts change all that often and the properly designed stored proc could "recalculate" only those items with affected assemblies/sub-assemblies.
I absolutely agree! And, if you're going to do that, make lookups fast! Joe Celko's "Nested Set" hierarchies are the berries!
--Jeff Moden
Change is inevitable... Change for the better is not.