On 9 Nov 2011, at 3:23pm, Fabian wrote: > I'm having an issue where inserts on an un-cached database are very slow. > The reason probably is that a large part of the existing index needs to be > read from disk, to be able to insert new rows to the index.
Are you doing a lot of INSERT commands one after another ? Things will go far faster if you wrap them in a transaction: BEGIN TRANSACTION; INSERT ...; INSERT ...; INSERT ...; END TRANSACTION; > The length of > the values in the indexed column are around 60 bytes, so I'm thinking about > adding an extra column, containing a shorter hash (64bits) of the actual > value, and move the index to that column instead. This way the total size > of the index (and the database) will be much smaller, hopefully resulting > in faster inserts. > > But I'm wondering if SQLite can deal more efficiently with a INTEGER index > (64bits) VS an 8-byte TEXT column (also 64bits). I know the INTEGERs > require less disk-space because SQLite can store smaller values in fewer > bytes, but are there any other differences that make them more preferable > as an INDEX? Yes. It is far faster to compare two integers than it is to compare two strings. This will speed up everything to do with the index, and make the index take up less space on disk. Of course, you will have to take the time to calculate a hash value for each string. > If there is no difference in performance, I could just take > the first 8 characters of the TEXT column as a hash-value, instead of > calculating a CRC64 each time. This would be a very fast HASHing calculation. If it's easy to program, then it might be worth a try. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
