Re: Copying across databases
--0-252203315-1092150249=:40842
Content-Type: text/plain; charset=us-ascii
It established one connection/session and maintains that session untill the work is done, it is an implicit transation.
The approach I like to use is:
Capture all aspects of the target table, indexes, constraint, FK's that reference it, triggers, etc.
ALTER TABLE target TYPE (RAW); -- Eliminates logging on this table and use only a few locks, cannot have indexes, uses light appends, could cause lengthy checkpoints.
LOCK TABLE source IN EXCLUSIVE MODE;
INSERT INTO target SELECT * FROM source;
ALTER TABLE target TYPE (STANDARD);Add indexes and contraints, etc.
Update stats.
Andy Kent <andykent.bristol1095@virgin.net> wrote:
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
---------------------------------
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
--0-252203315-1092150249=:40842
Content-Type: text/html; charset=us-ascii
<DIV>It established one connection/session and maintains that session untill the work is done, it is an implicit transation.</DIV>
<DIV> </DIV>
<DIV>The approach I like to use is:<BR></DIV>
<DIV>Capture all aspects of the target table, indexes, constraint, FK's that reference it, triggers, etc.</DIV>
<DIV>ALTER TABLE target TYPE (RAW); -- Eliminates logging on this table and use only a few locks, cannot have indexes, uses light appends, could cause lengthy checkpoints.</DIV>
<DIV>LOCK TABLE source IN EXCLUSIVE MODE;</DIV>
<DIV>INSERT INTO target SELECT * FROM source;</DIV>
<DIV>ALTER TABLE target TYPE (STANDARD);</DIV>
<DIV>Add indexes and contraints, etc.</DIV>
<DIV>Update stats.<BR><BR><B><I>Andy Kent <andykent.bristol1095@virgin.net></I></B> wrote:</DIV>
<BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Is INSERT INTO .. SELECT FROM inherently slow across databases? Does it keep<BR>having to re-make database connections or something? I am getting<BR>startlingly slower performance attempting to copy a big table across<BR>databases using this method compared to other methods.<BR><BR>My client has either tried or considered:<BR>- dbexport / dbimport - until he hit the 2Gig limit on unload.<BR>- Art's dbcopy utility - until we discovered he has the wrong kind of C<BR>compiler. (It needs ANSI, he has the crippleware one)<BR><BR>Of course there are all sorts of reasons why a table copy might go slowly<BR>but what I want to understand before deciding whether to try a different<BR>method (ipload, onload, get him to buy the right C compiler to use Art's<BR>utility ... ) is why dbexport+dbimport were considerably quicker than INSERT<BR>INTO .. SELECT FROM.<BR><BR>Can anyone shed any light on
what might be going on and either confirm or<BR>refute the 'repeated connect' theory, as well as nominating their preferred<BR>way of doing the job? An in-place method would be nice rather than one that<BR>dumps and re-imports.<BR><BR>It's 7.31 on HPUX 11.<BR><BR>Many thanks<BR><BR>-- <BR>Genuine reply address.<BR><BR>Andy Kent<BR>Bristol, UK<BR><BR><BR></BLOCKQUOTE><p>
<hr size=1>Do you Yahoo!?<br>
Yahoo! Mail is new and improved - <a href="http://us.rd.yahoo.com/mail_us/taglines/new/*http://promotions.yahoo.com/new_mail">Check it out!</a>
--0-252203315-1092150249=:40842--
sending to informix-list