Re: OFBIZ-4041: Materialized views
I agree, it's interesting feature that need huge work to ensure no regression and compatibility for major database Nicolas On 26/12/2018 12:30, Jacques Le Roux wrote: Le 11/12/2018 à 12:21, Jacques Le Roux a écrit : Hi, This is a quite interesting feature. Unfortunately the last patch is totally deprecated. I tried to change the paths but it's not enough. There are several chunks that needs to be placed on the right location by hand So the question is: do we really want to drop this effort and forget it for ever? Thanks Jacques It's a pity nobody seems interested, but I can understand why: much work to do by hand ahead. I'll nevertheless consider it in 2019... Cheers Jacques
Re: OFBIZ-4041: Materialized views
Le 11/12/2018 à 12:21, Jacques Le Roux a écrit : Hi, This is a quite interesting feature. Unfortunately the last patch is totally deprecated. I tried to change the paths but it's not enough. There are several chunks that needs to be placed on the right location by hand So the question is: do we really want to drop this effort and forget it for ever? Thanks Jacques It's a pity nobody seems interested, but I can understand why: much work to do by hand ahead. I'll nevertheless consider it in 2019... Cheers Jacques
OFBIZ-4041: Materialized views
Hi, This is a quite interesting feature. Unfortunately the last patch is totally deprecated. I tried to change the paths but it's not enough. There are several chunks that needs to be placed on the right location by hand So the question is: do we really want to drop this effort and forget it for ever? Thanks Jacques
[jira] [Updated] (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Sharan Foga updated OFBIZ-4041: --- Sprint: Bug Crush Event - 21/2/2015 Materialized views -- Key: OFBIZ-4041 URL: https://issues.apache.org/jira/browse/OFBIZ-4041 Project: OFBiz Issue Type: New Feature Components: framework Affects Versions: Trunk Reporter: Marc Morin Assignee: Jacques Le Roux Attachments: OFBIZ-4041-V2.patch, OFBIZ-4041-V2.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 was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13800536#comment-13800536 ] Jacques Le Roux commented on OFBIZ-4041: Hi Daniel, Remember a few days you said :D. Do you think you ever will get a chance to work on it, or should I take over? 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, 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 was sent by Atlassian JIRA (v6.1#6144)
[jira] [Commented] (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13679026#comment-13679026 ] Jacques Le Roux commented on OFBIZ-4041: Hi Daniel, Any chances? 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, 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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13539529#comment-13539529 ] Daniel Riquelme commented on OFBIZ-4041: True, give me a few days and I'll fix it 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, 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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13539570#comment-13539570 ] Jacques Le Roux commented on OFBIZ-4041: No need to hurry, this waits for years ;) 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, 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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13539363#comment-13539363 ] Jacques Le Roux commented on OFBIZ-4041: Mmm, just notice that this does not compile, due tothe visitor part, maybe it misses something... 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, 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
[ 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-V2.patch 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... 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, 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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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] [Updated] (OFBIZ-4041) Materialized views
[ 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] [Comment Edited] (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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] [Updated] (OFBIZ-4041) Materialized views
[ 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] [Updated] (OFBIZ-4041) Materialized views
[ 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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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
[jira] [Updated] (OFBIZ-4041) Materialized views
[ 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] [Assigned] (OFBIZ-4041) Materialized views
[ 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
[jira] [Commented] (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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] Commented: (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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] Commented: (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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] Commented: (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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
[ 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] 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] Commented: (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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] Commented: (OFBIZ-4041) Materialized views
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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 mmo...@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
[ https://issues.apache.org/jira/browse/OFBIZ-4041?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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.
Re: [jira] Commented: (OFBIZ-4041) Materialized views
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-tabpanelfocusedCommentId=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.