>I see. However, giving up on indexes kind of defeats the whole >purpose of having a database.
>I assume there is no way to leverage the fact that e.g. rows would be >inherently sorted by timestamp, without recurring to indexes. There kind of is. You can do this using a bit of funny business by "encoding" the timestamp into the rowid and storing the incoming data at row numbers rather than just using the default ascending rowid. For example, lets assume that you have a fixed number (or allow for a fixed number) of data sources (source points) and that your timestamp is an arbitrary integer such that you can do some manipulation without overflowing -- it can be for example integer milliseconds since the Unix Epoch for example. The rowid number is 64 bits in SQLite (63 since we don't want to deal with the sign and all integers in SQLite are signed). __int64 rowid; // Generate a unique rowid by combining the scan time with the source rowid = timestamp; // up to 47-bit unsigned timestamp rowid <<= 16; // shift the timestamp over rowid += sourcetag; // store the upto 16-bit (unsigned) integer sourcetag# in the rowid INSERT INTO ... (rowid, ...) values (?, ...); binding the computed rowid. When you append the data into the table it will automatically be in order by timestamp. You can still store the timestamps and sourcetag numbers seperately if you wish, but now you can use the rowid to get a range of rows based on the timestamp (and to delete based on age for example) since you can now compute the range of rows to scan without using an additional index. This assumes, of course, that the computation to arrive at the rowid from incoming data will generate unique rowid's. Presuming that the timestamps are ascending and all the same for each scan there is not much difference between this and using the default incrementing rowid, particularly if you can batch insert the rows -- you have just overloaded what would otherwise be simple ascending numbers for the rowid with simple ascending numbers that have an overloaded meaning. You would not want to encode the sourcetag into the highorder bits of the rowid as this would mean that you are not really merely "appending" to the table ... To obtain purely the same performance as just "appending rows" to a rowid table, you should sort the insert data in each batch to make sure the inserts are in ascending order of rowid. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
