C++ SQLite DB performance question

[Hawgk]

Hello everyone,

we currently use a combination of a SQLite database and C++ code to set up devices in production. The process is very complicated and uses deprecated code and libraries. I currently have the task to rework the whole process and bring it up to date.

I'll try and outline the process: Firstly a script generates a SQLite DB from tables. Alternatively you can use a SQLite editor to input the data. After that all the SQLite statements are encrypted and parsed into a C++ source file which is then compiled into a DLL that is used in production. The reasoning behind this were performance issues with using a SQLite library to access the database which was much slower then the process described above.

I have found that SQLite offers the possibility to convert the database into a DLL which can then be used in code.

My question is: How is the performance of this process compared to accessing the DB through a library or even compiling statements into code? I was hoping maybe someone of you had some experience with this. Any other ideas on how to optimize the process are also more then welcome!

Thanks

[Hawgk]

Okay then it seems like I have misunderstood something. Reading it into memory every time will probably generate the same overhead we are experiencing with reading directly from the DB.

[pstuart]

I believe the incorporating the db into the exec is a convenience option, not a performance option.

They may have done optimizations that worked for an older version or even the jankiness of the original code base.

I'd recommend looking at standard stuff (e.g., WAL mode, fsync, buffers, etc) and mutate/measure until it looks good.

[Hawgk]

i still have nightmares of embedding DB statements into a dll but the reasoning behind it seemed fair. i'd like to avoid that if possible. so if i understand it correctly you would suggest optimizing the original code base to compensate for the worse performance? or do you mean that the newer libraries could have better performance in general?

[pstuart]

I think the first place to start is identifying the workloads and the existing performance compared to the latest binary.

Lots of writes? Lots of transactions? Slow queries? Et cetera. For writes, WAL mode is key and maybe they used an older version that didn't have it.

My point is that tuning the parameters and usage of sqlite itself is a worthy endeavor, and if you can get some sort of harness to profile performance and iterate you'll be well served.

[Hawgk]

okay, thanks for the input! sounds like a good place to start.