Display method / While Select

Finance and Operations/AX Developer Forum
1

I know that we should not be using While select statements in a display method(on a form).
But I have a requirement where I need to use a while select, but as we know it is causing serious performance issues.

Any alternatives?

Code Snippet:

select InventSiteId, InventLocationId from inventDim
where inventDim.inventDimId == this.InventDimId;

while select InventDimId from inventDimSelection
where inventDimSelection.InventSiteId == inventDim.InventSiteId
&& inventDimSelection.InventLocationId == inventDim.InventLocationId
{

select sum(AvailPhysical) from inventSum
where inventSum.ItemId == this.ItemId
&& inventSum.InventDimId == inventDimSelection.InventDimId;

sumPhy+= inventSum.AvailPhysical;
}

return sumPhy;

I am selecting the location/site based on the InventDimId and then using those to fetch all the records from InventDim and eventually InventSum.
the reason behind all the above code and not just using the below code:

select sum(AvailPhysical) from inventSum

where inventSum.ItemId == this.ItemId

&& inventSum.InventDimId == this.InventDimId;

Because, there can be too many InventDimId’s for a single site/warehouse combination.

Any help would be appreciated.

  • Sourabh
2

I didn’t get your argument about why you have to use a while select. You can rewrite to something like this, can’t you?

select sum(AvailPhysical) from inventSum
where inventSum.ItemId == this.ItemId

join inventDimSelection
where inventDimSelection.InventDimId = inventSum.InventDimId
   && inventDim.InventSiteId == inventDimSelection.InventSiteId
   && inventDim.InventLocationId == inventDimSelection.InventLocationId

join inventDim
where inventDim.inventDimId == this.InventDimId;

It’s much more efficient because it makes just a single call to database and fetches a single field.

It it still takes too much time to calculate, it’s waste of resource calculating it for every view (e.g. calculating the same thing individually for every user looking at the form). You could store the calculated values in database and refresh them less often (based on a schedule, changes in InventSum or so).