Hi -
I have a report that produces the data correctly the problem is the execution time, it takes a long time to generate the report. look at the screen shot below for my process report method.
while select * from inventValueTransUnionAllParent
join * from inventDimParent
group by InventLocationId,InventSiteId ASC
where inventValueTransUnionAllParent.inventDimId == inventDimParent.inventDimId
&& inventDimParent.InventLocationId != ''
&& inventDimParent.InventLocationId != 'N/A'
{
ttsBegin;
MTRA_InventoryValueReportTmp.InventoryFinancialAmount = this.populateFinancialAmount(fromDate,toDate,inventDimParent.InventLocationId);
// MTRA_InventoryValueReportTmp.InventoryFinancialQty = this.populateFinancialQty(fromDate,toDate,inventDimParent.InventLocationId);
// MTRA_InventoryValueReportTmp.InventoryPhysicalPostedAmount = this.populatePhysicalAmount(fromDate,toDate,inventDimParent.InventLocationId);
// MTRA_InventoryValueReportTmp.InventoryPhysicalPostedQty = this.populatePhysicallQty(fromDate,toDate,inventDimParent.InventLocationId);
MTRA_InventoryValueReportTmp.InventLocationId = inventDimParent.InventLocationId;
MTRA_InventoryValueReportTmp.InventSiteId = inventDimParent.InventSiteId;
MTRA_InventoryValueReportTmp.insert();
ttsCommit;
}
Now for the individual methods which populated the tmp table buffer.
public CostAmount populateFinancialAmount(FromDate _fromDate, ToDate _toDate, InventLocationId _InventLocationId)
{
CostAmount Amounts = 0;
while select * from inventValueTransUnionAll
where inventValueTransUnionAll.TransDate >= _fromDate
&& inventValueTransUnionAll.TransDate <= _toDate
&& (inventValueTransUnionAll.unionAllBranchId == 6)
join * from inventdim
where inventValueTransUnionAll.inventDimId == inventdim.inventDimId
&& inventDim.InventLocationId == _InventLocationId
{
{
Amounts = Amounts + inventValueTransUnionAll.Amount;
}
}
while select * from inventValueTransUnionAll
where inventValueTransUnionAll.TransDate >= _fromDate
&& inventValueTransUnionAll.TransDate <= _toDate
&& (inventValueTransUnionAll.unionAllBranchId == 5)
join * from inventDim
where inventValueTransUnionAll.inventDimId == inventDim.inventDimId
&& inventDim.InventLocationId == _InventLocationId
{
{
Amounts = Amounts + inventValueTransUnionAll.Amount;
}
}// working query
return Amounts;
}
What changes I can make in my query/ Methods to optimize the report results ?
Any suggestions will be really helpfull
Thanks
*This post is locked for comments
I have the same question (0)
