You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by "Marc Morin (JIRA)" <ji...@apache.org> on 2010/12/03 18:31:10 UTC
[jira] Created: (OFBIZ-4041) Materialized views
Materialized views
------------------
Key: OFBIZ-4041
URL: https://issues.apache.org/jira/browse/OFBIZ-4041
Project: OFBiz
Issue Type: New Feature
Components: framework
Affects Versions: Release Branch 10.04
Reporter: Marc Morin
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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Updated] (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jacques Le Roux updated OFBIZ-4041:
-----------------------------------
Attachment: OFBIZ-4041.patch
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
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch, OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Adam Heath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12969490#action_12969490 ]
Adam Heath commented on OFBIZ-4041:
-----------------------------------
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.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Updated] (OFBIZ-4041) Materialized views
Posted by "Daniel Riquelme (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Daniel Riquelme updated OFBIZ-4041:
-----------------------------------
Attachment: OFBIZ-4041-V2.patch
Adapted to rev: 1387076
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch, OFBIZ-4041-V2.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (OFBIZ-4041) Materialized views
Posted by "Marc Morin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Marc Morin updated OFBIZ-4041:
------------------------------
Attachment: OFBIZ-4041.patch
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.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Commented] (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13128488#comment-13128488 ]
Jacques Le Roux commented on OFBIZ-4041:
----------------------------------------
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
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jacques Le Roux updated OFBIZ-4041:
-----------------------------------
Attachment: (was: OFBIZ-4041.patch)
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13458478#comment-13458478 ]
Jacques Le Roux commented on OFBIZ-4041:
----------------------------------------
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 :)
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch, OFBIZ-4041-V2.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Marc Morin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12966673#action_12966673 ]
Marc Morin commented on OFBIZ-4041:
-----------------------------------
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 -----
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Adam Heath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12969419#action_12969419 ]
Adam Heath commented on OFBIZ-4041:
-----------------------------------
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.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Scott Gray (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12969373#action_12969373 ]
Scott Gray commented on OFBIZ-4041:
-----------------------------------
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.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Comment Edited] (OFBIZ-4041) Materialized views
Posted by "Daniel Riquelme (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13458316#comment-13458316 ]
Daniel Riquelme edited comment on OFBIZ-4041 at 9/19/12 11:19 AM:
------------------------------------------------------------------
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.
was (Author: driquelme):
Adapted to rev: 1387076
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch, OFBIZ-4041-V2.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Marc Morin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12969573#action_12969573 ]
Marc Morin commented on OFBIZ-4041:
-----------------------------------
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.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Updated] (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (Updated) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jacques Le Roux updated OFBIZ-4041:
-----------------------------------
Affects Version/s: (was: Release Branch 10.04)
SVN trunk
New features applie only on trunk
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch, OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Marc Morin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12969414#action_12969414 ]
Marc Morin commented on OFBIZ-4041:
-----------------------------------
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.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Scott Gray (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12969444#action_12969444 ]
Scott Gray commented on OFBIZ-4041:
-----------------------------------
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.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Commented] (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (Commented) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13253818#comment-13253818 ]
Jacques Le Roux commented on OFBIZ-4041:
----------------------------------------
Mmm, I removed this last patch. It seems that there are some chunks which were rejected and I did not notice.
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (OFBIZ-4041) Materialized views
Posted by Marc Morin <ma...@emforium.com>.
Ok, I will try to spend some time this weekend in order to pull a patch together for this.
----- Original Message -----
> [
> https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12966681#action_12966681
> ]
>
> Jacques Le Roux commented on OFBIZ-4041:
> ----------------------------------------
>
> I'm interested...
>
> > Materialized views
> > ------------------
> >
> > Key: OFBIZ-4041
> > URL:
> > https://issues.apache.org/jira/browse/OFBIZ-4041
> > Project: OFBiz
> > Issue Type: New Feature
> > Components: framework
> > Affects Versions: Release Branch 10.04
> > Reporter: Marc Morin
> >
> > 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.
>
> -- This message is automatically generated by JIRA.
> - You can reply to this email to add a comment to the issue online.
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12966681#action_12966681 ]
Jacques Le Roux commented on OFBIZ-4041:
----------------------------------------
I'm interested...
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OFBIZ-4041) Materialized views
Posted by "Adam Heath (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12966615#action_12966615 ]
Adam Heath commented on OFBIZ-4041:
-----------------------------------
Is there a patch for this?
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] [Assigned] (OFBIZ-4041) Materialized views
Posted by "Jacques Le Roux (Assigned) (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jacques Le Roux reassigned OFBIZ-4041:
--------------------------------------
Assignee: Jacques Le Roux
> Materialized views
> ------------------
>
> Key: OFBIZ-4041
> URL: https://issues.apache.org/jira/browse/OFBIZ-4041
> Project: OFBiz
> Issue Type: New Feature
> Components: framework
> Affects Versions: Release Branch 10.04
> Reporter: Marc Morin
> Assignee: Jacques Le Roux
> Attachments: OFBIZ-4041.patch
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira