Simulating Indexed/Materialized Views

[Mihai Codrean]

Hi,

Can NHibernate be used to simulate indexed/materialized views, by
using a physical table that it maintains updated?
One reason would be to overcome the current limitations of indexed
views in SQL Server, like the unsupported "OUTER JOIN". My guess is
that this is not rocket-science, given the notification support in
NHibernate, and also considering existing implementations, like the
following, although not for SQL Server:

http://www.fromdual.com/mysql-materialized-views
http://code.google.com/p/flexviews/
http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
http://www.pgcon.org/2008/schedule/events/69.en.html

Anyone interested in such a feature?

Thanks,
-Mihai

PS: I originally posted this over one year ago on "NHibernate Users"
at forum.hibernate.org, but got no reply so far, so I'm hoping this
will not be considered cross-posting.

[Gunnar Liljas]

Anyone interested in such a feature?

Absolutely. It's something I've tinkered with as well. I found two approaches:

1. Using the event listeners. Quite complex, but certainly doable. The advantage is that the materialized data can be stored in any form, such as a NoSQL store. Deferring the materialization, e.g using a message queue, could be a good idea.

2. Using NHibernate to generate the fundamental SQL parts needed to create standard T-SQL triggers. It may sound crude and not very DB agnostic, but I believe it's actually a useful approach. The triggers can be created at runtime, or as part of a build/migration script.

/G

[Mihai Codrean]

> Absolutely. It's something I've tinkered with as well.

All right, so I'm not alone! :) I would add to your points above that
the "view" definition could be expressed as a DetachedCriteria, and
that the event listeners will monitor for the corresponding entities.
Thoughts?

Thanks,
-Mihai

[Mihai Codrean]

> the "view" definition could be expressed as a DetachedCriteria [...]
Or HQL, I guess.

Thoughts?

[Gunnar Liljas]

A DetachedCriteria with a projection would certainly work, and even HQL is possible, but certainly harder. The important part is to be able to inspect the definition to detect which entity updates/inserts/deletes will trigger which materialization routines.

Do you have an example of a projection/materialization you would like to have?

/G

2011/8/5 Mihai Codrean <mihaic...@gmail.com>

> the "view" definition could be expressed as a DetachedCriteria [...]
Or HQL, I guess.

Thoughts?

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Mihai Codrean]

> The important part is to be able to inspect the definition to detect which entity updates/inserts/deletes will trigger which materialization routines.

Right.

> Do you have an example of a projection/materialization you would like to have?

I want the world, but how about starting with the most simple case,
say updates to the entities participating in an inner-join, and then
see about outer-joins?

-Mihai

[Mihai Codrean]

The corresponding PKs for the entities participating in the "view"
definition will have to be part of the "view" as well, right?

[Gunnar Liljas]

Yes! Otherwise we'd be looking at massively inefficient rematerializations. 

/G

2011/8/5 Mihai Codrean <mihaic...@gmail.com>

The corresponding PKs for the entities participating in the "view"

definition will have to be part of the "view" as well, right?

[Mihai Codrean]

Correct. Do you know if there are, say, public guest branches that
could be used to collaborate on this? That is, instead of passing
patching back and forth.

[Gunnar Liljas]

Set up a repository on Github. I can do it also, if you want.

/G

2011/8/5 Mihai Codrean <mihaic...@gmail.com>

Correct. Do you know if there are, say, public guest branches that

could be used to collaborate on this? That is, instead of passing
patching back and forth.

--

[Mihai Codrean]

Here it is: https://github.com/mihaicodrean/NHibernate.IndexedViews
What's your username on github?

I will write a small test early next week so that we have something
tangible to discuss/work on.

[Gunnar Liljas]

Continuing off list.

2011/8/6 Mihai Codrean <mihaic...@gmail.com>

--

[Mihai Codrean]

> A DetachedCriteria with a projection would certainly work, and even HQL is possible, but certainly harder.

While I have started with DetachedCriteria, I just recalled that is
has no support for mass operations (AFAIK), as opposed to HQL. So I
think HQL is the way to go. Thoughts?

Details here: http://ayende.com/blog/4037/nhibernate-executable-dml

[Gunnar Liljas]

That is what lead me toward the "create trigger" option. I'm just not sure that the NHibernate DML (HQL) is strong enough, but it's certainly worth a try.

The DetachedCriteria/HQL can be used in the definition of the materialization, but not in the updating.

/G

2011/8/10 Mihai Codrean <mihaic...@gmail.com>

--

[Mihai Codrean]

Well, we can always aim to be DB server agnostic, can't we? :) (HQL
all the way)

[Mihai Codrean]

Here's a stumbling point. Let's assume:

• natively generated IDs;
  
• a simple model like Author, Book & AuthorBook;
• a normalization table called AuthorBookDenormalized, to store all the fields of the "inner join" between the three tables above;
• an author instance, a book instance & the author-book association entities are created & saved.
  

When using an interceptor, an "insert from select" could be issued whenever any of the monitored entities are getting saved. This could work, as the "inner join" will only produce the new row when all the entities have been persisted. However, the problem is that this can't be done from inside the OnSave interceptor method, as there are no IDs at that point. The AfterTransactionCompletion is a valid location, but at that point *all* the three entities above are already persisted, and thus, issuing an "insert from select" for each of them would create duplicates in the denormalized table. There could be a "select" before the "insert from select", to ensure no duplicates get created, but that's not so good performance-wise.

Any idea how to overcome this?

[Mihai Codrean]

Gunnar, any ideas on the issue above?

[Mihai Codrean]

Maybe it will work (i.e. no duplicates), at least for the "inner join" case, if the insert filter will be built using all the corresponding saved entities, instead of issuing an insert for each and every saved entity. I'll make some tests, and if this makes sense, I'll commit.

[Mihai Codrean]

Committed the work in progress. Any feedback is highly appreciated.