Hougaard.com – Applied Hacking

What’s faster than temporary tables?

I’m currently working on a new app (for AppSource) and I need fast accessible information throughout a user session that I can look up without having a performance impact on the user experience.

Great, we have the single instance codeunit, and temporary tables. That’s an old and proven solution.

But wait, the information I need to store is quite simple, not really something that I need a “whole table” for. And AL has the Dictionary data type.

Dictionary is one of the new additions to AL, straight from DotNet, and at least in DotNet, it’s blazing fast.

A dictionary is a has table lookup with values, and is defined like this:

Two examples of Dictionaries

We better test this, let’s run some performance tests on those two dictionaries. In order to have something to compare with, I also created two temporary tables, Standard Text is chosen because it’s a very simple table with only two fields:

I created some simple test code for all four examples. Inserting 1 million records in each of them, and measuring the time for each group.

Running this for all four types gives a significant speed advantage to the Dictionary:

Temp Rec, integer key, insert5 Sec. 828 milliseconds
Dictionary, integer key, insert203 milliseconds
Temp Rec, code key, insert12 Sec. 969 milliseconds
Dictionary, code key, insert2 Sec. 265 milliseconds

But how about looking up data? So I create another set of test functions like this. Performing a million random lookups:

Again, the dictionary is again smoking the temporary tables:

Temp Rec, integer key, lookup5 Sec. 952 milliseconds
Dictionary, integer key, lookup406 milliseconds
Temp Rec, code key, lookup11 Sec. 672 milliseconds
Dictionary, code key, lookup 2 Sec. 234 milliseconds

The first conclusion is clear, dictionaries are way faster than temporary tables with a 5x – 10x factor.

But, we cannot create a dictionary of records:

A dictionary of Records … No good πŸ™

(The reason for this, is that Record is a magic type in AL. A Record is both the values from a single record, the filters view of multiple records, and a “cursor” at the same time).

But we can create a dictionary of dictionaries:

Dictionary of dictionaries πŸ™‚

To populate this dictionary, we need to create the inner dictionary first and then add that to the main dictionary.

And the results for this: (only for 100000 records, I got bored of waiting for a million):

Insert Temp Table Customer22 Sec. 797 milliseconds
Random Lookup Temp Table1 Sec. 313 milliseconds
Insert Dictionary in Dictionary16 Sec. 922 milliseconds
Random Lookup Dict/Dict125 milliseconds

As we can, there’s a price to pay when creating the double-dictionary, but it’s still faster than the temporary table.

Conclusion?

It’s certainly faster, especially if the information you need to hold in memory is simple, but even with the more complicated dictionary-in-dictionary, it’ still faster than the temporary table.

One of the reasons for this is because the temporary table behaves like a table, with all the “overhead” a table has. But a dictionary is a very basic structure that can store data and retrieve it very quickly.

For structures like a “has-seen” table used under processing of data, or fast lookups, dictionaries can be the answer.

It’s yet another great tool in your Business Central toolbox!

Print Friendly, PDF & Email

1 thought on “What’s faster than temporary tables?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.