Re: optimising data load

On Thu, 2002-05-23 at 21:10, John Taylor wrote:
> >
> > Oh, ok.  How about creating an intermediary, indexless table
> > that the 300,000 records will be inserted into.  Then, after
> > the intermediary table is populated, drop the index(es) on
> > orderlines and INSERT INTO orderlines SELECT * FROM intermediary;
> >
>
> That sounds a good idea.
> Would it be quicker to copy out from the intermediary, and then copy in to the real one ?
> I guess I'll have to experiment.

It will be quickest to:

"INSERT INTO orderlinesupdate SELECT * FROM intermediary", like Ron said

No COPY involved, except to get the records into the intermediary table
initially.

That syntax on the INSERT statement is _really_ useful for this sort of
thing.


> I have a feeling a lot of the problems may be due to the indexes.
> I've been experimenting with an easier table.
> copy in to the live takes 5 minutes for 850000 records
> copy in to the updates takes 2 hours !
> It is the same data with 3 extra columns, but there are an additional 2 indexes.
> I think it must be the indexes making the diffrence. I'm just checking now.

Also consider that if the numbers of records inthe table is changing a
lot, then it is well worth while doing a Vacuum analyze after time, so
the planner statistics get updated and the system produces queries
appropriate to a 400k record table (e.g.) rather than a 100k record
table.

Regards,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?