DB on JFFS

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

DB on JFFS

Levente Kovacs
I'm implementing an application that is on a flash filesystem (JFFS).
The flash is connected to the CPU with SPI.

I experience about 1s INSERT time. My DB isn't big, just about 200k. I
implement a fifo like operation, so i have a fixed length row count. I
DELETE old data from time to time.

I call these function at each INSERT:

sqlite3_prepare_v2(sq3, query, -1, &sq3_stmt, NULL);
sqlite3_step(sq3_stmt);
sqlite3_finalize(sq3_stmt);

query holds the INSERT SQL.

The question is what can I do to speed up the INSERT proces? The database is
also opened by other processes, so I want to lock the table as short time as I
can.

Any hints are welcome.

Thanks,
Levente
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DB on JFFS

Simon Slavin-3


On 3 Nov 2017, at 9:35pm, Lev <[hidden email]> wrote:

> I call these function at each INSERT:
>
> sqlite3_prepare_v2(sq3, query, -1, &sq3_stmt, NULL);
> sqlite3_step(sq3_stmt);
> sqlite3_finalize(sq3_stmt);
>
> query holds the INSERT SQL.
>
> The question is what can I do to speed up the INSERT proces? The database is
> also opened by other processes, so I want to lock the table as short time as I
> can.

Those two are in conclict.  You might like to try putting BEGIN … END around your inserts just for testing.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DB on JFFS

Levente Kovacs
On Fri, 3 Nov 2017 21:40:52 +0000
Simon Slavin <[hidden email]> wrote:

> Those two are in conclict.  You might like to try putting BEGIN … END
> around your inserts just for testing.

That is a good idea, thanks. So it might look like this?

BEGIN TRANSACTION;

INSERT ...
INSERT ...

COMMIT;


Also, I don't really know how to set up a cache. I open my database as follows:

sqlite3_open_v2(sdmd->sqlite_fn, &sdmd->db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_PRIVATECACHE, NULL);

Despite the privatecache, I experience that evry INSERT is written to the disk when I call sqlite3_finalize().


Thank you very much for your help.
Levente
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DB on JFFS

Simon Slavin-3


On 4 Nov 2017, at 4:29pm, Lev <[hidden email]> wrote:

> That is a good idea, thanks. So it might look like this?
>
> BEGIN TRANSACTION;
>
> INSERT ...
> INSERT ...
>
> COMMIT;

Yes.

> Also, I don't really know how to set up a cache.

Don;’t bother with that at the moment.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] DB on JFFS

Hick Gunter
In reply to this post by Levente Kovacs
In addition to putting mutiple inserts within a single transaction (this saves "disc" IO), you might like to look into using parameters (this saves on CPU spent parsing/compiling the statements. This leaves you with:

(setup)
connect
prepare
BEGIN

(loop)
bind
step
reset

(cleanup)
END
finalize
disconnect

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Lev
Gesendet: Freitag, 03. November 2017 22:36
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] DB on JFFS

I'm implementing an application that is on a flash filesystem (JFFS).
The flash is connected to the CPU with SPI.

I experience about 1s INSERT time. My DB isn't big, just about 200k. I implement a fifo like operation, so i have a fixed length row count. I DELETE old data from time to time.

I call these function at each INSERT:

sqlite3_prepare_v2(sq3, query, -1, &sq3_stmt, NULL); sqlite3_step(sq3_stmt); sqlite3_finalize(sq3_stmt);

query holds the INSERT SQL.

The question is what can I do to speed up the INSERT proces? The database is also opened by other processes, so I want to lock the table as short time as I can.

Any hints are welcome.

Thanks,
Levente
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users