[tehlike]

This is very interesting. N+1 and lazy loading have been a very common problem that profilers can spot, but eager loading also has a cartesian product problem where if you have an an entity with 6 sub item, and 100 of another subitem, you'll end up getting 600 rows to construct a single object / view model.

I have been recently playing with RavenDB (from my all time favorite engineer turned CEO), it approaches most of these as an indexing problem in the database, where the view models are calculated offline as part of indexing pipeline. It approaches the problem from a very pragmatic angle. It's goal is to be a database that is very application centric.

Still to be seen if we will end up adopting, but it'll be interesting to play with.

Disclaimer: I am a former NHibernate contributor, and have been very intimate with AR features and other pitfalls.

[balfirevic]

Didn't NHibernate have the cartesian product problem solved in a neat way by having various fetch strategies?

You could specify to eagerly load some collections and have NHibernate issue additional select statement to load the children, producing maximum of 2-3 queries (depending on the eager-loading depth) but avoiding both N+1 problem and cartesian row explosion problem.

[tehlike]

yes, that's the common method, but you still end up issuing multiple network calls. The problem wit issuing select statements to load the children is you have to wait on the first query (root) to finish so you can issue others which adds to the network latency (usually low, but it also depends). It's still not as good as having materialized viewmodels on server where you can issue a single query to get everything you need. The disadvantage is the storage cost, though.

[balfirevic]

I went and looked at the docs to refresh my memory - there was also a subquery fetch strategy where you didn't have to wait for the root entity to load, but that comes at the expense of searching through data twice - which might or might not be worth it, depending on how complicated the query is.

I do wish relational databases (PostgreSQL and SQL Server specifically, since I work with those) had better support for automatically updated real-time materialized views.

Anyway, thanks for working on NHibernate - I miss some of it's configurability and advanced capabilities.

[jacques_chester]

> I do wish relational databases (PostgreSQL and SQL Server specifically, since I work with those) had better support for automatically updated real-time materialized views.

I've been keeping an eye on these folks: https://materialize.io/

[tehlike]

Automatically updated materialized views are something I really want too.

Take a look at ravendb, it might be a good thing to try on next smallish project that you can move to a later one :)

Postgres has a nice advantage of supporting json, so in theory you could have embedded documents as materialized views and whatnot, but it's hard to make it play nice with orms.