• Status: Solved
• Priority: Medium
• Security: Public
• Views: 126

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)
``````
0
Fairfield
• 9
• 8
1 Solution

Freelance IT ConsultantCommented:
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
``````
0

MSSQL Senior EngineerCommented:
Can you post sample data?
What do you have and what do you want to be returned?
0

Author Commented:
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.
0

Freelance IT ConsultantCommented:
@Fairfield:  Sorry my bad, we need an alias for COUNT(DISTINCT Child_Part_Num). Here is updated code:
``````;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) AS Total_Complexity
INTO BOM_RAW_LEVELS
FROM MyCTE
GROUP BY Parent_Part_Num
``````
0

Author Commented:
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?
0

Freelance IT ConsultantCommented:
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.
0

Author Commented:
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?
0

Freelance IT ConsultantCommented:
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]
0

Author Commented:
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.
0

Freelance IT ConsultantCommented:
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.
0

Author Commented:
Another error.

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

Freelance IT ConsultantCommented:
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)).
0

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

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

Author Commented:

This is what I see with the profiler
0

Freelance IT ConsultantCommented:
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?
0

Author Commented:
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?
0

Freelance IT ConsultantCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.