Hello Roger,
thanks for your answer. Answers inline.
Roger Binns wrote:
itself. However, I'm afraid that this will not lead to the best possible performance.
If you want the best possible performance then don't use SQLite at all and instead make your own file format that exactly meets your needs and tradeoffs (memory, byte ordering, CPU, concurrency etc).
Well, currently SQLite seems to meet my performance needs. Currently I don't think that brewing my own file format would pay off. I will consider this only as a last solution.
result to the issuer of the command. This approach saves memory, but I'm afraid the the thread switching involved in this design causes too much overhead.
In general the best way to structure applications such as you suggest is to use a queue and have some threads put work on the queues and a pool of threads that execute that work. (The actual work items should be higher level than a single SQL statement).
That makes it easier to split your program such as if you decide to make the backend be a service or over the network, makes threading issues less likely since there will be little shared data, and makes it easy to scale (just add more worker threads on machines with lots more memory and/or CPU).
The higher-level tasks will be performed by various callers in various threads. But in the end, each tasks requires several db commands to be executed, so the db will be the bottleneck; adding more threads and CPUs won't help if I have to serialize all requests into a queue that is processed by a worker thread. The thread switching at some point will limit scalability. The other option - opening the db in each thread - will lead to more memory overhead, also limiting the scalability.
Roger
Best regards
Rolf Schäuble
