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.
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!
