Hi,
after migrating from NAV 2013 to BC 20.5 (On-Prem), I noticed that some migrated but unchanged codeunits in BC were 5 to 10 times slower than in NAV 2013. Our MS partner then took a look and couldn't find any noticeable optimizations in the code. The query analysis on the SQL server also showed nothing unusual.
After a lot of experimenting I finally found the solution how to get the code back as fast as it ran in NAV 2013. Instead of resetting and reusing recordset variables with reset or clear, I basically used new record variables. Immediately the code ran like a rocket.
In NAV 2013 that didn't cause any problems; in BC, however, the data seems to get stuck in the record variable (cache?) and after about 15,000 to 20,000 data records, the performance drops rapidly.
To illustrate, I'll explain our purpose and code structure. The codeunits walk through the BOM structure, working from low-level-code 0 to 9.
The original code (fast in NAV but slow in BC) is as follows. The code runs normally fast at level 0 (approx. 10,000 articles) and up to the middle of level 1 (approx. 20,000 articles). From about the middle of level 1 it becomes extremely slow. In the end, code that previously took 8 seconds now runs several minutes.
var BOM_Structure: Record tbl_BOM_Structure; LowLevelCode: Integer; // some other vars begin for LowLevelCode := 0 to 9 do begin BOM_Strukture.Reset(); // before every LowLevelCode (also tested with Clear) BOM_Structure.SetCurrentkey(...) // some Code (SetRange / Repeat ... until / modify / ...) end; end;
With the following code I managed to get the old runtime behavior back. Here I used an Array of Record-Variables
var BOM_Structure: Array[10] of Record tbl_BOM_Structure; LowLevelCode: Integer; // some other vars begin for LowLevelCode := 0 to 9 do begin BOM_Structure[LowLewelCode 1].Setcurrentkey(...) // some Code (SetRange / Repeat ... until / modify / ...) end; end;
My own performance problem is solved here. But of course I'm interested in whether this behavior is known and normal. Because then that should actually cause problems in other places and it would be helpful if a reset or clear completely cleans the recordset variable. For example, I have a feeling that the standard "Calculate BOM Levels" code also has the same problem. This runs - with the same database - also at least 5x slower than in NAV 2013.
Greetings
Ralf
Hallo Inge,
I am not an MS partner myself and can therefore not open a support case myself. I received the statement from our MS partner that performance is already a big topic at Microsoft at the moment?!
Incidentally, I agree with you that everything seems to run faster - except when processing larger amounts of data, because I can't always confirm that.
Greetings
Ralf
Thanks for sharing an interesting observation. In general i feel that the performance is improving version by version and in the upgrades i have been involved in i have not seen this kind of behavior.
There can be a number of reasons for it but it is still good that you share these things. I have f.ex. seen code having very different behavior depending on the data it is running on. Especially in large databases when you have indexes that have low variations in their key values.
I am sure Microsoft would like to be noticed about your observation and I would recommend you open a support case with them around this so they can have a look at it as well.
Sohail Ahmed
2,655
Mansi Soni
1,574
YUN ZHU
1,453
Super User 2025 Season 1