this is a conceptual question. I’m planning an application that will process millions of records. The data is stored in a SQLite database, which works well. When importing data from a different file format, I create a list from the old keys of the records that have a different format than the common integer value.
So far I do this with the Dictionary, the old keys as key and the SQLite RowID as value.
MyDict.value(oldKey) = RowID
But the RAM increases enormously with such a high number of keys. Paul von Xojo describes again and again in his SQLite webinars that SQLite databases can be used for high speed searches.
The question is, is the memory consumption lower if I use an in-memory database and what is the performance of the app when starting millions of SELECT queries to read the RowID? Anybody experienced with that? My aim is to keep RAM usage as low as possible, as this is needed for other processes.
Dealt with the same question and chose using dictionaries in my project since I am not dealing with such huge amounts of data. @Martin T - in your case I would definitely go for SQLite in memory. Assume using SQLite vs dictionaries will be a little bit slower but the memory will be managed better.
My estimation were that you would have to do some tests. With millions of data, RAM obviously has to be used in numbers, no matter if dictionary or database. There might be some limits as to where the balance between both could shift.
Id be interested to see the results of such a comparison!
Another option could be to use JSONItem instead of a dictionary. I have found it to be a bit faster than dictionary, at least on macOS, and you have the pluspoint of a database like behavior, which means you can easily add more than one value to a child.
Kems https://github.com/ktekinay/JSONItem_MTC class could be worth a test drive too.
Thanks, Joost. You’re probably right. I’ll have to try both. I only noticed that the SQLite database on the hard disk with the same data takes up almost 18 MB, but the Dictionary increased the RAM to 79 MB. Would be to check whether the in-memory database also requires fewer resources.
Thank you Ulrich. That’s a very good idea. I had no idea that JSONItem also has a lookup method. That way, it could be handled like a dictionary.
In short, it’s a free command-line app that runs as a daemon. It provides a self-saving, in-memory dictionary that you communicate with via TCP. Because it’s in-memory, it’s very fast, and because it’s TCP, it can live on another machine.
Using it as a straight dictionary, I can get about 20k requests per second in a local Redis db. If I use “pipelines” (requests are written and received in bulk), I can get over 200k requests per second (reciting those figures from memory).
The advantage here is that any memory used would not be app memory, or even have to be on the same machine.
Thanks Kem for your input. TCP and Redis is nothing I have any experience with. I never heard about it before. I think ill try an In-Memory Database or JSONItem, because I dont want the users to install other software.