On Fri, Nov 15, 2013 at 7:33 AM, RSmith <rsm...@rsweb.co.za> wrote: > > Yes there would be a space-saving, but it is rather minimal. The real > advantage is removing one complete lookup reference cycle from a Query... >
That was my original theory too. But experimental evidence inverts this. There is a program called "wordcount.c" (see http://www.sqlite.org/src/artifact/2c2cc111?ln) that tests the performance of WITHOUT ROWID. It constructs a table like this: CREATE TABLE wordcount( word TEXT PRIMARY KEY, cnt INTEGER ) WITHOUT ROWID; -- "WITHOUT ROWID" optional And then reads a large text corpus and applies code like the following to each contiguous sequence of alphabetic characters: INSERT OR IGNORE INTO wordcount VALUES($new,1) -- if the previous was a no-op, then also do: UPDATE wordcount SET cnt=cnt+1 WHERE word=$new In this way, it constructs a table that contains a single entry for each word in the corpus, together with a count of the number of occurrences of that word. Applying this to the complete text of the King James Bible (791319 words of which 13539 are distinct) both with and WITHOUT ROWID gives results like this: with rowid: database size = 481280 bytes, time = 3.569 seconds without rowid: database size = 218112 bytes, time = 3.299 seconds So you can see that insert performance is a little faster (about 8%) but the more noticeable change is that the database is less than half its original size. So the big winner here is database size rather than performance. Some queries are faster with a WITHOUT ROWID table. For the example above and queries of the form: SELECT cnt FROM wordcount WHERE word=$word; A rowid table needs to do two lookups whereas a WITHOUT ROWID table can get by with only one. However, the second lookup of a rowid table is on a B*-tree table with integer keys and is therefore very fast, so the total speedup is only about 30%, not 50% as you might expect. In summary: Reduction in CPU time: 5% to 30% Reduction in disk used: up to 55% -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
