RE: Copying across databases

These are pretty obvious but: Did you drop indexes before doing the insert? Lock the table in exclusive mode so you don't have to lock every row/page? If you have adequate hardware resources I would use the HPL. Take advantage of the parallelism. Also eliminates the 2GB file size limit since you can unload to multiple files. Regards, Bill > -----Original Message----- > From: Andy Kent [SMTP:andykent.bristol1095@virgin.net] > Sent: Tuesday, August 10, 2004 4: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