asked on

BOM explosion complexity counts

I have this piece of code that allows me to explode bills of materials, however I am needing the output to reflect the complexity of each parent number. The report should take each parent number and tell me how many unique children are under it. For example:

Parent_Part_Num Total_Complexity
400002-001 61
403000-001 12
400210-011 88

Can someone help me with this code to perform this output?

;WITH MyCTE
AS ( SELECT Parent_Part_Num,  levl = 1, Child_Part_Num 
FROM BOM_RAW t 
WHERE NOT EXISTS (SELECT 1 FROM BOM_TEST t1 WHERE t.Parent_Part_Num = t1.Child_Part_Num) 
UNION ALL
SELECT MyCTE.Parent_Part_Num, MyCTE.levl+1 ,e.Child_Part_Num 
FROM BOM_RAW e
INNER JOIN MyCTE ON e.Parent_Part_Num = MyCTE.Child_Part_Num

 )
SELECT *
INTO BOM_RAW_LEVELS
FROM MyCTE

ORDER BY PARENT_PART_NUM, levl
option (maxrecursion 0)

Open in new window

Duy Pham

As I understand, you want to count all children of all levels under each parent right? If that is the case, might just need a group by with distinct

;WITH MyCTE
AS ( SELECT Parent_Part_Num,  levl = 1, Child_Part_Num 
FROM BOM_RAW t 
WHERE NOT EXISTS (SELECT 1 FROM BOM_TEST t1 WHERE t.Parent_Part_Num = t1.Child_Part_Num) 
UNION ALL
SELECT MyCTE.Parent_Part_Num, MyCTE.levl+1 ,e.Child_Part_Num 
FROM BOM_RAW e
INNER JOIN MyCTE ON e.Parent_Part_Num = MyCTE.Child_Part_Num

 )
SELECT Parent_Part_Num, COUNT(DISTINCT Child_Part_Num)
INTO BOM_RAW_LEVELS
FROM MyCTE
GROUP BY Parent_Part_Num

Open in new window

Vitor Montalvão

Can you post sample data?
What do you have and what do you want to be returned?

Fairfield

ASKER

Duy,

I am getting an error

Msg 1038, Level 15, State 5, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

ASKER CERTIFIED SOLUTION

Duy Pham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fairfield

ASKER

I am not running into an error, however for just a few PARENT_PART_NUM's, it is running for along time. Is there a way to speed up the query or am I out of luck?

Duy Pham

It's hard to say without the data. The query is plain simple, so it sounds like we need some indexing. Try to review your query with SQL Sentry Plan Explorer, it is useful tool to dig into query optimization.

Fairfield

ASKER

I think the issue with performance is with the number of records I have in my source table for BOM parent/children (29million rows). Is there a better or more efficient way to do a bom explosion using a list of parent numbers?

Duy Pham

That's quite a number.

First, make sure you have good indexes on your source table when looking up on Parent_Part_Num and Child_Part_Num

You can try to use temp table which can have indexes and faster when number of rows is large, a sample code could be:
-- Create temp table CREATE TABLE [#tmpBOM] (Parent_Part_Num INT NOT NULL, Level INT, Child_Part_Num INT NOT NULL, ProcessState TINYINT NOT NULL DEFAULT(0)) CREATE CLUSTERED INDEX [IDX_tmpBOM_ParentNumber] ON [#tmpBOM] (Parent_Part_Num, Child_Part_Num) CREATE CLUSTERED INDEX [IDX_tmpBOM_ProcessState] ON [#tmpBOM] (ProcessState) -- Get top most parents DECLARE @foundRows INT = 0 INSERT [#tmpBOM](Parent_Part_Num, Level, Child_Part_Num) SELECT Parent_Part_Num, 1, Child_Part_Num FROM BOM_RAW t WHERE 0 = (SELECT COUNT(*) FROM BOM_TEST t1 WHERE t.Parent_Part_Num = t1.Child_Part_Num) -- Recursively add children to temp table SET @foundRows = @@ROWCOUNT WHILE @foundRows > 0 BEGIN -- Mark unprocessed rows (ProcessState = 0) as being processed (ProcessState = 1) UPDATE [#tmpBOM] SET ProcessState = 1 WHERE ProcessState = 0 -- Insert direct children for this recursive level INSERT [#tmpBOM](Parent_Part_Num, Level, Child_Part_Num) SELECT p.Parent_Part_Num, p.Level + 1, e.Child_Part_Num FROM [#tmpBOM] p INNER JOIN BOM_RAW e ON e.Parent_Part_Num = p.Child_Part_Num WHERE p.ProcessState = 1 -- Count number of children found to see whether we should looking further SET @foundRows = @@ROWCOUNT -- Mark processing row (ProcessState = 1) as done (ProcessState = 2) UPDATE [#tmpBOM] SET ProcessState = 2 WHERE ProcessState = 1 END -- Finally, select result SELECT Parent_Part_Num, COUNT(DISTINCT Child_Part_Num) AS Total_Complexity INTO BOM_RAW_LEVELS FROM [#tmpBOM] GROUP BY Parent_Part_Num -- Drop temp table DROP INDEX IDX_tmpBOM_ProcessState ON [#tmpBOM] DROP INDEX IDX_tmpBOM_ParentNumber ON [#tmpBOM] DROP TABLE [#tmpBOM]

Fairfield

ASKER

Do I run separately?

Msg 1902, Level 16, State 3, Line 5
Cannot create more than one clustered index on table '#tmpBOM'. Drop the existing clustered index 'IDX_tmpBOM_ParentNumber' before creating another.

Duy Pham

Sorry, it is copy/paste issue while creating the code on the fly. Change the second index IDX_tmpBOM_ProcessState to NONCLUSTERED since each table has one and only one clustered index.

Fairfield

ASKER

Another error.

Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value '470060-427 ' to data type int.

Duy Pham

Shame me, i forgot that your parent/child number are not integer.
Change this CREATE TABLE [#tmpBOM] (Parent_Part_Num INT NOT NULL, Level INT, Child_Part_Num INT NOT NULL, ProcessState TINYINT NOT NULL DEFAULT(0))
to CREATE TABLE [#tmpBOM] (Parent_Part_Num VARCHAR(32) NOT NULL, Level INT, Child_Part_Num VARCHAR(32) NOT NULL, ProcessState TINYINT NOT NULL DEFAULT(0)).

Fairfield

ASKER

Still runs very slow I am guessing it is due to the shear number of records.

Duy Pham

That could be. But I think you'd better double check the query in SQL Profiler or an query analyzer tool (as suggested here: https://www.experts-exchange.com/questions/28660858/BOM-explosion-complexity-counts.html?anchorAnswerId=40741597#a40741597) to see what makes query time-consuming: IO, memery, CPU or missing indexes.

Fairfield

ASKER

This is what I see with the profiler

Duy Pham

It seems that your BOM_RAW table doesn't have indexes that can be used, so Table Scan (it should be Index Scan like BOM_TEST table) is used when querying rows in BOM_RAW table. Try to add an index for Parent_Part_Num on BOM_RAW to see if it could improve the performance?

Fairfield

ASKER

So I can just do this, correct?

CREATE INDEX Parent
ON BOM_RAW (PARENT_PART_NUM)

Do I need to replace PARENT_PART_NUM with the index (Parent) in my tsql?

Duy Pham

Yes you can create that index.

Do I need to replace PARENT_PART_NUM with the index (Parent) in my tsql?

Not sure what you mean here? There will be no change in the query (or t-sql), newly created index will be applied automatically to the query (or t-sql) if applicable.