Inserting a billion rows in SQLite under a minute • Hacker News Board

Re: Inserting a billion rows in SQLite under a minute

#161

Post by roberto » Mon, Jul 19, 2021, 5:29 PM UTC

lmz wrote: post #3

I wonder if defining a virtual table ( https://www.sqlite.org/vtab.html ) and just doing INSERT INTO t (...) SELECT ... from virtual_table would be any faster.

If you define the virtual table would you even need to do the `INSERT`? Just query the virtual table instead of inserting into `t` and querying `t`.

Re: Inserting a billion rows in SQLite under a minute

#162

Post by freeqaz » Mon, Jul 19, 2021, 7:23 PM UTC

orf wrote: post #26

How long does it take when using the native CSV import features? https://www.sqlite.org/csv.html

This is what I did the last time I ran into SQLite being slow with inserts.

I just shat out a CSV file and then imported it. It was much quicker! (like 1000x faster)

This article has some useful notes for me now to try the next time I play with my pet projects. :)

Re: Inserting a billion rows in SQLite under a minute

#163

Post by lexfiend » Tue, Jul 20, 2021, 2:34 AM UTC

adontz wrote: post #123

avinassh wrote: post #12

Hey 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?

Possibly because:

> The machine I am using is MacBook Pro, 2019 (2.4 GHz Quad Core i5, 8GB, 256GB SSD, Big Sur 11.1)

Given the target schema, 100M rows with only 8GB RAM risks hitting swap hard.

Re: Inserting a billion rows in SQLite under a minute

#164

Post by rmetzler » Tue, Jul 20, 2021, 7:31 PM UTC

DemocracyFTW wrote: post #147

rmetzler wrote: post #8

Is PRAGMA temp_store = MEMORY the same as putting the file on a TempFS?

I tried using tmpfs to create and exercise a file-based SQLite DB, and it performed significantly worse than a RAM-based DB (opened with an empty string or ':memory:').

Thank you :)