Project:
| View Revisions: Issue #42269 | [ Back to Issue ] | ||
| Summary | 0042269: [Performance] ProductCharacteristicValue master data performance improvement | ||
| Revision | 2019-11-13 13:12 by ioritzCia | ||
| Description | The query generated by the ProductCharacteristicValue master data refresh either incremental or full can be improved in performance. Filtering by characteri1_.em_obpos_useonwebpos = 'Y' makes the planner to build a less efficient plan. To take into account: - Oracle: An in clause with more than 1000 elements is not possible in Oracle, a subselect would, but a subselect does not improve the plan. It would be quite strange to have more than 20 characteristics as em_obpos_useonwebpos = 'Y', even more to have more than 1000. - To check if in environments with not many characteristics this does not make it perform worse. If it leaves it the same but improves cases with many characteristics it would be a nice solution to include. |
||
| Revision | 2019-11-13 13:05 by ioritzCia | ||
| Description | The query generated by the ProductCharacteristicValue master data refresh either incremental or full can be improved in performance. Filtering by characteri1_.em_obpos_useonwebpos = 'Y' makes the planner to build a less efficient plan. To take into account: - Oracle: An in clause with more than 1000 elements is not possible in Oracle, a subselect would, but a subselect does not improve the plan. It would be quite strange to have more than 20 characteristics as em_obpos_useonwebpos = 'Y', even more to have more than 1000. |
||
| Revision | 2019-11-13 13:05 by ioritzCia | ||
| Steps To Reproduce | Set log min duration statement of the environment accordingly (typically < 200ms) to be able to see both executions in log. Log in WebPOS and check for the execution of the query Original query example: https://pastebin.com/16RFjMdi [^] Changed query example: https://pastebin.com/8ZPUFBwk [^] Plan before the change: https://explain.depesz.com/s/IO5k [^] Plan after the change: https://explain.depesz.com/s/sps9 [^] |
||
| Revision | 2019-11-13 13:05 by ioritzCia | ||
| Description | The query generated by the ProductCharacteristicValue master data refresh either incremental or full can be improved in performance. Filtering by characteri1_.em_obpos_useonwebpos = 'Y' makes the planner to build a less efficient plan. To have into account: - Oracle: An in clause with more than 1000 elements is not possible in Oracle, a subselect would, but a subselect does not improve the plan. It would be quite strange to have more than 20 characteristics as em_obpos_useonwebpos = 'Y', even more to have more than 1000. |
||
| Revision | 2019-11-13 12:59 by ioritzCia | ||
| Steps To Reproduce | Log in WebPOS and check for the execution of the query Original query example: https://pastebin.com/16RFjMdi [^] Plan before the change: https://explain.depesz.com/s/IO5k [^] Plan after the change: https://explain.depesz.com/s/sps9 [^] |
||
| Revision | 2019-11-13 12:59 by ioritzCia | ||
| Description | The query generated by the ProductCharacteristicValue master data refresh either incremental or full can be improved in performance. | ||
| Copyright © 2000 - 2009 MantisBT Group |
 |