On 10/30/06, Joe Wilson <[EMAIL PROTECTED]> wrote:
If you have only one index, then pre-sorting your large datasets prior to
inserting with the default sqlite cache will yield twice as good insert
performance as not pre-sorting your data and using a huge cache.
This stands to reason since you're basically appending pages to the end
of the database file. (I'm assuming it's just an append since the pre-sorted
inserts' timing is constant regardless of the number of rows).
But if you wish to maintain more than one index and still have good insert
performance with huge datasets, then there's not much else you can
do other than using some combination of the techniques already discussed,
or use another database or some other form of data storage altogether.
You can also take the (somewhat crazy) step of rearranging your tables
so that all indices are sorted. If you have:
CREATE TABLE x (
name text primary key,
office text,
key(office)
);
You can't keep the name and office both sorted. But if you had:
CREATE TABLE x (
name text primary key
);
CREATE TABLE y (
office text primary key,
xid integer
);
you could insert into both tables in sorted order in a single
transaction. At the cost of taking up a bit more space, and making
all your SQL uglier, etc. Really a hack, though.
-scott
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
