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:
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, insert||5 Sec. 828 milliseconds|
|Dictionary, integer key, insert||203 milliseconds|
|Temp Rec, code key, insert||12 Sec. 969 milliseconds|
|Dictionary, code key, insert||2 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, lookup||5 Sec. 952 milliseconds|
|Dictionary, integer key, lookup||406 milliseconds|
|Temp Rec, code key, lookup||11 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:
(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:
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 Customer||22 Sec. 797 milliseconds|
|Random Lookup Temp Table||1 Sec. 313 milliseconds|
|Insert Dictionary in Dictionary||16 Sec. 922 milliseconds|
|Random Lookup Dict/Dict||125 milliseconds|
As we can, there’s a price to pay when creating the double-dictionary, but it’s still faster than the temporary table.
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!