0
\$\begingroup\$

Here I have a stored procedure that seems to be taking a little longer to run than I had hoped for. What made this algorithm so lengthy was the fact when adding an element to a new table in SQL it seemed to not always get put at the top.

I also had to query a query which created another table. All in all, this algorithm is too big. Does anyone have any ideas to make it shorter?

DECLARE @RecipeQuery TABLE(
RecipeID NCHAR(100),
MaterialID NCHAR(100),
Quantity DECIMAL(18,4));

INSERT INTO @RecipeQuery
(RecipeID, MaterialID, Quantity)
SELECT RecipeID, MaterialID, Quantity
FROM Recipe

DECLARE @PrevRecipeQuery TABLE(
prevRecipeID NCHAR(100),
prevMaterialID NCHAR(100),
prevQuantity DECIMAL(18,4));

DECLARE @TempRecipeQuery TABLE(
TempRecipeID NCHAR(100),
TempMaterialID NCHAR(100),
TempQuantity DECIMAL(18,4));

DECLARE @MaterialFinder TABLE(
RID NCHAR(100),
MID NCHAR(100),
Q DECIMAL(18,4));

DECLARE @PrevMaterialFinder TABLE(
PRID NCHAR(100),
PMID NCHAR(100),
PQ DECIMAL(18,4));

DECLARE @CalcMaterial TABLE(
CalcRecipeID NCHAR(100),
CalcMaterialID NCHAR(100),
CalcQuantity DECIMAL(18,4));

DECLARE @ROWCOUNT1 INT
SET @ROWCOUNT1 = 0

DECLARE @ROWCOUNT2 INT
SET @ROWCOUNT2 = 0

DECLARE @ROWCOUNT3 INT 
SET @ROWCOUNT3 = 0

DECLARE @isDone INT
SET @isDone = 0

DECLARE @mainRowCount INT

----------------------------------------------------------------------------

--LOOP UNTIL ALL LEVELS HAVE BEEN FOUND
WHILE(@isDone != 1)
BEGIN

SET @mainRowCount = (SELECT COUNT(*) FROM @RecipeQuery)

--LOOP THROUGH EACH ROW IN THE TABLE UNTIL ALL ROWS HAVE BEEN LOOKED AT
WHILE(@ROWCOUNT1 < @mainRowCount)
BEGIN

--IF THE ROW'S MATERIAL STARTS WITH A "TempRecipeID" THAT MEANS IT IS DONE
IF(PATINDEX('R%', (SELECT TOP 1 MaterialID FROM @RecipeQuery)) = 1)
BEGIN
--INSERT THE ROW RIGHT INTO TABLE new
    INSERT INTO @CalcMaterial (CalcRecipeID, CalcMaterialID, CalcQuantity) SELECT TOP 1 RecipeID, MaterialID, Quantity FROM @RecipeQuery
    SET @ROWCOUNT3 = @ROWCOUNT3 + 1
END
--OTHERWISE
ELSE
BEGIN
--FIND THE MATERIAL'S RECIPE AND PLACE THE NEW RECIPEID AND MATERIALID INTO TABLE TempMaterialID
    INSERT INTO @PrevMaterialFinder (PRID, PMID, PQ) SELECT TOP(1) RecipeID, MaterialID, Quantity FROM @RecipeQuery
    INSERT INTO @MaterialFinder (RID, MID, Q) SELECT (SELECT TOP 1 RecipeID FROM @RecipeQuery), MaterialID, Quantity FROM Recipe WHERE RecipeID = (SELECT TOP 1 MaterialID FROM @RecipeQuery)

    DECLARE @ROWCOUNT4 INT
    SET @ROWCOUNT4 = (SELECT COUNT(*) FROM @MaterialFinder)
    WHILE(@ROWCOUNT2 < @ROWCOUNT4)
    BEGIN
        INSERT INTO @CalcMaterial
        (CalcRecipeID, CalcMaterialID, CalcQuantity)
        SELECT TOP 1 RID, MID, Q*(SELECT TOP 1 PQ FROM @PrevMaterialFinder)*0.001
        FROM @MaterialFinder

        DELETE TOP(1) FROM @MaterialFinder

        SET @ROWCOUNT2 = @ROWCOUNT2 + 1
    END
    SET @ROWCOUNT2 = 0
END

SET @ROWCOUNT2 = 0

--DELETE THE TOP ROW
DELETE TOP(1)
FROM @RecipeQuery

--INSERT THE UPDATED ROW(S) FROM TABLE TempMaterialID INTO TABLE W
INSERT INTO @TempRecipeQuery
(TempRecipeID, TempMaterialID, TempQuantity)
SELECT CalcRecipeID, CalcMaterialID, CalcQuantity
FROM @CalcMaterial

--DELETE THE UPDATED ROWS FROM TABLE TempMaterialID
DELETE FROM @MaterialFinder
DELETE FROM @PrevMaterialFinder
DELETE FROM @CalcMaterial

--INCREASE ROW COUNTER C
SET @ROWCOUNT1 = @ROWCOUNT1 + 1

--LOOP ONTO NEXT ROW
END
--AT THIS POINT ALL OF THE ROWS HAVE BEEN GONE THROUGH AT LEAST ONCE

--INSERT THE UPDATED ROWS IN TABLE W INTO THE MAIN TABLE 
INSERT INTO @RecipeQuery
(RecipeID, MaterialID, Quantity)
SELECT TempRecipeID, TempMaterialID, TempQuantity
FROM @TempRecipeQuery

--TO COMPARE
--IF THE COUNT OF BOTH TABLES IS THE SAME THEY COULD BE THE SAME
IF((SELECT COUNT(*) FROM @prevRecipeQuery) = (SELECT COUNT(*) FROM @RecipeQuery))
BEGIN
--IF THE DIFFERENCES BETWEEN THE TWO ARE NULL THEN THEY ARE THE SAME;
    IF(SELECT MaterialID FROM @RecipeQuery EXCEPT SELECT prevMaterialID FROM @prevRecipeQuery) IS NULL
--SET DONE TO 1 TO END LOOP
    SET @isDone = 1
END

--DELETE PREV TABLE FOR NEW UPDATE
DELETE FROM @prevRecipeQuery

--SET COPY INTO PREV
INSERT INTO @prevRecipeQuery
(prevRecipeID, prevMaterialID, prevQuantity)
SELECT RecipeID, MaterialID, Quantity
FROM @RecipeQuery

--SET THE ROW COUNTER BACK TO 0
SET @ROWCOUNT1 = 0

--DELETE ALL FROM THE TEMP UPDATED TABLE SO THAT IT CAN BE FILLED WITH UPDATED ROWS IF AGAIN
DELETE FROM @TempRecipeQuery

--AT THIS POINT THE LOOP WILL REPEAT IF THERE ARE ANY MORE STOCK SOLUTIONS AS MATERIALS
END

--AT THIS POINT, ALL LOOPS ARE DONE AND THE MAIN TABLE DOES NOT CONSIST OF ANY STOCK MATERIALS AS MATERIALS
--PRINT THE MAIN TABLE
SELECT RecipeID, MaterialID, Quantity FROM @RecipeQuery
ORDER BY RecipeID
\$\endgroup\$
1
  • \$\begingroup\$ I doubt you need a cursor at all, looks like either some recursion or non-equi-join(s). Can you add some example data? \$\endgroup\$ – dnoeth Dec 1 '17 at 6:54
1
\$\begingroup\$

Like @dnoeth said, it would be helpful to have some example data and to also know what you want from this code. Without knowing that, we can only really comment on how well-written and formatted your code is, rather than a way to improve its functionality.

So I'm gonna take a shot in the dark here and guess that you have a self-referential tree hierarchy table and you want a better query to return the base (bottom-level) materials needed based on an input recipe.

In these kinds of cases it's often better to use recursive common table expressions or recursive CTE. A quick google should give you a more in-depth explanation than I can, but generally a CTE is like a sub-query that can reference itself, meaning it's probably the best solution here if your problem is solving a tree hierarchy.

I'll give an example that might be relevant to your use case:

First we'll make a table to store the relationships. SurrogateId is an auto-increment number (just a unique identifier), Recipe describes the parent, Material describes the child, quantity says how many of the material are part of that recipe.

declare @Materials table (
    SurrogateId int identity(1,1) 
    , Recipe varchar(100)
    , Material varchar(100)
    , Quantity int
)

Some data

insert into @Materials values 
    ('Oranges', 'Oranges', 1)
    , ('Lemons', 'Lemons', 1)
    , ('Citrus', 'Oranges', 1)
    , ('Citrus', 'Lemons', 1)
    , ('Copper', 'Copper', 1)
    , ('Tin', 'Tin', 1)
    , ('Alloy', 'Copper', 4)
    , ('Alloy', 'Tin', 1)
    , ('Mould', 'Mould', 1)
    , ('Cup', 'Alloy', 1)
    , ('Cup', 'Mould', 1)
    , ('Rope', 'Rope', 1)
    , ('Ball', 'Ball', 1)
    , ('Clapper', 'Rope', 1)
    , ('Clapper', 'Ball', 1)
    , ('Bells', 'Cup', 8)
    , ('Bells', 'Clapper', 8)
    , ('Bells', 'Rope', 6)
    , ('Walls', 'Walls', 1)
    , ('Windows', 'Windows', 1)
    , ('Ceiling', 'Ceiling', 1)
    , ('St Clement''s', 'Walls', 4)
    , ('St Clement''s', 'Windows', 10)
    , ('St Clement''s', 'Ceiling', 1)
    , ('St Clement''s', 'Bells', 1)

Base materials are defined as having the same Recipe as Material, but you can probably use a more logical solution – the method of defining base materials is not important, just make sure you somehow define it. To give an example of a relationship, Tin and Copper are base materials. Alloy is comprised of 4 Copper and 1 Tin. The bell Cup is made from 1 Alloy and 1 Mould (Mould is also a base material). Etc.

Next is the CTE.

;with items as (
    select 
        SurrogateId
        , Recipe
        , Recipe as Intermediate
        , Material
        , Quantity
    from @Materials
    where Recipe = Material

    union all

    select
        m.SurrogateId
        , m.Recipe
        , i.Recipe
        , i.Material
        , m.Quantity * i.Quantity
    from @Materials m
        inner join items i
        on i.Recipe = m.Material and m.SurrogateId <> i.SurrogateId
)

The part above the union all first populates the items table with only base materials. Then, the part below that selects all recipes that comprise of anything already in items. This is the recursion: The first step adds Tin and Copper, then the recursion adds Alloys as it comprises of Tin and Copper, then it adds Cup requiring the base materials Mould, Tin and Copper, and so on up the tree.

Now you have a table items that is like the @Materials table, except it lists only base (bottom-level) materials. Here's what my example looks like:

Recipe              Material     Quantity
------------------  -----------  -----------
Alloy               Copper       4
Alloy               Tin          1
Ball                Ball         1
Bells               Ball         8
Bells               Rope         8
Bells               Mould        8
Bells               Tin          8
Bells               Copper       32
Bells               Rope         6
Ceiling             Ceiling      1
Citrus              Lemons       1
Citrus              Oranges      1
Clapper             Ball         1
Clapper             Rope         1
Copper              Copper       1
Cup                 Tin          1
Cup                 Copper       4
Cup                 Mould        1
Lemons              Lemons       1
Mould               Mould        1
Oranges             Oranges      1
Rope                Rope         1
St Clements         Rope         6
St Clements         Ball         8
St Clements         Rope         8
St Clements         Mould        8
St Clements         Tin          8
St Clements         Copper       32
St Clements         Ceiling      1
St Clements         Walls        4
St Clements         Windows      10
Tin                 Tin          1
Walls               Walls        1
Windows             Windows      1

Now it's just a case of simply querying that table for the data you want.

I hope this answers your question, and sorry if I got the complete wrong idea!

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.