🚀 go-pugleaf

In article <6vaep7$dfs$1@nnrp1.dejanews.com>, tonyriches@ozemail.com.au writes:

> Hi,

> I recently faced a similar problem. I had to create such a structure from two
> formatted text files. One had the individual inventory items - 20000 of them,
> and the other the bill of materials in the format UpperCode,
> RevisionNo, LowerCode, RevisionNo2, Qty - 130000 lines.

> I imported them into access, and have now written a VB program to create a
> routing profile for each finished goods code. It is a fairly basic program in
> that it gets all the lower level codes for an upper code, then cycles through
> each of those and gets the lower level codes for them etc...to create the
> routing profile for 1700 products currently takes over an hour using a pII 266
> so i don't think it is very efficient.

I recently used a variation involving storing the entire parts
explosion with a flag indicating whether an entry in the bill of
materials table was a direct or indirect component. A very rough
stab is at <http://x6.dejanews.com/getdoc.xp?AN=366538824>. Anyway,
the disk and/or network IO throughput's much more important than
the CPU speed. When in doubt though, more RAM usually helps!

Yes, my code definitely has some room for improvement. First off,
you'd only want to examine parts with grandchildren, so its query
would look something like this:

select distinctrow part.id
from (part inner join partsisparts as p1 on part.id = p1.assembly)
inner join partsisparts as p2 on p1.component = p2.assembly

You'd want to make similar modifications in the UpdateBOM
subroutine. There, the first query should indicate if the part
has direct components but no indirect components in the
partsisparts table. The second query also should do something
similar. That ought to eliminate most of the redundant
inspections in my original code.

> I read some replies to the original message saying a single sql query could
> do this...if so i would love to hear it as i need to be able to reduce the
> speed of the process so it can be run every day...

The single SQL methods I've seen assume that the parts explosion
is fairly shallow.

-- 
Joe Foster <mailto:jfoster@ricochet.net> Spam is irrelevant. Assimilate this:
<ftp://ftp.microsoft.com/softlib/index.txt> Microsoft's master patch list
<ftp://ftp.microsoft.com/softlib/mslfiles/> MS I got yer patch right here!
<http://www.microsoft.com/msdn/>            MS Knowledge Base & more
<http://www.dejanews.com/home_ps.shtml>     Usenet search and archival engine
<http://www.altavista.digital.com>          WWW *and* Usenet search engine
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!