Hello,
your best bet is to first declare the database without indexes, then load all the data, then add the indexes afterwards.
Indeed.
Just for the kicks, I've tested with 100K records. These are the results:
No indexes: 21 sec. Indexing while inserting: 50 sec. Indexing after inserting: 37 sec.
-- Tito
On Jun 20, 2004, at 23:38, Darren Duncan wrote:
At 4:42 PM -0400 6/20/04, Edward Bacon wrote:The operation starts off loading data at about 500K/sec, but drops to about 50K/sec over 30 minutes and stays around there. (If it comes through, see attached) At that point about 1.2GB of the 3.7GB has loaded. It takes about 16 hours to complete the full 3.7GB.
Without the index, the whole 3.7GB loads in about 20 minutes.
Can the index be configured to be kept more in memory? Can it be kept in a different file and would this help (I suspect if and only if the file is on a different physical disk)?
Since huge loads like this aren't very common in the life of a single database, your best bet is to first declare the database without indexes, then load all the data, then add the indexes afterwards. I believe the slowdowns you are experiencing is from the huge number of updates to the index itself, including constant restructuring. The index would be built a lot more cleanly if it was done after all/most of the data was in place, I believe. -- Darren Duncan
P.S. I think this same tip will optimize any brand of database.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
