Re: Copying across databases

On Tue, 10 Aug 2004 04:38:51 -0400, Andy Kent wrote: You can speed up the SELECT ... FROM ... INSERT INTO ... by: - Drop all indexes on the target table. - Set FETBUFSIZE=32767 in the environment before starting dbaccess/sqlcmd - Set PDQPRIORITY=<as high as you dare but at least 2> - Make sure stats are up-to-date on the source - Run connected to the source server NOT the target It also depends on your version. In IDS versions <7.30/9.20 dbcopy was MUCH faster than select/insert, in later versions Informix improved some of the internals (perhaps to compete? ;-) The gap is not so great anymore, especially on single processor machines (dbcopy works best on MP machines). Another thing you can try is to run the dbaccess session on a third physical machine, neither the source nor the target. This can help a lot if you are running on only one or two CPUS per machine. Art > Is INSERT INTO .. SELECT FROM inherently slow across databases? Does it keep > having to re-make database connections or something? I am getting > startlingly slower performance attempting to copy a big table across > databases using this method compared to other methods. > > My client has either tried or considered: - dbexport / dbimport - until he > hit the 2Gig limit on unload. - Art's dbcopy utility - until we discovered > he has the wrong kind of C compiler. (It needs ANSI, he has the crippleware > one) > > Of course there are all sorts of reasons why a table copy might go slowly > but what I want to understand before deciding whether to try a different > method (ipload, onload, get him to buy the right C compiler to use Art's > utility ... ) is why dbexport+dbimport were considerably quicker than INSERT > INTO .. SELECT FROM. > > Can anyone shed any light on what might be going on and either confirm or > refute the 'repeated connect' theory, as well as nominating their preferred > way of doing the job? An in-place method would be nice rather than one that > dumps and re-imports. > > It's 7.31 on HPUX 11. > > Many thanks >