PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: How can I get the speed I need?
Date: Jul 20 1999 2:19PM

AGHZHONG@aol.com writes:
 > I need to move massive amount of data (7 million rows) from one database to 
 > another.
 > The two databases have different tables and different columns.  I cannot do a 
 > straightforward bcp in.
 > 
 > The only approved method right now is to bcp out from one database and read 
 > the data file row by row and then insert into various tables to the other 
 > database.  
 > This has to be done as fast as possible.
 > 
 > For a bulk estimate on how long the process will take,  I tried a simple 
 > case.  A 2000
 > row data file is read row by row and then converted column by column and 
 > finally insert into a (one for now) table using SybTools (built on top of 
 > Sybase::CTlib) ExecSql function call one row a time.  The result is very 
 > disappointing. I can only insert about 20 rows a second.  (Most of time is 
 > indeed spent on insertion,  not on those string manipulations at all.)  

Drop the indexes on the target tables. This should make things faster
(or, like others have suggested, use views and bcp, or use sybperl to
extract the data, format correct bcp files for the target database,
and bcp these back in.)

For that last option you could even create a named pipe, and use write 
to the named pipe from the perl script and read from the named pipe in 
the bcp process (so both processes can run in parallel)

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@peppler.org    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com