Re: HPL vs. Select Into

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