Re: optimising data load

On Thu, 2002-05-23 at 02:01, John Taylor wrote:
> On Thursday 23 May 2002 03:40, Ron Johnson wrote:
> > On Wed, 2002-05-22 at 16:05, John Taylor wrote:
> >
> > > So, how would I merge the data from the view with the data to copy in ?
> >
> > ??? Unless I am misunderstanding you:
>
> I think you are misunderstanding me.
> I have 300,000 rows to insert.
> They each require a different query to obtain the correct data to insert.

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;

> >
> > 0. create view v_new_orderlines (theorder, type, stock, line,
> > ordercurrent, sellingquant, price, discount, vatrate, comment)
> > as SELECT oh.theorder, 'P', '0310', coalesce(ol.line+1,1), 5, 0,
> > .52, 0, 0, ''
> > FROM orderheader oh
> > LEFT OUTER JOIN orderlines ol
> > ON oh.theorder = ol.theorder
> > WHERE oh.account=' MILN1'
> > AND oh.delivery=1
> > AND oh.thedate='2002-06-01'
> > AND oh.ordertype='O'
> > ORDER BY ol.line ;
> > 1. COPY v_new_orderlines to '/tmp/new_orderlines.tsv';
> > 2. DROP each index from orderlines
> > 3. COPY orderlines from '/tmp/new_orderlines.tsv';
> > 4. CREATE each index on orderlines
> >
>
> JohnT
--
+---------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://ronandheather.dhs.org:81 |
| |
| "I have created a government of whirled peas..." |
| Maharishi Mahesh Yogi, 12-May-2002, |
! CNN, Larry King Live |
+---------------------------------------------------------+