In the last years I've seen different old customers having performance problems with the newest version of NAV. Most of them told me: with the old version we didn't have all those problems in performance.
By taking a look at the objects, I discovered that most of the time partners made a porting of their original C/AL code written in a version prior to 2013 into the newest one by making a simple copy-and-paste of their code, without making a review.
Unfortunately, starting from version 2013, some instructions have been rewritten, and the instructions that were optimized for the old version, are not optimized for the newest ones. This means that in a porting project, we should always make a review of the most delicate procedures we have.
Let me give you some examples. When we make a (just for reading purpose) loop over some records, we can use FIND('-') instruction:
or the FINDSET instruction:
Unfortunately, the SQL translation of those AL instructions depends on the version of NAV in which they are executed. Let's have a look.
In NAV 2009 FIND('-') is translated into
SELECT * FROM "Sales Line"...but starting from version 2013, the SQL translation became
SELECT TOP 51 * FROM "Sales Line"...where the parameter 51 depends on cache settings at the server level.
What about FINDSET? We have that in version 2009 this was tranlsated into
SELECT TOP 51 * FROM "Sales Line"...but pay attention, because in version 5.0, this was
SELECT TOP 501 * FROM "Sales Line"...because the default record set was 500 for version prior to 2009 and 50 for version 2009.
For those versions, Microsoft told to set that value at the "average number of sales order lines the customer usually has". This because all the standard procedures reading sales order lines were optimized for reading a subset of records.
Starting from version 2013, FINDSET instruction became
SELECT * FROM "Sales Line"...As you can immediately understand, the code you wrote in 2009, optimized to read a set of records, is no longer optmized for the newest versions.
This means that if you are making a porting of your code you should never copy and paste, but you should always make a review.
Comments