[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] serious performance problems with indexes
From:       "Scott Hess" <shess () google ! com>
Date:       2006-10-31 18:34:53
Message-ID: 696e4b7c0610311034i3653bfeai8a93669e14e5d4e5 () mail ! gmail ! com
[Download RAW message or body]

On 10/30/06, Joe Wilson <developir@yahoo.com> 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 sqlite-users-unsubscribe@sqlite.org
-----------------------------------------------------------------------------

[prev in list] [next in list] [prev in thread] [next in thread]