On 14 Oct 2014, at 10:24pm, Pontus Bergsten <pontus_bergs...@yahoo.se> wrote:
> 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? Your description makes perfect sense, bearing in mind that cheap USB drives are slow. A relatively cheap piece of research might be to see if you can find a (more expensive) fast USB drive and see whether that makes your operation faster. Other than that, two possibilities occur to me: (A) Create your new database file on main storage, and create the Dest table there. Once it is complete, close the database, then copy the database file to the USB drive using file copy commands rather than SQLite commands. That should give you the fastest possible way of getting that data onto the drive. (B) Write your Dest table to memory, then use the SQLite backup API to copy that to a file on the USB drive. Copying the entire database page by page should be faster than copying the data row by row. <https://www.sqlite.org/backup.html> I do not know that either of these will definitely help you. It depends too much on the relative speed of various components of your embedded system and on the width of your data bottleneck. But they might be worth exploring. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
