If you use named pipes to unload and load the data in parallel (one
onpload writing to a fifo and another onpload loading from the same
fifo) you can move a large table pretty quickly. This strategy has the
added benefit of eliminating the intermediate step (and bottleneck) of
writing an unload file to disk (much like the insert into select *
does). You can use multiple named pipes in a device to take advantage
of multiple CPUs, modify the onpload.std config parameters and configure
a No Conversion job to boost performance.

I did this recently with the following results on a 4 CPU machine with
disk attached to a SAN:


Number of rows: 45 million
Row size: 357 bytes (includes a varchar (250))
Unload/reload time: 25 minutes

I doubt you can get insert into select * from to outperform this.

Andrew Ford



-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of Superboer
Sent: Tuesday, October 03, 2006 3:12 AM
To: informix-list@iiug.org
Subject: Re: HPL vs. Select Into


HPL in express mode is the fastest. test it you'll see.

when you create the target tables make sure the first/next extent size
are properly configured.
and a checkpoint has been done before loading.


Superboer.

Tam OShanter schreef:

> Hello,
>
> Looking at moving a table with many millions of rows to a new DB
Space.
>
> Question being, will the move be faster using HPL that doing something
like
> "Select * from into......."?
>
> Why, why not?
>
> Thanks for the advice friends,
>
> Tam.

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Ford, Andrew G schrieb:
> If you use named pipes to unload and load the data in parallel (one
> onpload writing to a fifo and another onpload loading from the same
> fifo) you can move a large table pretty quickly. This strategy has the
> added benefit of eliminating the intermediate step (and bottleneck) of
> writing an unload file to disk (much like the insert into select *
> does). You can use multiple named pipes in a device to take advantage
> of multiple CPUs, modify the onpload.std config parameters and configure
> a No Conversion job to boost performance.
>
> I did this recently with the following results on a 4 CPU machine with
> disk attached to a SAN:
>
>
> Number of rows: 45 million
> Row size: 357 bytes (includes a varchar (250))
> Unload/reload time: 25 minutes
>
> I doubt you can get insert into select * from to outperform this.
>
[... snip ... ]

sry, I know, I should not, but I must:

run bonnie++ on your SAN, because:
I guessing that your table RPP (rows per 2Kpage) is 6 (mayhaps even 7)
-->
you were moving like 5000 2Kpages per second and this is not too fast...

On a failry modern SAN box you should be able to move like 45000 2Kpages
per second!

Either your SAN box is slow or you did not saturate your disks
(driving your Porsche 656 in 2nd gear only
The latter you can easily avoid by fragmenting your table and
using same number of dev arrays in HPL as you have fragments, as that
will give you parallel disk access way beyond the point of
disk thruput saturation - which is a good thing, because you want
to use the hardware you have in place.

Another thing you can try is to estimate the memory throughput of
your box, because this tends to be a limiting factor more and more,
especially on those machines said to be fast (like big iron SMP
machines) and after a short while of comparing you see the bitter
truth: a CoreDuo in a laptop has a memory thruput which is ......
(pls see for yourself, I do habe to avoid legal problems)
And then, if you really want to see speed: look at a 4way
dualcore opteron system!

dic_k

--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe