Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Pontus Bergsten]

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!
Regards,
/Pontus
 



 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  
wrote:

 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
d...@sqlite.org

  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Kees Nuyt]

On Wed, 15 Oct 2014 01:26:10 +0200, Kees Nuyt 
wrote:

> http://knuyt.demon.nl/sqlite.org/faq.html#q19

Oops, make that 
http://www.sqlite.org/faq.html#q19

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Kees Nuyt]

On Tue, 14 Oct 2014 21:24:40 + (UTC), Pontus Bergsten
 wrote:

>Hi,
>I have the following problem setup:

[...]

> INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time 
> BETWEEN t1 AND t2
>
> Is there any technique that can be used for tuning the performance of sqlite3 
> in this scenario?

I suspect this is a case of 
http://knuyt.demon.nl/sqlite.org/faq.html#q19


-- 
Regards,

Kees Nuyt


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Richard Hipp]

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

>  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Simon Slavin]

On 14 Oct 2014, at 10:24pm, Pontus Bergsten  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.



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

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Mikael]

On the embedded unit write caching disabled?


2014-10-14 23:24 GMT+02:00 Pontus Bergsten :

> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue when copying data from one sqlite database to another

[Ketil Froyn]

Depends on how safe/robust you want the copying to be, but if you can
simply rerun if something goes wrong, you might look into stuff like:

pragma journal_mode = MEMORY;
pragma synchronous = off;

But make sure you understand the consequences first by reading about these
commands:

http://www.sqlite.org/pragma.html

Cheers, Ketil
On 14 Oct 2014 23:25, "Pontus Bergsten"  wrote:

> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance issue when copying data from one sqlite database to another

[Pontus Bergsten]

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