Details

    • Type: New Feature New Feature
    • Status: Patch Available
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: Trunk
    • Fix Version/s: None
    • Component/s: framework
    • Labels:
      None

      Description

      We make extensive use of view entities in our ofbiz application. We have noticed that when there is a large dataset and under some complex views, the query performance was not the best (not a index issue, just complex joins, etc...).

      With some commercial databases like Oracle, etc... we would have used materialized view semantics available for these dbms, but we are using PostgreSQL.

      So, we have extended the entity layer in Ofbiz to perform the materialization. This is pretty slick as all you need to do is the following:

      <view-entity name="myView" materialize="true">...</view-entity>

      and the system will do the following:

      • create a backing entity called "myView" that has the same fields as the view
      • backing entity has all the indexes inherited from the component entities
      • relations (fk,...) inherited from the component entities.
      • perform all the ECA actions automatically on all entities used in the view (direct members and nested members if case of view on views). (This is an eager update strategy only).

      So, the application doesn't change, it still accesses "myView", but now, it's result is returned from the backing entity instead of the complex SQL statement.

      We're pretty excited about this feature!!! Really pushes Ofbiz framework to next level and allows materialized views to be more broadly used on dbms that don't naturally support it.

      We are prepared to contribute this feature back to the community if desired. A note of caution about it though.... we have added a visitor pattern to the model entities and this feature makes use of it. It would need to come with it.

      1. OFBIZ-4041.patch
        125 kB
        Marc Morin
      2. OFBIZ-4041-V2.patch
        88 kB
        Daniel Riquelme
      3. OFBIZ-4041-V2.patch
        137 kB
        Jacques Le Roux

        Activity

        Hide
        Jacques Le Roux added a comment -

        Hi Daniel,

        Remember "a few days" you said . Do you think you ever will get a chance to work on it, or should I take over?

        Show
        Jacques Le Roux added a comment - Hi Daniel, Remember "a few days" you said . Do you think you ever will get a chance to work on it, or should I take over?
        Hide
        Jacques Le Roux added a comment -

        Hi Daniel,

        Any chances?

        Show
        Jacques Le Roux added a comment - Hi Daniel, Any chances?
        Hide
        Jacques Le Roux added a comment -

        No need to hurry, this waits for years

        Show
        Jacques Le Roux added a comment - No need to hurry, this waits for years
        Hide
        Daniel Riquelme added a comment -

        True, give me a few days and I'll fix it

        Show
        Daniel Riquelme added a comment - True, give me a few days and I'll fix it
        Hide
        Jacques Le Roux added a comment -

        Mmm, just notice that this does not compile, due tothe visitor part, maybe it misses something...

        Show
        Jacques Le Roux added a comment - Mmm, just notice that this does not compile, due tothe visitor part, maybe it misses something...
        Jacques Le Roux made changes -
        Attachment OFBIZ-4041-V2.patch [ 12562271 ]
        Hide
        Jacques Le Roux added a comment -

        Hi Daniel,

        I just did a cursory review, and noticed you missed the new files in your patch. I added them and also replaced all tabs by 4 spaces

        I attach an update of your patch. It must be revisited, I will see that again later...

        Show
        Jacques Le Roux added a comment - Hi Daniel, I just did a cursory review, and noticed you missed the new files in your patch. I added them and also replaced all tabs by 4 spaces I attach an update of your patch. It must be revisited, I will see that again later...
        Hide
        Jacques Le Roux added a comment -

        Thanks Daniel,

        I think it's a very interesting feature, I wish I would have more time to work in it. This will come one day

        Show
        Jacques Le Roux added a comment - Thanks Daniel, I think it's a very interesting feature, I wish I would have more time to work in it. This will come one day
        Daniel Riquelme made changes -
        Attachment OFBIZ-4041-V2.patch [ 12545646 ]
        Hide
        Daniel Riquelme added a comment - - edited

        Uploaded new Patch version adapted to rev: 1387076.-

        This patch hasn't been tested thoroughly, the only test I made so far was to change a Party entity and verified that the Materialized View changed.
        A few things are missing/pending:

        1) Insert entity data to new Materialized View upon creation
        2) Re build Materialized View upon entities modification.

        Show
        Daniel Riquelme added a comment - - edited Uploaded new Patch version adapted to rev: 1387076.- This patch hasn't been tested thoroughly, the only test I made so far was to change a Party entity and verified that the Materialized View changed. A few things are missing/pending: 1) Insert entity data to new Materialized View upon creation 2) Re build Materialized View upon entities modification.
        Hide
        Jacques Le Roux added a comment -

        Mmm, I removed this last patch. It seems that there are some chunks which were rejected and I did not notice.

        Show
        Jacques Le Roux added a comment - Mmm, I removed this last patch. It seems that there are some chunks which were rejected and I did not notice.
        Jacques Le Roux made changes -
        Attachment OFBIZ-4041.patch [ 12522630 ]
        Jacques Le Roux made changes -
        Affects Version/s SVN trunk [ 12311928 ]
        Affects Version/s Release Branch 10.04 [ 12314832 ]
        Hide
        Jacques Le Roux added a comment -

        New features applie only on trunk

        Show
        Jacques Le Roux added a comment - New features applie only on trunk
        Jacques Le Roux made changes -
        Attachment OFBIZ-4041.patch [ 12522630 ]
        Hide
        Jacques Le Roux added a comment -

        This is an interesting piece of code.

        Here is a patch update which applies to trunk. I have replaced all tabs and removed some problematic chunks

        Show
        Jacques Le Roux added a comment - This is an interesting piece of code. Here is a patch update which applies to trunk. I have replaced all tabs and removed some problematic chunks
        Jacques Le Roux made changes -
        Status Open [ 1 ] Patch Available [ 10002 ]
        Jacques Le Roux made changes -
        Assignee Jacques Le Roux [ jacques.le.roux ]
        Hide
        Jacques Le Roux added a comment -

        Mmm... Sorry it's a long time since I had a look at this issue (not reviewed the patch yet). I think it's one of the most interesting things which have been proposed "recently". I will try to focus on this.. in the next weeks... or months (rather large piece of code). Of course all help is welcome

        Show
        Jacques Le Roux added a comment - Mmm... Sorry it's a long time since I had a look at this issue (not reviewed the patch yet). I think it's one of the most interesting things which have been proposed "recently". I will try to focus on this.. in the next weeks... or months (rather large piece of code). Of course all help is welcome
        Hide
        Marc Morin added a comment -

        Adam, are you commenting on the materialized view problem vs the bulk operations? Either way, both use the same logic of cascading possible operations to dependent model items (either in the cache) or materlalized views. If you look at the patch, the materialized views use the EXACT same ModelConversion information that the cache uses to propage, but instead of clearing values, it clears and rebuilds.

        Show
        Marc Morin added a comment - Adam, are you commenting on the materialized view problem vs the bulk operations? Either way, both use the same logic of cascading possible operations to dependent model items (either in the cache) or materlalized views. If you look at the patch, the materialized views use the EXACT same ModelConversion information that the cache uses to propage, but instead of clearing values, it clears and rebuilds.
        Hide
        Adam Heath added a comment -

        The view caching system I wrote has support for that kind of access; a map of values can have a 'partial' match on cached view result lists, which then up causing the cached result to be cleared. This seems to be the same kind of thing.

        Show
        Adam Heath added a comment - The view caching system I wrote has support for that kind of access; a map of values can have a 'partial' match on cached view result lists, which then up causing the cached result to be cleared. This seems to be the same kind of thing.
        Hide
        Scott Gray added a comment -

        Hi Adam,

        Perhaps you misunderstand, when I say "bulk" what I mean is that a single SQL query affects multiple rows in the database. There is no way to hook ECAs into this type of query, the only solution would be to perform a select and then update/remove each row individually.

        Show
        Scott Gray added a comment - Hi Adam, Perhaps you misunderstand, when I say "bulk" what I mean is that a single SQL query affects multiple rows in the database. There is no way to hook ECAs into this type of query, the only solution would be to perform a select and then update/remove each row individually.
        Hide
        Adam Heath added a comment -

        Hmm. There are some things in the delegator that are direct api calls. findOne, delee, update. Then there are other things that are used to reduce code in the applications; the so-called bulk operations. This latter set of code should not have any ecas connected to them, ever. Instead, the bulk operations should just call the lower-level methods, which then would have the ecas attached to them.

        Maybe the bulk operations should be moved to a DelegatorHelper or Worker class.

        Show
        Adam Heath added a comment - Hmm. There are some things in the delegator that are direct api calls. findOne, delee, update. Then there are other things that are used to reduce code in the applications; the so-called bulk operations. This latter set of code should not have any ecas connected to them, ever. Instead, the bulk operations should just call the lower-level methods, which then would have the ecas attached to them. Maybe the bulk operations should be moved to a DelegatorHelper or Worker class.
        Hide
        Marc Morin added a comment -

        Good point Scott. We have "removed" the bulk operations in our implementation for specifically that reason... We've noticed that even though these are bulk operations, they don't appear to be from an API point of view. There isn't much to help guide the developer that using these API's will circumvent all ECA activity....

        We had thought about only doing the bulk operation iff there are no ECAs defined for the entity and operation defined. Otherwise, leave it as bulk. But that too would appear to be dangerous since there are unknown side affects of not running the ECA.

        Not sure what the best/good compromise is for the bulk vs normal operation is. Just seems incorrect to bypass the ECA. Not that "disabling" the eca via the entityengine.xml also causes the materialized views to not always maintaining it's results.

        Show
        Marc Morin added a comment - Good point Scott. We have "removed" the bulk operations in our implementation for specifically that reason... We've noticed that even though these are bulk operations, they don't appear to be from an API point of view. There isn't much to help guide the developer that using these API's will circumvent all ECA activity.... We had thought about only doing the bulk operation iff there are no ECAs defined for the entity and operation defined. Otherwise, leave it as bulk. But that too would appear to be dangerous since there are unknown side affects of not running the ECA. Not sure what the best/good compromise is for the bulk vs normal operation is. Just seems incorrect to bypass the ECA. Not that "disabling" the eca via the entityengine.xml also causes the materialized views to not always maintaining it's results.
        Hide
        Scott Gray added a comment -

        It's worth noting that GenericDelegator.storeByCondition(...) and GenericDelegator.removeByCondition(...) currently do not trigger ECAs because they are bulk operations. Just mentioning it because the ramifications could be a bit more significant for a feature such as ECA-dependent materialized views.

        Show
        Scott Gray added a comment - It's worth noting that GenericDelegator.storeByCondition(...) and GenericDelegator.removeByCondition(...) currently do not trigger ECAs because they are bulk operations. Just mentioning it because the ramifications could be a bit more significant for a feature such as ECA-dependent materialized views.
        Marc Morin made changes -
        Field Original Value New Value
        Attachment OFBIZ-4041.patch [ 12465614 ]
        Hide
        Marc Morin added a comment -

        Patch for implementation of Materialized views. This implementation used a visitor pattern, on the ModelEntity (and related classes) so they are part of the patch. Also, since entities are created by code, as opposed to by reading XML files, a number of getters and setters were added to the model objects.

        A new class, ModelMaterializedEntity is created when a view is read having materialized="true" property. It creates an ECA for all the dependent entities used by the view.

        There is NO checking on the usefulness or expense of the materialized views. It should also only be used on views where the pkey of the underlying entity is exposed as a field in the view, since this makes the ECA more efficient.

        CheckDb will rebuild all materialized views since there is no way currently in OFBIZ to detect that the view's definition has changed. In our implementation, we record an MD5 of the view as a comment on the table.... thereby detecting if we need to rebuild. That wasn't included at this time.

        No ECA to prevent store/remove on the backing entity.

        Show
        Marc Morin added a comment - Patch for implementation of Materialized views. This implementation used a visitor pattern, on the ModelEntity (and related classes) so they are part of the patch. Also, since entities are created by code, as opposed to by reading XML files, a number of getters and setters were added to the model objects. A new class, ModelMaterializedEntity is created when a view is read having materialized="true" property. It creates an ECA for all the dependent entities used by the view. There is NO checking on the usefulness or expense of the materialized views. It should also only be used on views where the pkey of the underlying entity is exposed as a field in the view, since this makes the ECA more efficient. CheckDb will rebuild all materialized views since there is no way currently in OFBIZ to detect that the view's definition has changed. In our implementation, we record an MD5 of the view as a comment on the table.... thereby detecting if we need to rebuild. That wasn't included at this time. No ECA to prevent store/remove on the backing entity.
        Hide
        Jacques Le Roux added a comment -

        I'm interested...

        Show
        Jacques Le Roux added a comment - I'm interested...
        Hide
        Marc Morin added a comment -

        Not yet, I would need to pull it out of our OFbiz source tree... which is about a year old and co-mingled with a lot of other framework goodies...

        I am prepared to do the work though if the community wants the feature.

        Marc Morin
        Emforium Group Inc.
        ALL-IN Software
        519-772-6824 ext 201
        mmorin@emforium.com

        ----- Original Message -----

        Show
        Marc Morin added a comment - Not yet, I would need to pull it out of our OFbiz source tree... which is about a year old and co-mingled with a lot of other framework goodies... I am prepared to do the work though if the community wants the feature. Marc Morin Emforium Group Inc. ALL-IN Software 519-772-6824 ext 201 mmorin@emforium.com ----- Original Message -----
        Hide
        Adam Heath added a comment -

        Is there a patch for this?

        Show
        Adam Heath added a comment - Is there a patch for this?
        Marc Morin created issue -

          People

          • Assignee:
            Jacques Le Roux
            Reporter:
            Marc Morin
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:

              Development