Micha Bieber <[EMAIL PROTECTED]> wrote: > Appending all 25*10^6 rows takes 40 > minutes on a PC with 3 GHz, 1GB memory and sufficient large harddisk. >
Inserting 25-million rows in a table should be quick. Building an index (or indices) on a 25-million row table takes much longer. You did not show us the schema. No doubt you have one or more indices, either inplicit or explicit. Building the indices separately after the table has been constructed tends to be faster - but only by a constant factor. This issue of building huge tables keeps coming up. Is SQLite really showing performance problems here? Is this something that we need to work on? Or is it just a hard problem? I'm curious to know how long it takes PostgreSQL/MySQL/Firebird to insert 25 million rows into the same table. The way indices work in SQLite is that there is one row in the index for each row in the table but the index rows are in index order. If the indexed values are randomly distributed in the table, that means building the index requires inserting each row in a random spot in the middle of the index. Constantly inserting things in random places means that there is no locality of reference and the pager cache does not perform well. It is not clear to me what can be done about this other than to completely redesign how indices work. And even then, I cannot think of an alternative design that would do much better. -- D. Richard Hipp <[EMAIL PROTECTED]>
