Hi, 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 BETWEEN t1 AND t2 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" database? Regards, Pontus Bergsten _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
