Skip to main content
Community site session details

Community site session details

Session Id :
Small and medium business | Business Central, N...
Suggested answer

Performance boost - by using new record variables instead of Reset or Clear

(0) ShareShare
ReportReport
Posted on by 144

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

  • neckit Profile Picture
    144 on at
    RE: Performance boost - by using new record variables instead of Reset or Clear

    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

  • Suggested answer
    Inge M. Bruvik Profile Picture
    1,029 Moderator on at
    RE: Performance boost - by using new record variables instead of Reset or Clear

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Now Available: 2025 Release Wave 2

Quick Links

Ramesh Kumar – Community Spotlight

We are honored to recognize Ramesh Kumar as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
Sohail Ahmed Profile Picture

Sohail Ahmed 2,655

#2
Mansi Soni Profile Picture

Mansi Soni 1,574

#3
YUN ZHU Profile Picture

YUN ZHU 1,453 Super User 2025 Season 1

Featured topics

Product updates

Dynamics 365 release plans