November 15, 2024 at 8:26 pm
I've read lots of the horror stories about nested views in databases where they're like 7 levels deep... hence the question.
I'm working on the bakery thing (well, because parts of the data problem are interesting). In this case, I'm looking at the recipe where some ingredients are sort of "subassemblies". In this case, the recipe will list all the ingredients and then list 500 grams of "Starter A" as an ingredient, for example. In order to get the "base ingredients" for a recipe, I created a view because I wanted to basically use it like a table, because it basically encapsulates the ugly logic of the scaling and substitutions. It looks like this <cringe>:
ALTER VIEW [dbo].[vwBaseIngredientRecipe]
/* NOTES:
The idea here is to split the Starter from the rest of the recipe
because Starter has its own "sub-recipe" (Potato flakes, flour, water for the most part)
This is necessary to break the ingredient list all the way down to just raw
ingredients.
Once that's done, get the sum of the weight of each raw ingredient (for pricing)
*/AS
WITH cteRecipe(RecipeID, RecipeName, IngredientID, IngredientName, RecipeWeight, Yield)
/* NOTES:
The idea here is to split the Starter from the rest of the recipe
because Starter has its own "sub-recipe" (Potato flakes, flour, water for the most part)
This is necessary to break the ingredient list all the way down to just raw
ingredients.
Once that's done, get the sum of the weight of each raw ingredient (for pricing)
*/AS (
SELECT
r.RecipeID
,r.[Recipe Name]
,i.IngredientID
,i.IngredientName
,ExpandedRI.RecipeIngredientWeight
,r.Yield
FROM
/* non-Starter ingredients */(SELECT ri.RecipeID
,ri.IngredientID
,i.IngredientName
,ri.RecipeIngredientWeight
FROM RecipeIngredient ri
INNER JOIN Ingredient i ON ri.IngredientID = i.IngredientID
WHERE /* ri.RecipeID = @RecipeID
AND */ i.IngredientName NOT LIKE '%Starter%'
UNION ALL
/* Starter Ingredients */SELECT ri.RecipeID
,sr.IngredientID
,ri.RecipeIngredientWeight * sr.WeightGrams/
(1.000 * SUM(sr.WeightGrams) OVER (PARTITION BY ri.RecipeID))
ri.RecipeIngredientWeight * sr.WeightGrams/(1.000 * SUM(sr.WeightGrams) OVER (PARTITION BY ri.RecipeID))
FROM RecipeIngredient ri
INNER JOIN Ingredient i ON ri.IngredientID = i.IngredientID
INNER JOIN StarterRecipe sr ON i.IngredientID = sr.ParentIngredientID
WHERE /* ri.RecipeID = @RecipeID
AND */ i.IngredientName LIKE '%Starter%') ExpandedRI
INNER JOIN Recipe r
ON ExpandedRI.RecipeID = r.RecipeID
INNER JOIN Ingredient i
ON ExpandedRI.IngredientID = i.IngredientID
)
SELECT
cte.RecipeID
,cte.RecipeName
,cte.IngredientID
,cte.IngredientName
,cte.RecipeWeight
,SingleLoafIngredientWeightGrams = cte.RecipeWeight/(1.000 * r.Yield)
,r.Yield
FROM cteRecipe cte
INNER JOIN Recipe r
ON r.RecipeID = cte.RecipeID;
The idea is that I can then join that to various other tables etc to create things like Ingredient Cost summaries (join to "most recent price" query in a CTE or whatever).
So at what point is having nested queries okay vs too much? That view is always close to top level - never more than 2 levels down. At what point does this conflict with the Don't Repeat Yourself adage? (by basically copying and pasting the definition of the view into different queries). For example, if I wanted to get cost details for a recipe, I'd join the Recipe (RecipeID, IngredientID, Yield) and a "latest price as of date" query to get the current price (just P*Q).
Basically, I reuse the query all over the place because it's instrumental in answering so many other questions.
Thanks!
November 16, 2024 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 19, 2024 at 4:38 pm
Some reasons for avoiding nested views is:
It makes it harder to the optimizer to calculate a good execution plan (work strategy) because there are more possible combinations.
It may hide non-sargable views that are detrimental to performance ( everything with item X table scans, translated names ...)
Personally I like views as long they are sargable and SQL server may retrieve the items quickly
About your recipes, there are some articles about bill of materials, hierarchy implementations on this site
November 25, 2024 at 9:39 pm
I'm a little late to the party, but I have plenty of horror stories about nested views. Mostly for the reasons Jo mentions but also troubleshooting problems is a major PITA when you have to keep digging.
I recommend trying a data-driven table solution with junction tables to join multiple tables as needed. Then you need only one script with certain input parameters that matches a row in your "recipe" junction table that reaches out to all necessary tables and pulls everything together.
Measurements is one table, ingredients is a table, order of ingredients is a third (maybe this is a junction), baking temperatures and pan types / sizes are other tables. Recipe names is yet another table. The IDs of all the tables go into one junction table and that's the one you select to join to all the other tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply