Probitas has recently upgraded one of its major clients from a Navision v3.60 Client to v5.0 SP1.  The main advantages we sought were an improvement in performance and reduced database size.  The system has been heavily customised over many years and it was decided not to upgrade the system objects.

The decision was not taken lightly and just upgrading the Client has taken a lot of hard work both in terms of performance testing and modifications to the software.  We compared both the v5.0 Client and v5.0 SP1 against the existing version and also each on different versions of SQL (2000, 2005 and 2008).  Each contains much the same functionality advances over v3.60 but with one fundamental difference – the way in which it handles flowfields.

The initial results were somewhat disturbing, so much so that we, at first, considered abandoning the exercise.  In all tests, the v5.0 Client was slower at reading, writing and deleting regardless of the version of SQL on which it sat.  The v5.0 SP1 Client was considerably quicker at writing to and deleting from the database but, in some cases, diabolically slow at reading.  Had we simply upgraded the database without any changes then the whole exercise would have been a disaster and the business would have ground to a halt – not too clever with 200+ eager users.

Previous versions of Navision achieve the concept of flowfields by the use of Sift Tables.  Each of these, in turn, has attached to it a number of Sift Indexes allowing the flowfield to work quickly even when filtered by a variety of different fields.  The new version has replaced these Sift Indexes with a number of SQL Views, thus removing a huge overhead from the database.  A view is generated, in memory, by the SQL Database and does not need updating.  Hence, write and delete times, for tables that contain flowfields, are dramatically reduced.  However, the detrimental effect on reading can be just as dramatic.  A view still needs to read from the database and, to do this quickly, requires indexes.  Unfortunately, these views now have to rely upon the indexes that exist on the table that provides the data for the flowfield.  The Sift Indexes have all gone so, if the user places a filter on a flowfield for which there is not a suitable key, the System will take an awfully long time to retrieve the result.  By adding the correct keys however, the flowfield may be made to retrieve the required data extremely quickly.  The important thing is to determine which flowfields are slow and under which circumstances their performance needs to be improved.  If you are using Standard V5.0 SP1 objects then Navision have done this for you.  However, if you are upgrading an older version then beware.

I will continue the saga of the upgrade in my next Blog.