It’s time for another quick performance tip. During reviews sometimes I come across code like this (table changed to protect the guilty):
ProdTable prodTable;
real total;
;
while select prodTable
{
total += prodTable.QtySched;
}
return total; |
Someone forgot that there’s such a thing as aggregate methods in queries. It’s a lot faster to let SQL Server do the work for you by calculating the sum in a single statement instead of fetching all records and do the calculation in Ax.
When I see something like that I recommend to rewrite it.
ProdTable prodTable;
real total;
;
select sum(QtySched)
from ProdTable;
total = prodTable.QtySched;
return total; |
This is a lot faster. I ran both scenario’s 5000 times for 252 production orders in the USMF demo company on Ax 2012 R3.
Quite a difference, no? Not only is it faster but it’s also more readable because it literally says what it does.
It was actually worse than this because the original code was part of an uncached display method on a table. Can you imagine the effect when it’s used in a grid on a form?
So for the love of insert deity of choice here please use aggregate functions when possible. It’s a low effort, low risk change that can make a noticeable difference.