Re: Copying across databases

Hi Andy, I'm unsure as to the reason... But, I prefer to: Unload to a 'compress' and 'split' pipeline to make the file(s) as small as possible, then rcp them to the destination and import them in reverse manner (ie, 'cat' and 'uncompress' to a pipeline). It is pretty efficient, and you don't have to worry about the maintenance aspect of an unsupported,yet gratefully offered product. If you use 'unload to', remember to use the column order provided by 'info columns for Table' just in case the original table was alter'd and columns were added 'before' other columns. Reason for this is because a 'select *' from the table does not order the columns in the same order as dbschema (7.31). Anyways, it is better to use 'select col1,col2,... from Table' and do likewise for 'insert into'. Do you drop and recreate the destination table before the import? Also, do you disable constraints, indexes, and triggers on the destination beforehand? Regards "Andy Kent" <andykent.bristol1095@v To: informix-list@iiug.org irgin.net> cc: Sent by: Subject: Copying across databases owner-informix-list@iiu g.org 08/10/2004 04:38 AM Please respond to "Andy Kent" 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