Which database system?

[Chris Angelico]

On Sat, Sep 16, 2017 at 4:04 AM, Stefan Ram <ram at zedat.fu-berlin.de> wrote:
>   When one is building an in-memory database that has a single
>   table that is built at the start of the program and then one
>   writes some complex queries to the table, what can be expected
>   to be faster:
>
>     - implementing the table as a builtins.list of builtins.tuples
>       with builtins.dicts as indexes for faster lookups and
>       additional sorted builtins.lists for sorted "views" on the
>       table
>
>     - implementing the table as a database table in sqlite3
>       (":memory:") and using SQL commands for insertion and
>       queries?

You're gonna get that dreaded response...

It depends.

To decide whether it's better to use built-in objects or an in-memory
SQLite3 database, I would decide more on the basis of desired
semantics and simplicity than pure performance. Can you do everything
with *just* dict lookups? If so, there's no point going for SQLite3.
Are you going to be wanting complex queries where you filter by
multiple columns? Then the dict is going to be a lot of hassle, so SQL
is well worth it. Might you, in the future, want to drop your data to
disk? Then *definitely* go SQLite3, because it's trivial to switch
from :memory: to a file, but switching your dict/list system to be
disk-backed is a lot harder.

In terms of pure performance, I would generally expect that anything
where the above rules say "go with dict/list" will also be faster with
a dict/list system than it would be with SQLite3. In the cases where
I'd prefer SQLite3, the performance could go either way, but honestly,
it matters less than the massive code maintenance cost of rolling your
own query system.

ChrisA