How To use a progressbar in Dynamics NAV?

Progressbars are very interesting tool for the programmer to show the user a progress of a task.
BUT most programmers don’t know how bad those progressbars can be for performance.


To know how many records there are to process, you have to put your filters on the table and then do a COUNT on it. If you have a good key/index with perfectly fitting filters on it, you have no problems. But if this is not the case, you can have a performance issue for the database. The COUNT has to scan the records the DB will read and after that, he will read them (again!) to process them. So the database reads the records twice possibly scanning the whole table twice. To avoid this problem (a little) on SQL, the COUNTAPPROX has been invented. But this does not count the records exactly. It is based on the SQL-statistics. But these can be very wrong, even if you do a regular calculate statistics on SQL.

In short: if you don’t have a key/index that matches very well the filters, it is better to avoid progressbars and just update a dialogbox with some counter of records already processed. The user will not know how much time he has to wait to finish the task, but at least he sees the server is working and he will be content anyway (but not so much as with a progressbar). And if you explain the user he has to wait (much) longer if he wants a nice progressbar, most users accept a dialogbox with a counter.

Now EVEN if we have a perfect fitting filter+key/index, it can still become a performance issue if you implement your dialogbox-updates in a wrong way to update the progressbar.

IMPORTANT: the dialog.UPDATE-command is very time-consuming. It means that Windows has to refresh the screen (or part of it) and this takes a lot of time. You don’t notice it because we are talking about milliseconds, but if you do it 1.000.000 times, you WILL notice it (see the bad example).

Lets take an iteration on an integer-field (a better fitting filter+key/index for speed is not possible because we are not even reading records!).
We iterate from 1 to 1.000.000 (yes: 1 million iterations!).
If you do it in a good way, it costs less then 2 seconds (depending on the CPU in your PC and its utilization). If you do it in a bad way … well … you can get a coffee and get back to check if your PC has finished the job.

Lets see the code of a badly implemented progressbar:

intProgressI := 0;
diaProgress.OPEN(
  'A good advice : click  :-)\' + 
  'The idea is : too many updates of the dialogbox can hinder performance.\' +
  '@1@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\',intProgress);
intProgressTotal := 1000000;
timProgress := TIME;
REPEAT
  // Progress bar
  intProgressI := intProgressI + 1;
  intProgress := ROUND(intProgressI / intProgressTotal * 10000,1);
  diaProgress.UPDATE;
  // some processing
UNTIL intProgressI = intProgressTotal;
diaProgress.CLOSE;

“intProgressI” is the variable that is counts the iteration.
“intProgressTotal” is the variable which holds the total number of iterations to be made.
“intProgress” is the variable that goes from 1 to 10000. This number is needed by the progressbar to show a percentage.
What is wrong here?
We do an UPDATE of the dialogbox on each iteration. We need to do fewer updates of the dialogbox to speed it all up.

Lets now take a look at a correctly implemented progressbar:

intProgressI := 0;
diaProgress.OPEN('@1@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\',intProgress);
intProgressTotal := 1000000;
timProgress := TIME;
REPEAT
  // Progress bar
  intProgressI := intProgressI + 1;
  IF timProgress < TIME - 1000 THEN BEGIN // every second
    timProgress := TIME;
    intProgress := ROUND(intProgressI / intProgressTotal * 10000,1);
    diaProgress.UPDATE;
  END;
  // some processing
UNTIL intProgressI = intProgressTotal;

What is different here?
We still have “intProgressI” that increases on each iteration.
BUT we use the time to check if a certain amount of time (in this case 1 second) has passed BEFORE doing another UPDATE of the dialogbox. Also the calculation of “intProgress” is done at this moment. It is not needed to calculate this all the time but only when we need it to update the dialogbox.

IMPORTANT: even if you use a counter in stead of a progressbar, don’t update your dialogbox too often, but still do it every second.