I have the following problem setup:
We use sqlite3 in an embedded signal logger application. The "Main" in-memory 
signal signal database consists of some minor signal definition tables + two 
large tables (shards) with the actual signal data. The sharding technique is 
used in order to implement an efficient ringbuffer in sqlite.

Now, from time to time in the application, it is desired to extract some 
signals in some specified time window from the "Main" database, and save the 
selected signals to another smaller "Dest" database on USB memory. The "Dest" 
database will have the same signal definition tables as "Main", but only one 
signal data table. No ringbuffer functionality, and hence no sharding, is 
needed for the "Dest" database.
The actual copying is done by first creating the "Dest" database file with the 
required empty tables on USB, and then attach it to the "Main" in-memory 
database. Then the signal definitions and data is copied using a series of 
statements looking much like

INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time 
And here is the performance problem: 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. Profiling the thread that executes the sql-statements 
above, reveals that the thread is active in many very small steps, while 
waiting for the USB driver for very long time (compared to the active time), in 
between. During profiling the copy-thread only did useful work for about 5% of 
of the total time, the rest was waiting.

Is there any technique that can be used for tuning the performance of sqlite3 
in this scenario? For example, writing larger chunks of data to the "Dest" 

Pontus Bergsten

sqlite-users mailing list

Reply via email to