Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am working on a hobby project called Menu/Recipe Management.

This is how my entities and their relations look like.

A Nutrient has properties Code and Value

An Ingredient has a collection of Nutrients

A Recipe has a Collection of Ingredients and occasionally can have a collection of other recipes

A Meal has a Collection of Recipes and Ingredients

A Menu has a Collection of Meals

The relations can be depicted as

Menu Entities and Relationships

In one of the pages, for a selected menu I need to display the effective nutrients information calculated based on its constituents (Meals, Recipes, Ingredients and the corresponding nutrients).

As of now am using SQL Server to store the data and I am navigating the chain from my C# code, starting from each meal of the menu and then aggregating the nutrient values.

I think this is not an efficient way as this calculation is being done every time the page is requested and the constituents change occasionally.

I was thinking about a having a background service that maintains a table called MenuNutrients ({MenuId, NutrientId, Value}) and will populate/update this table with the effective nutrients when any of the component (Meal, Recipe, Ingredient) changes.

I feel that a GraphDB would be a good fit for this requirement, but my exposure to NoSQL is limited.

I want to know what are the alternative solutions/approaches to this requirement of displaying the nutrients of a given menu.

Hope my description of the scenario is clear.

share|improve this question

migrated from stackoverflow.com Oct 21 '13 at 20:38

This question came from our site for professional and enthusiast programmers.

    
How many objects are we talking? Will performance really be an issue? –  flup Oct 9 '13 at 23:06
    
@flup On an average a Menu can have 8 meals, each meal can have 2 recipes and 2 ingredients, each recipe can have 6-8 ingredients. –  Chandu Oct 9 '13 at 23:38
    
Aren't your arrows are in the wrong direction? –  Branko Dimitrijevic Oct 10 '13 at 1:00
    
Have you seen Nerd Dinner Entity Framework sample? –  Akash Kava Oct 12 '13 at 21:05

7 Answers 7

up vote 8 down vote accepted

Based on the requirements and architecture, there may be performance improvement options:

  • You may use indexed views(matrialized) To improve read performance on RDBMS(Sql server) level.
    Basically, all you need to do is:
    Create a regular view.
    Create a clustered index on that view.

  • Using a cashing mechanism in application level will improve performance.
    If its possible and feasible to use cashing, having a cash strategy like singleton lazy cashing will help you.

NoSql:
There are lots of good articles about Sql vs NoSql, like this and this

The parts interests me :

Where to use NoSql:

If your DB is 3NF and you don’t do any joins (you’re just selecting a bunch of tables and putting all the objects together, AKA what most people do in a web app.

When used be ready to:

  • You end up writing jobs to do things like joining data from different tables/collections, something that an RDBMS would do for you automatically.
  • Your query capabilities with NoSQL are drastically crippled. MongoDb may be the closest thing to SQL but it is still extremely far behind. Trust me. SQL queries are super intuitive, flexible and powerful. NoSql queries are not.
  • MongoDb queries can retrieve data from only one collection and take advantage of only one index. And MongoDb is probably one of the most flexible NoSQL databases. In many scenarios, this means more round-trips to the server to find related records. And then you start de-normalizing data - which means background jobs.
  • The fact that it is not a relational database means that you won't have (thought by some to be bad performing) foreign key constrains to ensure that your data is consistent. I assure you this is eventually is going to create data inconsistencies in your database. Be prepared. Most likely you will start writing processes or checks to keep your database consistent, which will probably not perform better than letting the RDBMS do it for you.
  • Forget about mature frameworks like hibernate.

Beside deciding to use or not to use NoSql, a helpful article on NOSQL DBMS Comparison and the intention of them could be found here as some of them are focused on high reads, low writes, map-reduce, HA ...
Having a look at the ranking and popularity of them, by category may be usefull.

share|improve this answer
    
Thanks for the details. Will check the links and get back to you. –  Chandu Oct 9 '13 at 23:37

It greatly depends on how you do to get the menus and the nutrients initially. Why do you think it will not be efficient?

From what I understand, you go to the DB, get the menu, then go again, get each recipe, then go again and get each ingredient and so on. This is really inefficient, as there are a lot of queries and round-trips to the server, which is the main source of delays. This is known as the SELECT N+1 problem.

What you should do is to fetch all the data in a single query, by using JOINs for all tables from the menu up to the nutrients, so the DB server can use all the relationships and indexes to get the data all at once. The client C# app only processes and display the final result. Doing so is much more efficient than going one by one.

In general, using proper querying techniques and the right indexes for critical queries, relational databases can perform very well on big tables under load.

share|improve this answer
    
Thanks, I understand that it depends on the joins. Since the constituents of the menu change occasionally I don't want to run the calculation everytime some one hits the page. Instead I want a background service to do the calculation and I can simply read it out of a table when needed. The issue with the calculation is identifying the whole chain when one of the constituent changes. –  Chandu Oct 4 '13 at 16:03
    
Just searching a few relationships doesn't incurs in any calculation at all, even if there are 5 or 6 JOINs that should not be a burden on the server (unless we're talking about fetching hundreds or thousands of rows), if proper indexing is in place. Even with big data sets, you can always build a view on the whole result, and even index the view to have the result precalculated, if performance ever becomes a problem. –  Alejandro Oct 8 '13 at 2:33

I fact you do not need to use a graph db, just store required values in one upper level. It is just like storing an Order and OrderItems. you do not have to calculate total each time an order is about to be displayed. Instead you just calculate sum, vat and other stuff and store them with your Order.

order.Subtotal = order.Items.Sum(item => item.Price);
order.Tax = order.Subtotal * 0.25m; // just a value
order.Total = order.Subtotal + order.Tax;

// fast forward time
var subTotal = order.Items.Sum(item => item.Price);
var tax = subTotal * 0.25m;
var total = subTotal + tax;

if (toal == order.Total) {
   Console.Log("Why the hell I've just re-calculated total?");
}
share|improve this answer

It looks like you've spent some time thinking about how best to model the data so that it can be easily updated and queried. However, now you are at the point where you need to provide access to the data. Those two things are separate concerns.

You mention reloading the page is causing a new query to the database. You also mention that the database will occasionally be updated and when it is you want those updates to be displayed on the page in a timely manner. Your best method to reduce the overhead of queries is not to do them. If you're running the same queries over and over and getting the same results, why not cache them for a while? You should be able to implement some caching upstream without modifying the rest of the project. I would recommend reading about rest. Regardless if you implement the project in an rdbms or nosql problems with performance of this type are best handled by reducing the number of times you have to go to the database. Say you have 100 requests for the same recipe in 60 seconds. If you cache for 60 seconds then you only hit the database once so that's a 100x improvement in performance. To see that same level of improvement by switching to nosql is going to require a lot more work.

Nosql type systems can be a great solution when you have huge amounts of data or extreme read or write speed requirements. However that extra performance comes at a cost of throwing out things like referential integrity.

share|improve this answer

I suggest to look at the Command Query Responsibility Segregation pattern.

Basically instead of create a single model to read and write you can create 2 different models. One optimized for updating and the other optimized for queries (read, reporting, ...). The 2 models are synchronized (usually with eventual consistency) using domain events (see DDD).

I started to study this pattern some months ago and it really changed my way of modelling software. It is not easy because It is a big shift, especially when used with other techniques like DDD and Event Sourcing. But for worth it.

There are many resource available on the net, search for CQRS and DDD (and eventually Event Sourcing).

This pattern can be used on both SQL and noSql.

In your case you can fire an event each time the nutrients are changed to update the read model that is optmized for reading. The read model can be for example a denormalized view of the menu's nutrients (why not using a nosql db for efficient read). You can have multiple read models based on the queries that you need to perform.

There are some implications using this approach but it is very scalable and extensible.

share|improve this answer
    
This was the approach I was contemplating, but was not sure about how to get the data for the read model (basically some process should get me the data for read model). –  Chandu Oct 13 '13 at 20:07
    
Usually the read model is updated at each change. You should implement the ui with commands (task based) instead of using crud operations. In this way every single command is reflected to the read model. You don't need to execute other queries. Designing commands allow the system to capture the real intent of the user. –  Davide Icardi Oct 13 '13 at 20:23

It seems like for the experiment or knowledge purpose you want to try Graph-DB but your example is clearly an example of hierarchical data where we can Drill-Down/Up through a node. I am not expert on Graph/Neo DB yet I can see there are not much complexity in the way user/you can request data from this schema. I see the choice of database/schema design is very depends on how and what type of data will be queried against it. As you using SQLSERVER "HierarchyI"D is the best option from my point of view to put this nodes as part of Tree.

share|improve this answer

My suggestion is to think like a machine and not like a human. It may seems repetitive, but that what machines are good at. One thing that you have to ask yourself is "do I have to retrieve every object, anyway, to display on my page?" If yes, continue what you are doing, compared to data retrieval, cpu cycles are negligible when doing simple math.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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