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
