SQLite DB building speed-up

Question

I'm going to use SQLite in order to save a lot of data in real-time environment.

In order to avoid procedures of find disk space (or move pages in the DB file) for new data to be written to the DB in real-time, I want to build tables in advance and insert into them the largest data that any cell can has (according to its type), So in the real-time running, there will be only 'UPDATES' queries.

The building and inserting data made in journal_mode=WAL mode.

I have 6 different DB files that i have to build. Every DB has between 10 to 200 tables, where all the tables in all the DB look the same :

ID  |  TimeStart     |   Float data   |   Float data   |   Float data
--------------------------------------------------------------------------------

The difference is that there are some tables with 100000 rows and some with 500000 rows.

These DBs are built on a SD card with an ARM9 CPU (on linux), so it takes a lot of time to build the DBs. I am talking about some days.

How can i speed-up the building? are there any 'Pragmas' or tricks that i can do? Can i copy a ready-table?

It is important to mention that the robust of the DB is not important in the building process - Speed is much more important to me than the corruption possibility of the DB.

score 1 · Answer 1 · answered Feb 07 '12 at 16:39

I concur with @Graham Borland's answer, but also: if you have any indexes, I'd advise you to not create them until after you've added all the data to the DB. If you add them before hand, the indexes update themselves every time you insert a new record, which slows things down immeasurably when you insert a very large number of rows in quick succession.

score 0 · Answer 2 · answered Feb 07 '12 at 16:34

0

Pre-generate the database on your host machine, and copy it when you install your application to the target device.

answered Feb 07 '12 at 16:34

Graham Borland

60,055
21
138
179

I thought about it, The problem with that is when you have different targets you will have to manually copy different DB, while when you build the DB on the target, it can check for the target board automatically and according to it - build the relevant DB. – Gold Fish Feb 08 '12 at 06:42

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

Read this, it is very relevant. The answers by Graham Borland and Nick Shaw are also very relevant and are part of the advice in the linked document I've given you.

edited May 23 '17 at 12:03

Community

1
1

answered Feb 07 '12 at 17:07

JimR

15,513
2
20
26

SQLite DB building speed-up

3 Answers3