Slow dbcopy?

How fast should dbcopy be? :D We have used dbacces and plaing old select-insert statements to copy between ver. 5 and 7 before, so I figured dbcopy would be faster: Table Name security Row Size 75 Number of Rows 30118 Number of Columns 8 This is a 10Mb WAN that delivers approx. 600KB at this time of day. time ~thomasp/bin/dbcopy -F -h trajan -H vpsdbs1_obi_prod -d obi -D ob i -t security -T security Warning setting PDQPRIORITY on source: -201, 0 Selecting data from obi@trajan with: SELECT * FROM security; Block flush enabled. Blocks contain 431 rows. Rowsize is 76 bytes. Inserting data to obi@vpsdbs1_obi_prod with: INSERT INTO security ( sid, sec_type, symbol, isin, isin_subcode, sec_name, fm_date, to_date ) values ( ?, ?, ?, ?, ?, ?, ?, ? ); Input: 30118 records. Copied: 30118 records to obi@vpsdbs1_obi_prod:security. Logged: 0 records to error log. real 3m49.328s user 0m3.970s sys 0m4.210s Thomas

Thomas Parsli wrote: > > How fast should dbcopy be? :D Ideally about 3X the speed of INSERT INTO...SELECT FROM... going from 7.xx to 7.xx with the dbcopy running on either the source or target machine (it can run on a third machine, see below). Unfortunately 5.xx does not support Fetch Array or the large communications buffers needed for the -F option to work to best advantage so the speed will be only slightly faster than using dbaccess to do the copy. The remaining speedup is mainly due to code optimized for this kind of thing rather than the generic INSERT and SELECT code inside the engine which must cope with the unusual request to INSERT from the result set of a SELECT. Dbcopy may be slower if it is running on a third machine, from the source and target, or if the source and target tables are on the same server and database, unless the network is very fast, because it must move the data off the server into its own address space over the network and back again, while the engine can eliminate one level of this copying across the net for INSERT .... SELECT in these two cases. In your situation the main advantage of using dbcopy is the partial commits which prevent long transaction rollbacks, keeping huge numbers of locks and running out of locks, and reduced server overhead so other tasks are less impacted. This means you can either break the copy up to more partial copies than you could using dbaccess or you can copy more tables concurrently. Having said all that you are not doing too badly. You inserted 131.5 rows per second using a single process. It is not easy to insert much over 100 rows per second with a single task in the presence of indexes normally so without an INSERT...SELECT run to compare to I'd say dbcopy was doing a great job! Of course I'm biased. Also note that the 'time' output shows that most of the elapsed time was spent waiting, probably for the network, and only about 8.2 seconds was actual processing time! I am in the process of using dbcopy to move a table from a 5.06 server to a 7.31 server on a much faster machine. I am currently clocking the copy at ~150 rows of 148 bytes per second and currently ftp copies from the source to the target at 480KB/sec. Anyone else who can help Thomas judge by posting results either of dbcopy or pure SQL? Art S. Kagel PS - I do the same but the -d obi -D obi and -t security -T security are redundant. The -d and -D default to each other as do -t and -T so you can save a bit of finger wear if the database and table names are the same on both the target and source. FWIW. > We have used dbacces and plaing old select-insert statements to copy between > ver. 5 and 7 before, so I figured dbcopy would be faster: > > Table Name security > Row Size 75 > Number of Rows 30118 > Number of Columns 8 > > This is a 10Mb WAN that delivers approx. 600KB at this time of day. > > time ~thomasp/bin/dbcopy -F -h trajan -H vpsdbs1_obi_prod -d obi -D ob > i -t security -T security > Warning setting PDQPRIORITY on source: -201, 0 > Selecting data from obi@trajan with: > SELECT * FROM security;> > Block flush enabled. Blocks contain 431 rows. Rowsize is 76 bytes. > > Inserting data to obi@vpsdbs1_obi_prod with: > INSERT INTO security ( > sid, > sec_type, > symbol, > isin, > isin_subcode, > sec_name, > fm_date, > to_date > ) values ( > ?, ?, ?, ?, ?, ?, ?, ? );> > Input: 30118 records. > Copied: 30118 records to obi@vpsdbs1_obi_prod:security. > Logged: 0 records to error log. > > real 3m49.328s > user 0m3.970s > sys 0m4.210s > > Thomas

"Art S. Kagel" <kagel@bloomberg.net> writes: > Thomas Parsli wrote: > > > > How fast should dbcopy be? :D > > Ideally about 3X the speed of INSERT INTO...SELECT FROM... going from 7.xx > to 7.xx with the dbcopy running on either the source or target machine (it > can run on a third machine, see below). > > Unfortunately 5.xx does not support Fetch Array or the large > communications buffers needed for the -F option to work to best advantage > so the speed will be only slightly faster than using dbaccess to do the > copy. We're trying the unload->rcopy->load path, this seems faster on the relativly small tests I've done. I suppose gzip could help with our 10Mb WAN... > Dbcopy may be slower if it is running on a third machine, from > the source and target, or if the source and target tables are on the same > server and database, unless the network is very fast, because it must > move the data off the server into its own address space over the network > and back again, while the engine can eliminate one level of this copying > across the net for INSERT .... SELECT in these two cases. After some casting, changing bzero to memset and using aCC instead of cc (yuck) -I managed to compile on our HP (HP-UX B.10.20)=8) It doesn't seem noticable faster running on the target db-machine than on my Portable (P 400MHZ) running Linux (on 10Mb ethernet). > Having said all that you are not doing too badly. You inserted 131.5 rows > per second using a single process. It is not easy to insert much over 100 > rows per second with a single task in the presence of indexes normally so > without an INSERT...SELECT run to compare to I'd say dbcopy was doing a > great job! No indexes or logs on target DB:) > Of course I'm biased. Also note that the 'time' output shows > that most of the elapsed time was spent waiting, probably for the network, > and only about 8.2 seconds was actual processing time! Our WAN is normaly unused at that time, and I did try some copying. I got the same speed inserting into Informix on my laptop too -and that's 10Mb LAN. Thanks btw! Thomas