Sql-server – Recursive Updates on large table

sql serversql-server-2012

I am desperately seeking a solution for a problem, which i will try to fully describe hoping that a somebody can come up with a clever solution.

The metadata :

At the moment i have the following set of tables (minified to remove chatter) :

Schema

Table "Recipe" has a Foreign Key to table "Item" on Recipe.OutputITemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Item" on RecipeIngredient.ItemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Recipe" on RecipeIngredient.RecipeId = Recipe.Id

A recipe contains a number of ingredients that represent items. The items representing these ingredients could again be produced by a recipe. This creates a structure of recipe's and their ingredients.

At the moment the list of recipes is about 8000, the list of ingredients about 25000. I have written a recursive CTE that returns the tree structure resulting in 144000 rows. A fair amount of recipe's are reused.

The problem : Calculating the total cost.

The cost of a recipe has to be calculated based on the total cost (SUM SellValue) of all the descendant recipes or, when no recipe available for the current item, the SellValue of that particular item.
On stale data, traversing through a CTE with a cursor, these values could easily be updated, though this is not an option IMHO. The reason for this is that the SellValue of an item is updated VERY frequently, read constantly. An update of a certain item would cause numerous recipe's to have their pricing recalculated (up to 100 recipe's).

I have tried using recursive triggers, even though i hate em, on Item and Recipe updating the recipe based on its descendants, to no good effect.

Apparently my knowledge of SQL, plus searching Google for quite a while, didn't provide a feasible solution.

Please help, i'm pulling out the few hairs i have left… 😉

UPDATE 1:

The use of nested/recursive triggers on the recipe table led to a common problem, deadlocks. For now i'll investigate the possibility of using a recursive CTE with SUMS on the respective values.

Best Answer

Your schema diagram is good. You should not put any total cost column either to Recipe or RecipeIngedient as it is not compliant to normalization. And so there is no need for triggers. The total cost sum should be calculated on the fly in a query that you use to list recipes.

Related Solutions

Sql-server – Build a three table join with a recusive table in the middle

You can do the recursion in a CTE from the top down carrying MarkupGroupID with you.

with C as
(
  select P.PartGroupID,
         P.ParentID,
         P.MarkupGroupID
  from PartGroup as P
  where P.ParentID is null
  union all 
  select P.PartGroupID,
         P.ParentID,
         coalesce(P.MarkupGroupID, C.MarkupGroupID)
  from PartGroup as P 
    inner join C 
      on P.ParentID = C.PartGroupID
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  inner join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

SQL Fiddle

The recursive CTE will create a derived table that looks like this.

PartGroupID ParentID    MarkupGroupID
----------- ----------- -------------
1           NULL        NULL
2           NULL        1
3           NULL        2
15          3           2
16          3           2
17          3           2
4           1           3
5           1           4
8           5           4
13          8           4
14          8           4
6           4           3
7           4           3
11          7           3
12          7           3
9           6           3
10          6           3

If you need parts with partgroup not connected to a markup you can use a outer join against MarkupGroup in your main query.

with C as
(
  select ...
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  left outer join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

Categories and Buckets.

create table categories
(
  category_id int primary key
);

create table buckets
(
  bucket_id int primary key
);

Depending on how to interpret "an item may belong to at most 1 bucket" you need to do some different things with Items.

An item has to belong to one bucket.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int not null references buckets(bucket_id),
  unique (category_id, bucket_id)
);

If an item may belong to one bucket but does not have to you need to allow for null values in bucket_id and use a filtered index as the unique constraint.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int references buckets(bucket_id)
);

create unique index uq_items on items(category_id, bucket_id) where bucket_id is not null;

There is a collection of items where each item belongs to 1 category.

category_id as a foreign key in items.

an item may belong to at most 1 bucket.

bucket_id as a foreign key in items.

There is a collection of buckets which may contain at most 1 item from each category.

A unique constraint on bucket_id and category_id in items.

Best Answer

Related Solutions

Sql-server – Build a three table join with a recusive table in the middle

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

Related Question