Hi Simon, Thanks for the reply.
On 19 September 2011 13:23, Simon Slavin <slav...@bigfraud.org> wrote: > On 19 Sep 2011, at 10:13am, Jaco Breitenbach wrote: > > > CREATE TABLE T ( K varchar(22) PRIMARY KEY ); > > Note that SQLite doesn't really do 'varchar'. All text fields are text > fields and can have any number of characters in. You can choose to put 22 > character in each entry, but you might choose not to. For clarity, defining > that as 'K text' would have an identical result. > I've understood from the SQLite documentation that there really are only a small(ish) number of primitive column types. I've chosen to use the 'varchar' label because it's similar to the Oracle syntax that I'm familiar with. > > I run the database (3.7.7.1) in WAL mode, with checkpointing performed at > 10 > > minute intervals. > > You turned 'PRAGMA wal_autocheckpoint' off, right ? > > Correct. The frequency of checkpointing seems to have a significant impact on overall performance. I've disabled auto-checkpointing and introduced a time-based manual checkpoint frequency similar to that of TimesTen. In my tests I've set the interval to 10 minutes. If you know you are going to do lots of INSERTs in a row you can put > BEGIN/END around them. This will dramatically speed up the operation. On > the other hand, if your benchmark is simulating lots of separate logging > entries you will not want to do this. > > That is indeed how my application works. Records are processed in transactions of approximately 20,000 records each. > If you're actually concerned about filesize, then WAL mode is probably not > the right mode to use. You may be better off with 'PRAGMA journal_mode = > DELETE'. If your platform stores data on an SSD, then you may be better off > with 'PRAGMA journal_mode = PERSIST'. For further details see > > My application is currently using the PERSIST journal mode. Actual file size is not important. I've mentioned the file sizes only to add more detail and a clearer indication of where I get the numbers from. The PERSIST mode is used because it seems to have the least overhead of the available journal modes. > <http://www.sqlite.org/draft/pragma.html#pragma_journal_mode> > > If, on the other hand you aren't concerned about using too much space, and > are simply exploring to find out how things are likely to work in real life, > carry on. > > My application is rather simple. It simply inserts random 22-character strings into a single-column, unique-indexed table as fast as possible. The main concern is application performance and I've spent many hours optimising as much as possible. In terms of hardware, the resource I am most concerned about is I/O bandwidth and I am trying to estimate the I/O requirement as a function of record size and record processing rate. Best regards, Jaco _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
