fast-sqlite3-inserts VS flamegraph

I am experimenting with SQLite, where I try inserting 1B rows in under a minute. The current best is inserting 100M rows at 23s. I cut many corners to get performance, but the tweaks might suit your workload.

I have explained my rationale and approach here - https://avi.im/blag/2021/fast-sqlite-inserts/

the repo link - https://github.com/avinassh/fast-sqlite3-inserts

I am working on a project [0] to generate 1 billion rows in SQLite under a minute and inserted 100M rows inserts in 33 seconds. First, I generate the rows and insert them in an in-memory database, then flush them to the disk at the end. To flush it to disk it takes only 2 seconds, so 99% of the time is being spent generating and adding rows to the in-memory B Tree.

For Python optimisation, have you tried PyPy? I ran my same code (zero changes) using PyPy, and I got 3.5x better speed.

I published my findings here [1].

[0] - https://github.com/avinassh/fast-sqlite3-inserts

[1] - https://avi.im/blag/2021/fast-sqlite-inserts/

I have a side project where I tried to insert one billion rows in SQLite. I was able to insert 100 million rows using Python under 210 seconds. The same thing with PyPy took 120 seconds. I am wondering what kind of speed boost I would get with Cython

Measure, measure, measure! There is a PR which made really minor changes, but it got 2x speed boost with CPython version

[somewhat related] Recently, I was benchmarking SQLite inserts and I managed to insert 3.3M records per second (100M in 33 ish seconds) on my local machine - https://github.com/avinassh/fast-sqlite3-inserts Ofcourse the comparison is not apples to apples, but sharing here if anyone finds it interesting

You can install cargo-flamegraph with cargo install flamegraph. There are some underlying requirements to be able to use cargo-flamegraph; you will want to take a look at the repo here to make sure you have the right dependencies.

I tried to diagnose the issue with flamegraph, but unfortunately the flamegraph didn't show anything beyond the next call for some reason

having some tools to identify slowness origins (flamegraph is one... but not sure it's the way to go)

I'm currently implementing a solution for the first part of the day 16. It work but it is really slow... I'd like to : - understand why - having some tools to identify slowness origins (flamegraph is one... but not sure it's the way to go) - eventually have some clue/solution/idea - have general feedback on what in my "coding style" is not appropriate for rust (I come from java/kotlin/ts even if I've already coded a bit in c/c++) : for example I love iterator & sequence but i feel they are not really suited to overuse in rust (mostly because of async & result).

Use a profiler. Flamegraph is a good way to visualise profiler output. This lets you identify which functions are taking up a large amount of time - and hence helps you identify where to focus your optimisation efforts.

You tried trying to see what takes the most time under load via flames? https://github.com/flamegraph-rs/flamegraph

Well, in cases like that the answer is straight forward, use a profiler like https://github.com/flamegraph-rs/flamegraph

I don't see any obvious problems with your algorithm. I've had luck using cargo-flamegraph to identify the slow parts of my code. That's going to show you which parts to focus on improving the performance of!

From the readme of cargo flamegraph: