fast-sqlite3-inserts VS Lua

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

The EventSource uses the Lua language to search for any services with ports set to 80 or 443 in the ‘argocd’ namespace. More examples can be found here.

If you're new to the 12 in 24 series, I'm learning and building projects with a new programming language every month - this month, it's the Lua scripting language. You can find source code for the projects I build in the official GitHub repository (check it out, this week's folder contains code for both this and two other bonus projects!).

This month, we're talking about Lua. It's not always a first choice when it comes to programming, but I think there's a lot to enjoy about this little language. Heck, I'm a big game development fan myself - I would look into it even if that was the only reason to.

It’s Portuguese. It’s the same in the Lua codebase [1].

[1]: https://github.com/lua/lua

If we think we need more flexibility for processing records, we can write our own embedded filters using Lua language. Lua is a highly efficient programming language used mainly for embedded scripting.

Lua

➜ ~ brew info lua ==> lua: stable 5.4.6 (bottled) Powerful, lightweight programming language https://www.lua.org/ /opt/homebrew/Cellar/lua/5.4.6 (29 files, 788.7KB) * Poured from bottle using the formulae.brew.sh API on 2023-05-16 at 11:03:06 From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/l/lua.rb License: MIT ==> Caveats You may also want luarocks: brew install luarocks ==> Analytics install: 16,599 (30 days), 56,745 (90 days), 139,027 (365 days) install-on-request: 1,763 (30 days), 6,266 (90 days), 21,105 (365 days) build-error: 0 (30 days)

I think Lua is a good example of doing documentation well. The source code is commented only as much as needed, mainly with brief comments about things that might not be obvious and a small number of longer explanations of how the architecture works (mainly relevant to developers). It also has a super nice feature that's surprisingly rare: each file has a very short line at the top that describes what the file is, so you don't have to guess based on the filename alone. The API is documented in a single HTML file on the website that has both the high level descriptions of the language and architecture, as well as documentation for each public-facing function. The docs are maintained by hand, but the API is mostly stable, so the docs don't need to change very often.

This is using the Lua language and the Solar2d game framework