Thanks for your suggestions.
@Mikael I do believe that write caching was disabled on target. However, 
enabling lazy data write and data caching didn't make much of a difference.
@Ketil I specified the pragmas before copying by executing the sql statements 
"PRAGMA Dest.journal_mode = OFF" and "PRAGMA synchronous = OFF" on the database 
connection containing the "Main" and the attached "Dest" database. The queries 
seemed to be accepted by sqlite. However, it didn't make much of a difference.

@Richard The embedded operating system is RTOS-32 (including RTFiles-32 and 
RTUSB-32) from On time.
I find it strange that enabling the file data cache, and the setting of the 
synchronous pragma didn't affect performance. However, from the thread 
profiling, it can be seen that a "INSERT INTO ... FROM  ... WHERE"  statement 
usually have a couple of continuous segments with fairly good CPU utilization 
for about 10 to 50 ms, that is followed by long periods, 100 to 200 ms, 
containing only sporadic activity ~10 us triggered by the USB driver.
In the current implementation the data is transferred in chunks using separate 
"INSERT INTO ... FROM  ... WHERE" statements. The reason for this was to 
display some progress to the user. Next, I will try to transfer data data in 
larger chunks and see it makes any difference.
Any other ideas are most welcome!

     Från: Richard Hipp <>
 Till: Pontus Bergsten <>; General Discussion of SQLite 
Database <> 
 Skickat: onsdag, 15 oktober 2014 0:29
 Ämne: Re: [sqlite] Performance issue when copying data from one sqlite 
database to another

On Tue, Oct 14, 2014 at 5:24 PM, Pontus Bergsten <> 

 When the application is executed on Windows on a desktop computer, the copying 
works fine and the performance is fairly ok, even when saving to USB. However, 
when the same code is executed on the embedded system, the copying of data is 
extremely slow, even though the CPU load is very moderate.

That sounds like a file-system problem to me.  What is your embedded OS?

D. Richard Hipp

sqlite-users mailing list

Reply via email to