Duckdb to replace SQLite3 for better performance and analytical report

[guanlisheng]

https://duckdb.org/

seems both MMEX and Duckdb share some common tech stack, such as C++11. ideally we can replace current SQLite3 with Duckdb.

[ovari]

If you are after performance, should Polars be used instead?

Cf. https://dataengineeringcentral.substack.com/p/whats-all-the-hype-with-duckdb

[vomikan]

If you are after performance, should Polars be used instead?

Polaris is DataFrame. DuckDB is an in-process SQL OLAP database management system.
Polaris does not store data, only processes it in RAM, which is why it works fast.

[guanlisheng]

thanks @ovari and @vomikan ,

exactly that the goal is to replace SQLite3 with an in-process "database" instead of a real one.

[gabriele-v]

Are all commands of SQLite working in DuckDB? If not, I'd avoid it because we have already broken GRM multiple times in the past and we should avoid creating other issues for a small performance gain

[rmelillo76]

Are all commands of SQLite working in DuckDB? If not, I'd avoid it because we have already broken GRM multiple times in the past and we should avoid creating other issues for a small performance gain

I agree. What problem is trying to be solved here? I also can't think of any performance issues that exist, so what performance are we trying to improve? MMEX just needs a simple, self-contained, portable database, and SQLLite fits the bill very well. If we were looking into an overall architectural change, I could understand, but change just for the sake of change doesn't make sense to me.

[vomikan]

This is not a solution to any problem. This is the development of the product as a whole. duckDB is more progressive.

[ovari]

When to not use DuckDB

• Writing to a single database from multiple concurrent processes

https://duckdb.org/

It would seem that duckDB would need to be purged if in the future it was decided to allow multiple devices to access, at the same time, one MMEX-database file.

[guanlisheng]

thanks all, and I agree it is a significant change. I will be sure to provide more materials later.

[grumpy235]

As a long time user of MMEX and an ancient sysprog who wishes he could get his head around C++ so he could contribute, I thought I would throw in my two penneth.

It strikes me this is another case of "it aint broke but we are going to fix it" !
The limited number of dedicated developers here work very hard keeping the program ticking over and dealing with the backlog of issues/requirements, they obviously do not have enough to do so let's change a key part of the program!

SQLite has a large usage base and has been working in MMEX just fine for 20+ years so why complicate matters for what I consider to be limited gains other than it's fancy and new?

The performance graph shows gains for 20GB of data and I wonder if any MMEX| user has a database anywhere near this size - mine has 30 years of data and is only around 2.5Meg, it works as quick as I need it to and from a performance perspective I doubt I would notice the difference.

If this does go ahead perhaps do it as a separate project in parallel with a later merge ?

With my cynical hat on, this might be a case of - "I would like to get some experience of Duckdb development so let's put it in MMEX" 😀

[renato-mmex]

I would prefer not to have additional problems / unrest due to a not necessary change of the database would affect the currently good stability of MMEX.
From my point of view, the current SQLite3 solution is sufficiently fast, if a user has problems, I would recommend him to replace his hard disk with an SSD.
Probably we would not have the capacity to seriously perform the necessary tests on all the operating systems we currently support.
Just by the way: There would be enough problems / wishes that the users would like to have solved.

[guanlisheng]

wow, understand your concerns: stability is the key.
it also matches the goal of creating #6247 to monitor the usage.

As a desktop-only app, current performance is sufficient, I will spend some time on quality.

[guanlisheng]

in terms of stability, we should keep our schema stable enough. these data schemas are our core values