Support Article

We are seeing exceptions in Logs complaining about SQL query

SA-20016

Summary



PegaRULES logs show exceptions: One of the join conditions involving the following unoptimized property is not allowed, since it can severely degrade performance: .IndustryCode. Revise the join conditions or optimize the property.


Error Messages



com.pega.pegarules.data.internal.rd.resolve.UserResolveReportException: One of the join conditions involving the following unoptimized property is not allowed, since it can severely degrade performance: .IndustryCode. Revise the join conditions or optimize the property.
at com.pega.pegarules.data.internal.rd.utils.ValidationUtils.throwException(ValidationUtils.java:485)
at com.pega.pegarules.data.internal.rd.resolve.FiltersResolver.validateFilter(FiltersResolver.java:290)
at com.pega.pegarules.data.internal.rd.resolve.FiltersResolver.resolveFilter(FiltersResolver.java:104)
at com.pega.pegarules.data.internal.rd.resolve.FiltersResolver.resolveFilters(FiltersResolver.java:82)
at com.pega.pegarules.data.internal.rd.resolve.JoinsResolver.resolveJoins(JoinsResolver.java:90)
at com.pega.pegarules.data.internal.rd.resolve.JoinsResolver.resolve(JoinsResolver.java:62)
at com.pega.pegarules.data.internal.rd.resolve.SqlReportResolver.resolve(SqlReportResolver.java:82)
at com.pega.pegarules.data.internal.rd.resolve.SqlReportResolver.resolve(SqlReportResolver.java:36)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.resolve(SqlReportExecutor.java:208)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.executeMainReport(SqlReportExecutor.java:111)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:90)
at com.pega.pegarules.data.internal.rd.SqlReportExecutor.execute(SqlReportExecutor.java:57)
at com.pega.pegarules.data.internal.PRDataProviderImpl.executeReport(PRDataProviderImpl.java:1229)
at com.pega.pegarules.session.internal.mgmt.Executable.executeReport(Executable.java:10787)
at com.pegarules.generated.definition.ra_action_businessunitdata_caaa7e4b06ff914e6eece4b1000f3b75.perform(ra_action_businessunitdata_caaa7e4b06ff914e6eece4b1000f3b75.java:50)
at com.pega.pegarules.session.internal.mgmt.Executable.doAction(Executable.java:3305)
at com.pegarules.generated.activity.ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.step30_circum0(ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.java:3411)
at com.pegarules.generated.activity.ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.perform(ra_action_pxretrievereportdata_ea2365ec0714372ba5ea7e36c543cc9a.java:637)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10521)
at com.pegarules.generated.activity.ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.step4_circum0(ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.java:373)
at com.pegarules.generated.activity.ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.perform(ra_action_pxcallretrievereportdata_ac7c5191bd54e9e32ee884ac93fa0a8e.java:123)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pega.pegarules.exec.internal.declare.infengine.pages.DeclarativePageDirectoryImpl.runRule(DeclarativePageDirectoryImpl.java:994)
at com.pega.pegarules.exec.internal.declare.infengine.pages.DeclarativePageDirectoryImpl.runLoadActivity(DeclarativePageDirectoryImpl.java:452)
at com.pega.pegarules.session.internal.mgmt.base.handler.EditableDataPageHandler.loadDataPage(EditableDataPageHandler.java:130)
at com.pega.pegarules.session.internal.mgmt.base.handler.EditableDataPageHandler.findDataPage(EditableDataPageHandler.java:295)
at com.pega.pegarules.session.internal.mgmt.base.AbstractPageDirectory.findDataPageInCurrentDir(AbstractPageDirectory.java:920)
at com.pega.pegarules.session.internal.mgmt.base.AbstractPageDirectory.getDirectPage(AbstractPageDirectory.java:713)
at com.pega.pegarules.session.internal.mgmt.base.ThreadPageDir.getDirectPage(ThreadPageDir.java:473)
at com.pega.pegarules.session.internal.mgmt.base.ThreadPageDir.getPage(ThreadPageDir.java:431)
at com.pega.pegarules.session.internal.mgmt.PRThreadImpl.getPage(PRThreadImpl.java:539)
at com.pega.pegarules.session.internal.mgmt.Executable.findPage(Executable.java:2081)
at com.pega.pegarules.session.internal.mgmt.Executable.findPage(Executable.java:2034)
at com.pega.pegarules.session.internal.mgmt.Executable.findPageWithException(Executable.java:7242)
at com.pegarules.generated.activity.ra_action_appsetnamesforbu_fefada2271aefbf1813e1507cfa9d300.perform(ra_action_appsetnamesforbu_fefada2271aefbf1813e1507cfa9d300.java:99)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3500)
at com.pega.pegarules.session.internal.mgmt.Executable.invokeActivity(Executable.java:10521)
at com.pegarules.generated.activity.ra_action_appmapaccountownerpartydetails_57dd44ad36ae8ed0e88cbdd75c1cfa23.step3_circum0(ra_action_appmapaccountownerpartydetails_57dd44ad36ae8ed0e88cbdd75c1cfa23.java:639)


Steps to Reproduce



None


Root Cause



A defect or configuration issue in the operating environment:

Configuration of the manually added database tables that are specific to Pega Customer Service was not correct. The IndustryCode property is referenced in the class join filter condition of report definition 
PegaCA-Interface-BusinessUnit.BusinessUnitData. The join condition checks for equality between PegaCA-Interface-BusinessUnit.IndustryCode and PegaCA-Interface-Industry.IndustryCode. The latter property was not correctly mapped to an exposed column in the custom database table created during the installation of Customer Service framework .

Resolution



The IndustryCode property is mapped to an exposed column in the pa_industry table in the sample database that is shipped with Customer Service framework. The property needs to be mapped to an exposed column in the custom table that is created by the developer for the actual installation.

 

Suggest Edit

Published February 18, 2016 - Updated February 22, 2016

Did you find this content helpful?

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.