On 21 Sep 2012, at 12:51pm, _ph_ <hauptma...@yahoo.com> wrote: > I've seen that the timre required to create that snapshot depends largely on > the size of the entire table, even if only selected columns go into the > snapshot. > > I.e. > > ATTACH ':memory:' AS mem; > INSERT INTO mem.Snapshot SELECT col1, col2 FROM Data; > > is much slower if 'Data' contains an additional column with large data. > Moving my item meta data (small) into a table separate from the possibly > large blobs helped immensely.
As an alternative to moving the BLOBs out, it may speed up a lot of you make an index which contains just columns col1 and col2. Do this just one CREATE INDEX fastCol1Col2 ON Data (col1,col2); ANALYZE Then do INSERT INTO mem.Snapshot SELECT col1, col2 FROM Data ORDER BY col1,col2; SQLite will realise it can get all the data it needs from the index it's already using. It will never read the TABLE data at all. However the actual speed advantage you get it strongly tied to your hardware setup, so do some testing first to see if it helps as much as I think it will. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
