fast-sqlite3-inserts VS MonkeyType

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

i can't, unfortunately, it was youtube internal code. but by the same token a lot of the performance stuff was specific optimisations for that code and would not really generalise.

one of the current "extremely large python codebase" projects is instagram, and they do have some public repos, notably monkeytype (https://github.com/Instagram/MonkeyType) which youtube did have its own analogue of, and cinder (https://engineering.fb.com/2022/05/02/open-source/cinder-jit...). in general facebook's engineering blog is a great place to read about this sort of thing.

MonkeyType collects runtime types of function arguments and return values, and can automatically generate stub files or add type annotations directly to your Python code based on the types collected at runtime.

To alleviate the burden of manual annotation, MonkeyType offers a clever solution. It dynamically observes the types entering and leaving functions during code execution. Based on this observation, it generates a preliminary draft of type annotations. This significantly reduces the effort needed to add type hints to legacy code.

https://github.com/instagram/MonkeyType can perform the call logging, and can export a static typing file which is used by mypy, but also e.g. PyCharm. It doesn't expose such fine grained types, but you could build that based on the logged data.

Which introduces MonkeyType, a python library that generatics static type annotations by collecting runtime types.

Not sure if you're joking but there is something similar for python developed by a rather well known company https://github.com/Instagram/MonkeyType