How do I optimize this OQL query for latest status History for each MPDI

0
I am using this same query for 3 tabs with 3 different statuses and whenever I open the page it takes time to load. I want to optimize this query. ''SELECT MPDI/UniqueIdentifier AS UniqueID, MPDI/TargetPayrollDate as TargetDate, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_MemberAccountTier/InServiceOutbound.MemberAccountTier/InServiceOutbound.MemberAccountTier_MemberAccount/MPDIRIntegration.MemberAccount/MembershipNumber as Membership_Number, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_DeductionInstruction/MasterData.DeductionInstruction/DeductionInstructionCode AS DI_Code, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_PersonEmployer/MasterData.Organization/OrganizationCode as OrgName, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_CycleSchedule/MasterData.CycleSchedule/MasterData.CycleSchedule_EPSPayrollCycleForCyclecode/MasterData.EPSPayrollCycle/MasterData.EPSPayrollCycle_EPSCycle/MasterData.EPSCycle/CycleCode as Bank, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_DeductionInstructionAdminOfficeWorkLocation/MasterData.DeductionInstructionAdminOfficeWorkLocation/MasterData.DeductionInstructionAdminOfficeWorkLocation_WorkLocation/MasterData.Organization/OrganizationCode AS OrgNameAdmin, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_MemberAccountTier/InServiceOutbound.MemberAccountTier/InServiceOutbound.MemberAccountTier_PlanTier/MasterData.PlanTier/PLAN_TIER as Plan_Tier, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_PersonEmployer/MasterData.Organization/OrganizationCode as Employer, MPDI/PersonJobTitleCode AS Job_ID, MPDI/JobSequenceNumber AS Job_Sequence, MPDI/DeductionInstructionType AS DI_Type, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_EPSCycleEmployerDepartment/MasterData.EPSCycleEmployerDepartment/DepartmentCode AS Work_Location, MPDI/DeductionPercentage AS Rate, MPDI/AnnualGoalAmount AS Goal_Amount, MPDI/InstallmentCount AS InstallmentCount, MPDI/DeductionAmount AS InstallmentAmount, MPDI/OtherRetirementFlag as OtherRetirementFlag, MPDI/InServiceOutbound.MemberPayrollDeductionInstruction_ServiceRequestTransaction/SRM.ServiceRequestTransaction/TransactionNumber AS SRTransaction, MPDI/MPDIExtract.MemberPayrollDeductionInstructionExtractionDetail_MemberPayrollDeductionInstruction/MPDIExtract.MemberPayrollDeductionInstructionExtractionDetail/MPDIExtract.MemberPayrollDeductionInstructionExtractionDetail_MemberPayrollDeductionInstructionExtraction/MPDIExtract.MemberPayrollDeductionInstructionExtraction/ExtractionDate AS ExtractionDateFROM InServiceOutbound.MemberPayrollDeductionInstruction AS MPDI WHERE EXISTS ( SELECT 1 FROM InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory AS StatusHist WHERE StatusHist/InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory_MemberPayrollDeductionInstruction = MPDI.ID AND StatusHist/DeductionInstructionStatus = ''PDTR'' AND StatusHist/Id = ( SELECT InnerHist/Id FROM InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory AS InnerHist WHERE InnerHist/InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory_MemberPayrollDeductionInstruction = MPDI.ID ORDER BY InnerHist/StatusEffectiveDate DESC, InnerHist/createdDate DESC LIMIT 1 ) ) ORDER BY UniqueID DESC''
asked
1 answers
2

Hi Asha Misal


I analyzed this and can guide you step by step process


Step1:Create a new OQL DataSet in Mendix that returns only the latest StatusHistory record per MPDI. Name it something like DS_LatestStatusPerMPDI

SELECT
  StatusHist/InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory_MemberPayrollDeductionInstruction AS MPDI_ID,
  StatusHist/DeductionInstructionStatus AS LatestStatus,
  StatusHist/StatusEffectiveDate AS LatestEffectiveDate
FROM InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory AS StatusHist
WHERE StatusHist/Id = (
  SELECT InnerHist/Id
  FROM InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory AS InnerHist
  WHERE InnerHist/InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory_MemberPayrollDeductionInstruction
        = StatusHist/InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory_MemberPayrollDeductionInstruction
  ORDER BY InnerHist/StatusEffectiveDate DESC, InnerHist/createdDate DESC
  LIMIT 1
)

This isolates the expensive "find the latest status" logic into one dedicated dataset. Step2: Now replace your original WHERE EXISTS with a direct JOIN to the result above:


SELECT
  MPDI/UniqueIdentifier AS UniqueID,
  MPDI/TargetPayrollDate AS TargetDate,
  -- ... all your existing columns ...
  LatestStatus/DeductionInstructionStatus AS CurrentStatus

FROM InServiceOutbound.MemberPayrollDeductionInstruction AS MPDI

JOIN InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory AS LatestStatus
  ON LatestStatus/InServiceOutbound.MemberPayrollDeductionInstructionStatusHistory_MemberPayrollDeductionInstruction
     = MPDI/Id

WHERE LatestStatus/DeductionInstructionStatus = $StatusParameter

ORDER BY UniqueID DESC

Replace $StatusParameter with your status value ('PDTR' or whichever status each tab uses).


Step3: Instead of hardcoding 'PDTR' in three separate queries, pass the status as a parameter into the DataSet.

In your page's microflow or data source

Tab 1 → call query with StatusParameter = 'PDTR'

Tab 2 → call query with StatusParameter = 'XXX'

Tab 3 → call query with StatusParameter = 'YYY' Or even You can retrive all data and filter using nanoflow but i wont suggest this. and more over try to add indexes also for faster.


I hope this helps!!

answered