Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Inserting a billion rows in SQLite under a minute

avi.im

Post reply on HN
Print view View original

81–90 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#81

Post by IfOnlyYouKnew »

I ran this and got the following results:

      /fast-sqlite3-inserts (master)> time make busy-rust
      
      Sun Jul 18 17:04:59 UTC 2021 [RUST] busy.rs (100_000_000) iterations
      
      real 0m9.816s
      user 0m9.380s
      sys 0m0.433s
      
      ________________________________________________________
      Executed in    9.92 secs    fish           external
      usr time    9.43 secs    0.20 millis    9.43 secs
      sys time    0.47 secs    1.07 millis    0.47 secs
      
      
      fast-sqlite3-inserts (master)> time make busy-rust-thread
      
      Sun Jul 18 17:04:48 UTC 2021 [RUST] threaded_busy.rs (100_000_000) iterations
      
      real 0m2.104s
      user 0m13.640s
      sys 0m0.724s
      
      ________________________________________________________
      Executed in    2.33 secs    fish           external
      usr time   13.68 secs    0.20 millis   13.68 secs
      sys time    0.78 secs    1.18 millis    0.78 secs
I'm probably doing something wrong. Or I'm getting the pace needed for the billion?

This is on a M1 MacBook Air.

Re: Inserting a billion rows in SQLite under a minute

#83

Post by jtvjan »

motogpjimbo wrote: post #76
xeromal wrote: post #40

Earlier quoted context omitted.

Yeah, that's my interpretation of a billion too. I vaguely recall that India or Britain interprets a billion differently though. Maybe that's what they're thinking?

Just about everyone in the UK defines a billion to be 10^9. I've heard about the 10^12 definition but never encountered anyone who uses it - I think it must have been an older usage that fell out of favour.

In 1974, the UK government abandoned the long scale (million, milliard, billion, billiard) in favor of the short scale (million, billion, trillion, quadrillion) used in the US. The long scale is still used in languages like Dutch, German, and French.

Re: Inserting a billion rows in SQLite under a minute

#84

Post by tyingq »

ndepoel wrote: post #63
tyingq wrote: post #22

Earlier quoted context omitted.

Do you mean crafting all the various database page btree structures and entries yourself? I'd be concerned about subtle bugs.

An SQLite database is just a file. You can build the empty database with schema and base values ahead of time, save it to a file (or an in-memory byte buffer) and then every time you want to create a new database, you just copy that file. No need to do any expensive initialization queries that way. If raw high-speed throughput is needed, skipping that step can make a significant difference.

Yes, that approach makes sense. I thought what I was replying to was suggesting writing b-tree pages themselves, outside of sqlite, for the new data.

Re: Inserting a billion rows in SQLite under a minute

#85

Post by newman314 »

avinassh wrote: post #60
minitoar wrote: post #39

Earlier 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…

Attempting 1 Billion Row Inserts in under 1 Minute

Re: Inserting a billion rows in SQLite under a minute

#86

Post by nzealand »

If you insert 4 rows into a table, and join it on itself without any conditional criteria, it will result in a cartesian product of 16 rows (Select * from a, a)

Do that cartesian join three more times, and you have over 4 billion results.

Then you simply need to use the random function in conjunction with division, rounding and case statements to get the desired random numbers.

Re: Inserting a billion rows in SQLite under a minute

#87

Post by orf »

motoboi wrote: post #82
orf wrote: post #26

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

It’s fast. But then we need a “1 billion CSV lines” article.

Which I expect is significantly faster to generate. You could probably combine it with a pipe to avoid all disk IO.

Re: Inserting a billion rows in SQLite under a minute

#88

Post by geofft »

einpoklum wrote: post #31

> Recently, I ran into a situation where I needed a test database with lots of rows and needed it fast. So I did what any programmer would do: wrote a Python script to generate the DB. The author must have a rather low opinion of programmers... generating data with a script is good for 1,000 rows. Maybe 10,000 rows. 100,000 is probably stretching it. Beyond that the slowness will be meaningful. Anyway, if the "test d…

I suspect these opinions would be less likely to get downvoted if you could back them up with data, specifically:

- Assuming you do have a test database in advance that's represented in a file, but for some reason not a SQLite file or other database, how long does it take to load 100M rows in a database of your choice, and how does that compare to the author's results?

- The author does not in fact have a test database in advance, and is generating random data, which I think is what you're saying by "apply your random-number-generating function on a trivial column" - how long would your approach take to generate 100M rows?

- If you generated a database of about the same size in a database of your choice, how do bulk operations on large tables perform in comparison to SQLite? (My understanding is that it's "lite" because it's not client/server, not because its performance is poor.)

I don't think any of these experiments would be terribly hard to run.

Re: Inserting a billion rows in SQLite under a minute

#89

Post by diehunde »

atleta wrote: post #42
xeromal wrote: post #40

Earlier quoted context omitted.

Yeah, that's my interpretation of a billion too. I vaguely recall that India or Britain interprets a billion differently though. Maybe that's what they're thinking?

I think it's the same in UK English as well. But in some (most?) European languages billion actually means 1 000 000 million (so a thousand times more ). And we use "milliard" for 1000 million.

https://en.wikipedia.org/wiki/Long_and_short_scale for anyone that's interested in the differences

Re: Inserting a billion rows in SQLite under a minute

#90

Post by vajrabum »

mimir wrote: post #18

Database optimization posts are always interesting, but it's really hard to do any apples to apples comparison. Your performance is going to depend mostly on your hardware, internal database settings and tunings, and OS level tunings. I'm glad this one included some insight into the SQLite settings disabled, but there's always going to be too many factors to easily compare this to your own setup. For most SQL systems…

Oracle, DB2, MySQL, SQL Server, and PostgreSQL all support bulk insert. Two obvious use cases are QA Databases and the L part of ETL which pretty much require it.
Post reply on HN