----- Original Message -----
From: "P Kishor" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Sunday, December 03, 2006 10:42 AM
Subject: Re: [sqlite] How do I speed up CREATE INDEX ?
On 12/2/06, Mohd Radzi Ibrahim <[EMAIL PROTECTED]> wrote:
----- Original Message -----
Because I'm going to do it 24 times. I have 24 monthly files each of
which
about the same size. A faster computer is not an answer at this time.
unless you are doing something really wrong (creating the wrong
indexes, for example), there is nothing wrong about CREATE INDEX
taking a relatively long time. That is the price you pay... you pay it
once, when creating the index. After that, your queries are quick
because they use the index. There really is no way around that.
I have 5 simple tables:
1. Transaction (id, NoOfPack, NoOfDX, NoOfOps, NoOfCure)
2. Cure(transID, cureID)
3. Diag(transID, diagID, Sequence)
4. Ops(transID, opsID)
5. Pack(transID, packID, qty, amt)
Tansaction has primary key on ID, and I've created index on each detail
tables (Cure, Diag, Ops and Pack - one-to-many relationship with Trans)
after loading the data. The indexes are Cure(CureID, TransID), Diag(diagID,
transID), Ops(opsID, transID), Pack(packID, transID). Same goes with the
other tables.
There are 6million rows in Pack, 2 million in Trans.
I was quite impressed with the loading part. Even faster than MSSQL Bulk
Insert. But the indexing part is much slower than MSSQL. I got a better
result (12 min) when using PRAGMA CACHE_SIZE=20000. That probably match the
time for MSSQL. Increasing to 40000 does not show any improvement.
regards,
Radzi.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
