Is it still useful to buffer records in a temptable?

Maybe some of you remember my how-to increase performance by reducing db reads.

I wrote it in 2007 when we still had the native DB and also the SQL DB but NOT the service tier with all its extra’s for performance.

Basically it improves the reading speed of this kind of code:






UNTIL SalesLine.NEXT = 0;

Meaning for each SalesLine-record you go back to the DB and ask it again for the item even if you already received that same item thousands of time. You should NOT harass the database like that with those puny requests. He has better things to do!

But now in 2016 with NAV2016 out in the wild (I should have tested this when NAV2013 came out but …….… No comment please……), I was wondering if my trick was still valid or not.

Considering that now all code is run on the service tier instead of on the client and the service tier has its own cache shared between all its connected clients, I thought that it might have become useless. Maybe it even hinders performance because of the extra overhead. So (finally) time to put it to the test.

So I created a codeunit to run different tests and run it multiple times. The following is a typical output (for my machine and data [not a CRONUS DB]):

Reread worst-case:0.651 seconds

Reread random-case:0.634 seconds

Reread best-case:0.589 seconds

Construct-TT worst-case:4.857 seconds

Construct-TT random-case:3.108 seconds

Construct-TT best-case:0.317 seconds

-“Reread” means I always do a ItemREC.GET() even if the record-variable has already the correct item record.

-“Construct-TT” means I have a function GetItem where I put some code that constructs a temptable on the fly with the items it had to get from the database so that the next read it I could just get it from the temptable (or in the best case I already have it in the variable).

-“Worst-case” means that every time the item-record I have last read is different from the one I need now.

-“Best-Case” means that most of the time the item-record I have last read is the same as the one I need now.

-“Random-case” means I do some pseudo-random access with a high number of cases that the last read item-record is the same as the one I need now.

So what does these numbers tell us?

In the reread-scenario’s it seems that the performance doesn’t change a lot. This is what I expected because once a record has been read, a reread does not result in a query to SQL Server but the record gets taken from the service tier cache (or maybe the C# code behind it sees that it already is in the variable and does not even get it from the cache. If this is not the case, it might be an extra optimization for Microsoft to implement). So this is good!

In the Construct-TT scenario’s we see big differences. In the worst-case and random-case now perform (a lot) worse than on the classic client. The best-case is better than the reread-scenarios but only by half. The “Construct-TT worst-case” and the “Construct-TT random-case” perform a lot worse than the reread=scenario’s.


CONCLUSION: While looping your sales lines, just do a GET of your item for each sales line. The only improvement you might introduce is { IF ItemREC.”No.” <> SalesLineREC.”No.” THEN ItemREC.GET(SalesLineREC.”No.”). } You might get some better performance than with just a { ItemREC.GET(SalesLineREC.”No.”) }. I didn’t check it but I don’t think it will be that much.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s