Modification of big index-tree is disk-expensive operation.
You can try to insert pre-sorted data. Did yoy search only
by equals conditions? Did you think about packing a set
of values in single string or blob like to
Insert into t1 values ('1 2 3 4 5 6 7 8 9');
You can search by string content using FTS3/4 index.2012/2/9 Valentin Davydov <sqlite-u...@soi.spb.ru>: > Hi, All! > > I've got a database containing single table with a dozen of columns and some > indices like that: > > CREATE TABLE T(X, Y, Z, ..., UNIQUE (X, Y, Z, ...) ON CONFLICT IGNORE); > CREATE INDEX IX ON T(X); > CREATE INDEX IY ON T(Y); > CREATE INDEX IZ ON T(Z); > ..... > > Data in the different columns are small positive integers, 32-bit integers > or few-bytes blobs. Neither of the X, Y, Z, ... are unique by themselves, > only their combination is unique (that's why I opt using relational database > to process them). My application treats this table as append-only, that is, > doing either INSERT or SELECT on it, without any UPDATEs. Few millions of > inserts are wrapped in a single transaction in order to reduce journal usage. > Total number of records in the table is more than 10^10, so it doesn't fit > in any RAM. PRAGMA CACHE_SIZE is adjusted to use most of the available memory. > Journal file (of enough size to hold all the pages dirtied by a biggest > transaction) is created in advance and PRAGMA JOURNAL_MODE is set to PERSIST > in order not to bother operating system with creating/deleting files. Page > size is matched to the underlying filesystem block size and to the stripe > size of RAID containing that filesystem. Sqlite version is now 3.7.3, but > it seems that exact version doesn't matter. > > When trying to insert data already present in the table, performance is > fairly well, most of the CPU time is spent only on parsing SQL statements > and converting data to internal format, while database operation itself > (that is checking data against the constraint) is almost instantaneous, > which is quite impressive given the table size. But when the application > inserts new data, things change drastically: total throughput drops by a > 2-3 orders of magnitude. CPU is staying almost idle, and all time is spent > waiting for disk _reading_ (rarely interspersed with fast and happy write > bursts on each COMMIT). What is sqlite reading there? Does it try to > perfectly balance each index on each insert (million times per > transaction) or something else? > > Sincerely, > Valentin Davydov. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
