What is the fastest way to insert 100 000 records from one database to another?

Question

I've a mobile application. My client has a large data set ~100.000 records. It's updated frequently. When we sync we need to copy from one database to another.

I've attached the second database to the main, and run an insert into table select * from sync.table.

This is extremely slow, it takes about 10 minutes I think. I noticed that the journal file gets increased step by step.

How can I speed this up?

EDITED 1

I have indexes off, and I have journal off. Using

insert into table select * from sync.table

it still takes 10 minutes.

EDITED 2

If I run a query like

select id,invitem,invid,cost from inventory where itemtype = 1 
order by invitem limit 50 

it takes 15-20 seconds.

The table schema is:

CREATE TABLE inventory  
('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 'serverid' INTEGER NOT NULL DEFAULT 0,
 'itemtype' INTEGER NOT NULL DEFAULT 0,
 'invitem' VARCHAR,
 'instock' FLOAT  NOT NULL DEFAULT 0,
 'cost' FLOAT NOT NULL DEFAULT 0,
 'invid' VARCHAR,
 'categoryid' INTEGER  DEFAULT 0,
 'pdacategoryid' INTEGER DEFAULT 0,
 'notes' VARCHAR,
 'threshold' INTEGER  NOT NULL DEFAULT 0,
 'ordered' INTEGER  NOT NULL DEFAULT 0,
 'supplier' VARCHAR,
 'markup' FLOAT NOT NULL DEFAULT 0,
 'taxfree' INTEGER NOT NULL DEFAULT 0,
 'dirty' INTEGER NOT NULL DEFAULT 1,
 'username' VARCHAR,
 'version' INTEGER NOT NULL DEFAULT 15
)

Indexes are created like

CREATE INDEX idx_inventory_categoryid ON inventory (pdacategoryid);
CREATE INDEX idx_inventory_invitem ON inventory (invitem);
CREATE INDEX idx_inventory_itemtype ON inventory (itemtype);

I am wondering, the insert into ... select * from isn't the fastest built-in way to do massive data copy?

EDITED 3

SQLite is server-less, so please stop voting a particular answer, because that is not the answer I'm sure.

Answer 1

If the target is some version of MS SQL Server, SqlBulkCopy offers an efficient insert for large data sets this is similar to the command bcp.

You can also disable/remove the non-clustered indexes before inserting, and re-create them after.

In SQLite, these are usually pretty fast:

.dump ?TABLE? ...      Dump the database in an SQL text format
.import FILE TABLE     Import data from FILE into TABLE

Also try: PRAGMA journal_mode = OFF

FYI, you should be able to run the command line utility on Windows Mobile if you include it in your package.