I've used the other pragma, but does
PRAGMA locking_mode = EXCLUSIVE;
improve performance much?
Inserting a billion rows in SQLite under a minute
101–110 of 164 posts
Re: Inserting a billion rows in SQLite under a minute
#102Here's my solution : generate the 1B records .sqlite file once beforehand, then before running each test, copy it over the test DB file ;)
Re: Inserting a billion rows in SQLite under a minute
#103Earlier quoted context omitted.
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
Achieving 100m SQLite inserts in under a minute.
Re: Inserting a billion rows in SQLite under a minute
#104How long does it take when using the native CSV import features? https://www.sqlite.org/csv.html
I built https://superintendent.app and experimented a lot with this feature.
I can import 1GB CSV file in 10 seconds on my MacBook. This queries from a virtual table and puts it in actual table
Re: Inserting a billion rows in SQLite under a minute
#105an in memory database is not a database ... you might as well just create billion object array and have your java process constantly running to have that data accessible.
Re: Inserting a billion rows in SQLite under a minute
#106You can go even faster if you can organize your problem such that you simply start from a copy of a template SQLite database each time (it is just a file/byte sequence). We do SQL evaluation for a lot of business logic throughout our product, and we have found that starting from a template database (i.e. one with the schema predefined and canonical values populated) can save a lot of time when working in tight loops.
Are you suggesting have a template and then updating rows with random values where necessary?
> Are you suggesting have a template and then updating rows with random values where necessary?
> Are you suggesting have a template and then updating rows with random values where necessary?
Re: Inserting a billion rows in SQLite under a minute
#107You can go even faster if you can organize your problem such that you simply start from a copy of a template SQLite database each time (it is just a file/byte sequence). We do SQL evaluation for a lot of business logic throughout our product, and we have found that starting from a template database (i.e. one with the schema predefined and canonical values populated) can save a lot of time when working in tight loops.
Are you suggesting have a template and then updating rows with random values where necessary?
3PwjGO6X9Gwc
Re: Inserting a billion rows in SQLite under a minute
#1087VKs3Dsn
Re: Inserting a billion rows in SQLite under a minute
#109Re: Inserting a billion rows in SQLite under a minute
#110Hey 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.