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 run the database (3.7.7.1) in WAL mode, with checkpointing performed at 10 > minute intervals. You turned 'PRAGMA wal_autocheckpoint' off, right ? > From tests performed on an HP-UX Itanium server I have the following > figures: > > Processing rate: 135,416 inserts/min (2,257 inserts/s) > Total Inserts @ checkpoint (10 min): 1,354,167 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. > So, from these figures I calculate the following: > Total bytes per record in the data file = 174,230,528 / 2,500,000 = 70 > bytes/record > > In the case of the data file, since my record is only 22 bytes wide, am I > correct in assuming that the extra 48 bytes are for the index? More or less, but it's not fixed. SQLite uses a tree structure to store the index, and the more rows you have in your table, the higher percentage of filespace is devoted to the index. Also, some of that file (a fixed amount) is header information. Like a description of the structure of each table and index. > Total bytes per record in the log file = 2,050,719,096 / 1,354,167 = 1514 > bytes/record That's not consistent either. The storage is not a fixed number of bytes per record in the database, it's more like a fixed number of bytes per record entered since the last checkpoint. For further details see <http://www.sqlite.org/draft/wal.html#ckpt> 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 <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. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
