Question
LANIT-BPM
RU
Last activity: 16 Jul 2016 5:47 EDT
Declarative Index join in report definition
Hi,
I have the situation as following: several declarative index use the same database table to write the data in (pr_index_operators), the report definition in Data-Admin-Operator-ID class which is joined on one of this index with option "Include all rows in this class". I expect that all records from Data-Admin-Operator-ID will be joined on records of the joined index only ("select from pr_index_operators" will be filtered by pxIndexPurpose), but it's not what happens. All records from pr_index_operators are used in this case. Is this a bug or this is designed to be so?
Thanks in advance.
Message was edited by: Lochan to add Category
Pegasystems
IN
Please share the screenshot of how the report definition has been configured along with the query generated when this report definition is executed.
Also, please state the version of Pega platform you are using.
LANIT-BPM
RU
Rajiv,
It's configured like this: http://joxi.ru/12M7RPbI4Jj4Yr , 1465979755123 (240 kb) закаÑан 15 иÑÐ½Ñ 2016 г. Joxi .
SQL:
Rajiv,
It's configured like this: http://joxi.ru/12M7RPbI4Jj4Yr , 1465979755123 (240 kb) закаÑан 15 иÑÐ½Ñ 2016 г. Joxi .
SQL:
SELECT "PC0".PYUSERIDENTIFIER AS "pyUserIdentifier" , "PC0".PYUSERNAME AS "pyUserName" , "PC0".PZINSKEY AS "pzInsKey" FROM SBCIBR_DEV.pr_operators "PC0" LEFT OUTER JOIN SBCIBR_DEV.pr_index_operators "OperatorData" ON ( ( "PC0".PZINSKEY = "OperatorData".PXINSINDEXEDKEY ) AND "OperatorData".PXOBJCLASS = ? AND "PC0".PXOBJCLASS = ? ) WHERE ( "OperatorData".LOADFACTOR <= ? OR "OperatorData".LOADFACTOR IS NULL AND "PC0".PYACCESSGROUP = ? ) AND "PC0".pxObjClass = ? |
|
SELECT "PC0".PYUSERIDENTIFIER AS "pyUserIdentifier" , "PC0".PYUSERNAME AS "pyUserName" , "PC0".PZINSKEY AS "pzInsKey" FROM SBCIBR_DEV.pr_operators "PC0" LEFT OUTER JOIN SBCIBR_DEV.pr_index_operators "OperatorData" ON ( ( "PC0".PZINSKEY = "OperatorData".PXINSINDEXEDKEY ) AND "OperatorData".PXOBJCLASS = ? AND "PC0".PXOBJCLASS = ? ) WHERE ( "OperatorData".LOADFACTOR <= ? OR "OperatorData".LOADFACTOR IS NULL AND "PC0".PYACCESSGROUP = ? ) AND "PC0".pxObjClass = ? |
It's Pega 7.1.8.
Pegasystems
IN
I have the situation as following: several declarative index use the same database table to write the data in (pr_index_operators), the report definition in Data-Admin-Operator-ID class which is joined on one of this index with option "Include all rows in this class".
So there are different declare indices but they map to the same Index- class which stores the data in pr_index_operators?
We never use the pxIndexPurpose to filter the records. Filtering is done by putting in the pxObjClass filter to the Index- class which is used by the declare index that is referenced in the report definition. In this case, OperatorData.
LANIT-BPM
RU
So there are different declare indices but they map to the same Index- class which stores the data in pr_index_operators?
Yes, correct. And in this case pxObjClass is the same for different indexes's records.
Is this a bad design then to write multiple indexes to the same table?
Pegasystems
IN
I don't think it is a bad design. The assumption was as follows
We will only 1 declare index mapping to an Index- class for a given work class
The above is satisfied because
1) pxObjClass is part of the ON condition for the Index- class
2) The main class pzInsKey is equated to the pxIndexedInsKey making sure that only instances of the current class' index entries are joined
In your scenario, it looks like there are multiple declare indices, which map to the same index table using the same Index- class for different list / group properties under the same work class. This would require the pxIndexPurpose condition for the join, which we never apply. This needs to be an enhancement.
For the moment, you will need to change the class names so that the filtering occurs correctly.
Cognizant Technology Solutions Pvt Ltd
NL
Hi Rajiv,
We have a similar situation where we are joining our work table with an index table with "include all rows in this class" option. The query generated in RD for us is as below:
SELECT "PC0".CUSTOMERLEGALNAME AS "CustomerLegalName" , "PC0".SERVICECENTREREQUESTTYPE AS "ServiceCentreRequestType" , "PC0".LOANAGREEMENTPRODUCTID AS "LoanAgreementProductId" , "PC0".PXCREATEDATETIME AS "pxCreateDateTime" , "PC0".PXUPDATEDATETIME AS "pxUpdateDateTime" , "PC0".PYSLADEADLINE AS "pySLADeadline" , "PC0".PYSTATUSWORK AS "pyStatusWork" , "PC0".PYID AS "pyID" , "PC0".BANKCODE AS "BankCode" , "IndxObj".LOANNUMBER AS "LoanNumber" , "PC0".PZINSKEY AS "pzInsKey" FROM OWNER_OA2.pc_RBG_RN_Financing_Work_a8174 "PC0" LEFT OUTER JOIN OWNER_OA2.PR_INDEX_SEARCHLOANNUMBER "IndxObj" ON ( ( "PC0".PZINSKEY = "IndxObj".PXINSINDEXEDKEY ) AND "IndxObj".PXOBJCLASS = ? AND "PC0".PXOBJCLASS IN (? , ? , ? , ? , ? )) WHERE ( ( "PC0".PYSTATUSWORK LIKE ? OR "PC0".PYSTATUSWORK = ? ) AND "PC0".PXOBJCLASS IN (? , ? , ? , ? , ? ) ) ORDER BY 5 DESC
We were having huge number of records in our DB and this RD is taking around 3 min to run in PROD environment. If we remove the part which is bolded and run the same query, the query was getting executed in 5-6 seconds. Could you please let us know why we are checking for pxObjClass?