This document is internal case study only and the outcome can be vary depending on the DBMS type and the record counts and/or the way Chart of Account (COA) is defined.
Currently P09210A (Trial Balance / Ledger Comparison) takes more than 2 hours to return value when user review old records (for instance, Aug-20-2014) but when user enter Sept-05-2014 it performs acceptable performance (e.g., less than 5 min). How to overcome this issue?
Customer specific data count:-
Change code of TrialBalanceCalculateBalances (B0900090, Trial Balance Calculate Balances) to have SELECT statement as below,
Issue is verified by issuing below query through database query tool as below,
: From the first query though data are to be filtered by PN (Period Number) it goes to full table scan.
Note:
To add additional parameter in JDB_SetSelection() try this,
/* Tested code */
ID I_D0900090_F0911_Balance_Calculate(LPDS_D0900090_F0911_DB_INFO lpdsF0911DBInfo,
LPDS_D0900090_F0911_KEYS lpdsF0911Keys,
LPDS_D0900090_COMMON_VALUES lpdsCommonValues,
LPDS_D0900090_CALCULATED_AMOUNTS lpdsCalculatedAmounts,
LPDSD0900090 lpDS)
{
/***************************************************************************
* Variable declarations
***************************************************************************/
ID idJDBReturn = JDEDB_PASSED;
int i= 0; /* Counter */
/***************************************************************************
* Declare structures
***************************************************************************/
DS_D0900090_COLUMN_F0911 dsColumnsF0911;
SELECTSTRUCT Select9[11] = {0};
SELECTSTRUCT Select10[12] = {0};
SELECTSTRUCT Select11[9] = {0};
SELECTSTRUCT Select13[8] = {0};
SELECTSTRUCT Select14[9] = {0}; /* Add additional Select structuer */
/***************************************************************************
* Declare pointers
***************************************************************************/
LPDS_D0900090_COLUMN_F0911 lpdsColumnsF0911;
/***************************************************************************
* Set pointers
***************************************************************************/
lpdsColumnsF0911 = &dsColumnsF0911;
/***************************************************************************
* Initializations
***************************************************************************/
memset((void *)(&dsColumnsF0911), (int)(_J('\0')), sizeof(DS_D0900090_COLUMN_F0911));
/***************************************************************************
* Main Processing
***************************************************************************/
/* Based on rollup method, select records from the Account Ledger Table
(F0911) matching the correct key */
switch(lpdsCommonValues->cAccountBalanceRollupMethod)
{
/* Break In Code */
case _J('N'):
if (lpdsCommonValues->cProcessingFirstHalfOfPeriodFlag == _J('Y')) /* No change for this condition */
{
/* Set Selection Parameters For Key 13 */
jdeNIDcpy(Select13[i].Item1.szDict, NID_POST);
jdeNIDcpy(Select13[i].Item1.szTable, NID_F0911);
Select13[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glpost;
Select13[i].nValues = 1;
Select13[i].nAndOr = JDEDB_ANDOR_AND;
Select13[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select13[i].Item1.szDict, NID_AID);
jdeNIDcpy(Select13[i].Item1.szTable, NID_F0911);
Select13[i].lpValue = lpdsF0911Keys->dsKey13F0911.glaid;
Select13[i].nValues = 1;
Select13[i].nAndOr = JDEDB_ANDOR_AND;
Select13[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select13[i].Item1.szDict, NID_LT);
jdeNIDcpy(Select13[i].Item1.szTable, NID_F0911);
Select13[i].lpValue = lpdsF0911Keys->dsKey13F0911.gllt;
Select13[i].nValues = 1;
Select13[i].nAndOr = JDEDB_ANDOR_AND;
Select13[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select13[i].Item1.szDict, NID_CTRY);
jdeNIDcpy(Select13[i].Item1.szTable, NID_F0911);
Select13[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glctry;
Select13[i].nValues = 1;
Select13[i].nAndOr = JDEDB_ANDOR_AND;
Select13[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select13[i].Item1.szDict, NID_FY);
jdeNIDcpy(Select13[i].Item1.szTable, NID_F0911);
Select13[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glfy;
Select13[i].nValues = 1;
Select13[i].nAndOr = JDEDB_ANDOR_AND;
Select13[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select13[i].Item1.szDict, NID_PN);
jdeNIDcpy(Select13[i].Item1.szTable, NID_F0911);
Select13[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glpn;
Select13[i].nValues = 1;
Select13[i].nAndOr = JDEDB_ANDOR_AND;
Select13[i].nCmp = JDEDB_CMP_EQ;
i++;
/* dmseo - test on 27 Oct 2014 - to restrict value range */
jdeTraceSz1(NULL, _J("Select13 Line 2665 %ls"),_J("jdPeriodEndDate1"));
/* lpdsF0911Keys->dsKey13F0911.gldgj = lpDS->jdPeriodEndDate1; */
jdeNIDcpy(Select13[i].Item1.szDict, NID_DGJ);
jdeNIDcpy(Select13[i].Item1.szTable, NID_F0911);
/* Select13[i].lpValue = &lpdsF0911Keys->dsKey13F0911.gldgj; */
Select13[i].lpValue = &lpDS->jdThruDate1;
Select13[i].nValues = 1;
Select13[i].nAndOr = JDEDB_ANDOR_AND;
Select13[i].nCmp = JDEDB_CMP_LE;
i++;
JDB_SetSelection(lpdsF0911DBInfo->hRequestF0911, (LPSELECT)Select13,
(ushort)i, JDEDB_SET_REPLACE);
}
else
{ /* Add additional parameter and change data SELECT data structure */
/* Set Selection Parameters For Key 14 */
jdeNIDcpy(Select14[i].Item1.szDict, NID_POST);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
Select14[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glpost;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select14[i].Item1.szDict, NID_AID);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
Select14[i].lpValue = lpdsF0911Keys->dsKey13F0911.glaid;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select14[i].Item1.szDict, NID_LT);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
Select14[i].lpValue = lpdsF0911Keys->dsKey13F0911.gllt;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select14[i].Item1.szDict, NID_CTRY);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
Select14[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glctry;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select14[i].Item1.szDict, NID_FY);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
Select14[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glfy;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_EQ;
i++;
jdeNIDcpy(Select14[i].Item1.szDict, NID_PN);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
Select14[i].lpValue = &lpdsF0911Keys->dsKey13F0911.glpn;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_EQ;
i++;
/* lpdsF0911Keys->dsKey13F0911.gldgj = lpDS->jdPeriodBeginDate1; */
jdeNIDcpy(Select14[i].Item1.szDict, NID_DGJ);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
/* Select13[i].lpValue = &lpdsF0911Keys->dsKey13F0911.gldgj; */
Select14[i].lpValue = &lpDS->jdThruDate1;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_GT; /* Set Comparison GT (Greater Than) */
i++;
/* to restrict value range */
/* lpdsF0911Keys->dsKey13F0911.gldgj = lpDS->jdPeriodEndDate1; */
jdeNIDcpy(Select14[i].Item1.szDict, NID_DGJ);
jdeNIDcpy(Select14[i].Item1.szTable, NID_F0911);
/* Select13[i].lpValue = &lpdsF0911Keys->dsKey13F0911.gldgj; */
Select14[i].lpValue = &lpDS->jdPeriodEndDate1;
Select14[i].nValues = 1;
Select14[i].nAndOr = JDEDB_ANDOR_AND;
Select14[i].nCmp = JDEDB_CMP_LE;
i++;
JDB_SetSelection(lpdsF0911DBInfo->hRequestF0911, (LPSELECT)Select14,
(ushort)i, JDEDB_SET_REPLACE);
}
}
/* End of Code */