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.

Nick Shaw · Accepted Answer · 2012-02-07 16:39:11Z

1

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.

answered Feb 7, 2012 at 16:39

Nick Shaw

2,12319 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:03:32Z

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, 2017 at 12:03

CommunityBot

11 silver badge

answered Feb 7, 2012 at 17:07

JimR

16.3k2 gold badges25 silver badges26 bronze badges

Comments

Graham Borland · Accepted Answer · 2012-02-07 16:34:14Z

0

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

answered Feb 7, 2012 at 16:34

Graham Borland

60.8k21 gold badges144 silver badges184 bronze badges

1 Comment

Gold Fish Over a year ago

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.

Collectives™ on Stack Overflow

SQLite DB building speed-up

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related