RE: Copying across databases

you may try running multiple occurrences of INSERT INTO ..SELECT FROM with different ranges in the where clause, what i mean to say is, you can write a simple script to identify the ranges for one of the columns in the source table (make sure it has an index on it), then spawn multiple INSERT INTO ...SELECT FROM ... at the same time with different ranges in the where clause.... make sure to set the PDQPRIORITY appropriately (i.e. if you are running 5 concurrent insert statements with different ranges, set PDQPRIORITY to 20 for each session). you will also get good performance if your target database is not logged, or if you create the target table as a raw table and then alter the table to change it from raw to permanent after inserting the data. -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]On Behalf Of Andy Kent Sent: Tuesday, August 10, 2004 3:39 AM To: informix-list@iiug.org Subject: Copying across databases 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 -- Genuine reply address. Andy Kent Bristol, UK sending to informix-list