0039824: Slownes loading the Product model using multi tax category module

Description

The query executed to get the product when doing the master data refresh is slow when using multi-tax category module.

Steps To Reproduce

-Ask support for an environment where that issue is reproducible.

Removing the following part of the query the execution time is reduced form 16 second to 1.5 seconds.

       coalesce(
                  (SELECT obfmtc_pro12_.C_Taxcategory_ID
                   FROM OBFMTC_Product_TaxCategory obfmtc_pro12_
                   WHERE obfmtc_pro12_.M_Product_ID=product1_.M_Product_ID
                     AND ad_isorgincluded('B3D3F77C44F64CECB1E650DC61DF77FE', obfmtc_pro12_.AD_Org_ID, product1_.AD_Client_ID) <> -1
                     AND ad_isorgincluded('B3D3F77C44F64CECB1E650DC61DF77FE', obfmtc_pro12_.AD_Org_ID, product1_.AD_Client_ID)=
                       (SELECT min(ad_isorgincluded('B3D3F77C44F64CECB1E650DC61DF77FE', obfmtc_pro13_.AD_Org_ID, product1_.AD_Client_ID))
                        FROM OBFMTC_Product_TaxCategory obfmtc_pro13_
                        WHERE obfmtc_pro13_.M_Product_ID=product1_.M_Product_ID)), product1_.C_TaxCategory_ID) AS col_35_0_,

Proposed Solution

The idea would be to define a new preference for multi tax category module ("Multitax - Filter by store organization"). This preference should be disabled by default. If it is enabled, then the property added should not filter by ad_isorgincluded, but instead should filter by the terminal store organization explicitly. This will make the query much faster in cases in which there is definition of tax category per store instead of per country.

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0108675) hgbot (developer) 2018-12-19 14:03	Repository: erp/pmods/org.openbravo.retail.multitaxcategory Changeset: 27e40f69df9a488ec92960eca1a73c1ca1e2d324 Author: Ranjith S R <ranjith <at> qualiantech.com> Date: Wed Dec 19 18:33:15 2018 +0530 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.multitaxcategory/rev/27e40f69df9a488ec92960eca1a73c1ca1e2d324 [^] Related to issue 39824 : Added Preference to load the Product Tax category by store organization * New Preference 'OBRMTC_FilterByStoreOrganization' added to get product tax category by store organization --- M src/org/openbravo/retail/multitaxcategory/master/TaxCategoryLoadedProductProperties.java M src/org/openbravo/retail/multitaxcategory/master/TaxCategoryProductProperties.java A src-db/database/sourcedata/AD_PREFERENCE.xml A src-db/database/sourcedata/AD_REF_LIST.xml ---

Issue History
Date Modified	Username	Field	Change
2018-12-17 17:51	egoitz	New Issue
2018-12-17 17:51	egoitz	Assigned To	=> Retail
2018-12-17 17:51	egoitz	Resolution time	=> 1546815600
2018-12-17 17:51	egoitz	Regression level	=> Production - Confirmed Stable
2018-12-17 17:51	egoitz	Triggers an Emergency Pack	=> No
2018-12-18 13:59	marvintm	Proposed Solution updated
2018-12-18 15:14	ranjith_qualiantech_com	Assigned To	Retail => ranjith_qualiantech_com
2018-12-18 15:14	ranjith_qualiantech_com	Status	new => scheduled
2018-12-19 14:03	hgbot	Checkin
2018-12-19 14:03	hgbot	Note Added: 0108675
2018-12-20 06:34	ranjith_qualiantech_com	Status	scheduled => resolved
2018-12-20 06:34	ranjith_qualiantech_com	Resolution	open => fixed
2018-12-24 13:17	marvintm	Review Assigned To	=> marvintm
2018-12-24 13:17	marvintm	Status	resolved => closed
2018-12-24 13:17	marvintm	Fixed in Version	=> RR19Q1