0

This provides a simple example of select n + 1 problem in ORM in pht, but the idea should be the same in other ORM of other languages.

A typical solution is to use eager loading to reduce it to 1 query, which I don't think is very hard, but, OTOH, it's quite brittle, because latter refactoring would require the developers to change two places, otherwise this select n + 1 problem re-occurs.

My intuition is that it should be possible to do some path analysis to figure it out that those children attributes would be used in later part of the code, so let's generate the query to collect all the necessary info in one go.

This is probably quite too much to ask for ORM implemented using interpreted langs, such as ruby, php, etc. However, neither Java (NHibernate) nor C# (Entity framework) is doing this path analysis, AFAIK. Why is this?

Albert Netymk
  • 1,102
  • 8
  • 24

1 Answers1

0

i don't understand, if you use EntityFramework, you can use Include(....) to specify which lazily loadable child relationtship entities to load in the same query, and if you specify a clause that take into account some field on that included entity, you get a single request (in few words, just use joins in your linq query)

btw here you can find an old and related question like your: What is SELECT N+1?

as i commented below, a way could be to have a method that you call to add all your needed Include, so that when you add a new navigable property/collection you have just to remember to add there, this answer shows you the concept: https://stackoverflow.com/a/14520939/1716620

Community
  • 1
  • 1
Not Important
  • 762
  • 6
  • 22
  • Yes, I can use `Include` to solve the select n+1 problem, but whatever I put in `Include` must be consistent with the usage below. For example, in later refactoring, I added one more item in the view, but forget to update the `Include`, I would suffer from this select n+1 problem again. I understand this select n+1 concept, but I am curious why it couldn't be solved automatically by the compiler. – Albert Netymk Dec 07 '15 at 22:58
  • compiler can't know how you want to handle your entities, it's up to you to specify which kind of related entities load eager or lazy whenever you create a linq query, you are just creating an IQuearable rapresentation of the query, and it's not solved at compile time but at runtime, compiler just check if the generic implementation constraint are satisfied but only at runtime the query get solved and translated to SQL or whatever idiom it need to be converted anyway you could implement your logic to append includes before enumerating your IQuerable (so before generating SQL) – Not Important Dec 07 '15 at 23:06
  • That would require the compiler to be aware of the ORM. It's possible you might be able to do something like what you are thinking of using Roslyn in C# though. – Bradford Dillon Dec 07 '15 at 23:07
  • i'd add that if you find yourself in the position of having always to use Include, maybe it's a candidate to be eager loaded – Not Important Dec 07 '15 at 23:09
  • updated the answer with another link with an example about the mentioned "implement your logic to append includes before enumerating your IQuerable" : http://stackoverflow.com/a/14520939/1716620 – Not Important Dec 07 '15 at 23:12