Need it done in Deno, fren.
Inserting a billion rows in SQLite under a minute
121–130 of 164 posts
Re: Inserting a billion rows in SQLite under a minute
#122Earlier quoted context omitted.
Yes, if you want to run non-trivial queries on that data. Although, frankly, SQLite would not be my choice.
What would be your choice?
If you don't do any joins, Clikhouse is worth a try. Otherwise, MonetDB (or even MonetDB embedded) if you want FOSS. Commercial offerings - Actian Vector, or maybe HyperDB. The big corporate ones are probably pretty good, but it's always difficult to tell because there's this taboo on any proper benchmarking against them.
If you just want to browse what other people are using, and not go by the recommendations of a random commenter, try:
https://db-engines.com/en/ranking
but note that's a joint ranking both for transaction-focused and analytics-focused DBMSes.
If it's time series data there are some more specialized offerings and I'm (even) less of an expert there.
Re: Inserting a billion rows in SQLite under a minute
#123Hey all, author here. I didn't expect this to reach front page of HN. I came here to submit and it was here already! I am looking for more ideas to experiment. Here's one idea which someone from another forum gave me: exploring recursive queries and using SQLite random methods to do the insertions. Another crazy idea is to learn about SQLite file format and just write the pages to disk.
Why don't you create in-memory database (see SQLITE_OPEN_MEMORY, or ":memory:", or sqlite3_vfs_register) and copy result to a real file?
Re: Inserting a billion rows in SQLite under a minute
#124Here is an example using Tcl to create an SQL script using recursive common table expressions:
# createSQL.tcl
set increment 100000
set loop [expr {100000000 / $increment}]
puts {PRAGMA journal_mode = OFF; PRAGMA synchronous = 0; PRAGMA cache_size = 1000000; PRAGMA locking_mode = EXCLUSIVE; PRAGMA temp_store = MEMORY;
CREATE TABLE user( pk INTEGER PRIMARY KEY, area INTEGER, age INTEGER, active INTEGER );}
for {set i 0} {$i #EOF
Core 2 Duo 2.53 GHz, 4GB RAM, SSD:
$ time -p tclsh createsql.tcl | sqlite3 test.db
off
exclusive
real 213.37
user 208.10
sys 4.58
Re: Inserting a billion rows in SQLite under a minute
#125If it's a test database, can you simply generate it once, commit it, and use that in all future tests?
Re: Inserting a billion rows in SQLite under a minute
#126>Inserting One Billion Rows in SQLite Under A Minute
But the results given are all for inserting 0.1B rows, not 1B rows. What are the full results?
Re: Inserting a billion rows in SQLite under a minute
#127Earlier quoted context omitted.
Ok but that is a rather clickbaity title. The title makes it sound like they are successfully doing that.
Hey, sorry for the misleading title. I started with ‘Fast SQLite Inserts’ and it had many iterations. In the title, I wanted to intend that I want to insert 1 billion rows under a minute on my machine. I thought the current title is fine, since I got LGTM for earlier drafts. The detail about on my machine is also important since mine is a two year old laptop and all the measurements are done on it. Also I got another…
>I am wondering the right way to convey all of this in the title yet also keep it short.
Add "trying":
"Trying to insert 1 billion rows in SQL in under a minute".
If anything it's more interesting because it implies the chance of failure.
Re: Inserting a billion rows in SQLite under a minute
#128[deleted]
Re: Inserting a billion rows in SQLite under a minute
#129Just tried to insert 100M KV pairs of the same data format in a KV store. It takes about 26s on a Xeon Silver 4210, including fully writing down the data to the filesystem/SSD.
The source code can be found here: https://github.com/wuxb45/remixdb/blob/master/i100m.c
$ make i100m.out libremixdb.so
$ numactl -N 0 ./i100m.out
insert time without sync: 24.865s, with sync: 25.801s