On Mon, May 5, 2008 at 4:05 AM, Dan <[EMAIL PROTECTED]> wrote: > On May 5, 2008, at 1:59 PM, John Passaniti wrote: > > I am trying to decide if sqlite is appropriate for my system. > > [...] > > Use transactions if you did not already plan to.
Yes, thanks. In the simplest case, I would be writing sensor data once a minute with a single INSERT, so I doubt a transaction would be of any benefit there. But certainly if I found I was writing more than one thing, I'd wrap it in a transaction. > If you are using automatically generated rowids for your table (you > are unless you have declared a column as INTEGER PRIMARY KEY) and > the indexed timestamps are always increasing (I guess they are, > right?) then your overhead will be mimimal. I was now planning on using automatically generated rowids. Since this is timestamped data, I was going to use a REAL for the timestamp, and declare that the primary key. As these timestamps come from the clock, they are always increasing. Do I gain any benefit (aside from disk space) of not using a REAL but in multiplying the time by 86400 (24*60*60) and expressing it as an INTEGER? > You should try to measure it first to make sure though... I sorta have, and the results are pretty depressing. What I did was add printf statements to the unixRead and unixWrite routines (I'm under Linux). I then did 10000 INSERTs with an ascending timestamp (as a REAL) and random sensor data. The result was 161331 writes. So a very rough estimate here is that each INSERT is ultimately resulting in 16 writes. Now, jffs2 may effectively end up coalescing some of those writes-- but even if it manages to merge 50% of them (a guess), I'm still wearing writing to flash blocks 8 times faster than my INSERT rate. Or something like that. I guess I'll play with block sizes and see how that affects things. My questions: Aside from transactions and INTEGER PRIMARY KEY, are there any tips or tricks others have to reduce the number of writes to the media? Is there anyone who is currently using sqlite3 and jffs2 who is also writing data at a constant rate? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
