Radzi,
are the ids of the Transaction table ordered when inserted?
I have discovered that it is very bad for performance of huge
tables, if the rows are inserted with random ids. If you use
an integer id (primary key )for such a table, SQLite uses the
ROWID column to store the integer primary key. SQLite will
put the records physically in the order you insert them but
logically in ROWID order.
Suppose you insert the following data:
id data
9 -- disk 1
6 -- disk 2
8 -- disk 3
1 -- disk 3
5 -- disk 5
2 -- disk 6
7 -- disk 7
4 -- disk 8
3 -- disk 9
The recorders are on disk in order 'disk 1' .. 'disk 9'.
But SQLite accesses the in id order. If the table is huge,
then the head of your hard disk jumps around like crazy.
When you create an index, SQLite uses the id order to access
your entries. This takes for ever.
If you can order the data on id before you insert should dramatically
speed up the indexing. If this is not possible, don't make the id column
primary key, but create an index for id instead.
I wonder how this would change the performance of your application....
Michael
Thanks for the suggestion. I'm a bit lost now. I've tried to load
80million rows now. It took 40 minutes to load into non-index tables;
but creating index now take almost forever. It's already 12 hrs, not yet
complete.
regards,
Radzi.
----- Original Message ----- From: <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Sunday, December 03, 2006 8:21 PM
Subject: Re: [sqlite] How do I speed up CREATE INDEX ?
"Mohd Radzi Ibrahim" <[EMAIL PROTECTED]> wrote:
Hi,
I was loading a file to sqlite (3.3.8), and it took 4 mins to load 6
million rows (with no index). But then when I run CREATE INDEX it
took me 40 mins to do that. What could I do to speed up the indexing
process ?
The reason index creation slows down when creating large
indices is a problem with locality of reference in your disk
cache. I've learned a lot about dealing with locality
while working on full-text search, and I think I can
probably implement a CREATE INDEX that runs much faster
for a large table. There are some plans in the works
that might permit me the time to do this in the spring.
But in the meantime, the only thing I can suggest is to
add more RAM to your machine so that you disk cache is
larger. Or get a faster disk drive.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
