fast-sqlite3-inserts VS crystal

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

27. Crystal - $77,104

I like the first code example on https://crystal-lang.org

    # A very basic HTTP server

Obviously as an interpreted language, it's never going to be as fast as something like C, Rust, or Go. Traditionally the ruby maintainers have not designed or optimized for pure speed, but that is changing, and the language is definitely faster these days compared to a decade ago.

If you like the ruby syntax/language but want the speed of a compiled language, it's also worth checking out Crystal[^1]. It's mostly ruby-like in syntax, style, and developer ergonomics.[^2] Although it's an entirely different language. Also a tiny community.

[1]: https://crystal-lang.org/

Crystal is a nice language that's not only simple to read and write but performs very well too. And the documentation is amazing as well.

Ruby is a super fun scripting language. I much prefer it to python when I need something with a little more "ooomph" than bash. It's just...nice...to write in. Ruby performance has come a long way in the last decade as well. There's libraries for pretty much everything.

My modern programming toolkit is basically golang + ruby + bash and I am never left wanting.

I do find Crystal (https://crystal-lang.org/) really interesting and am hoping it has its own "ruby on rails" moment that helps the language reach a tipping point in popularity. All the beauty of ruby with all of the speed of Go (and then some, it often compares favorably to languages like rust in benchmarks).