On 08/26/10 10:59 AM, Tim Bunce forwarded....:
Hope you are doing good. I am a software programmer struggling to solve the
problems in my code.
Pls. find below the summary of my problem
1. I am migrating data from MSSQL to MSSQL DB.
2. I am fetching data from source DB, manipulating Data and inserting into
Target DB.
3. Data manipulation involves some complex business logic.
Note: The SQL statements are prepared. (More than 100 tables are present)
a. Data have to be queried from TABLE X, Y and Z respectively
and the sequence will change according to business logic.
b. One row will be fetched from source and inserted to target at a time.
4. Data model between two DBs are different.
5. I am using Perl, DBI::ODBC to migrate the data.
6. I can only able to transfer 100, 000 records in a period of 60 minutes.
The performance is very slow and I am struggling to overcome the problem.
Can you pls. ignite some light in my problem ?
In case of any queries, pls. revert back.
Thank you very much
Regards,
Mahesh
Doing 1 single insert per transaction will greatly slow things down too,
as it forces a disk commit on each transaction. if you can organize
your code so you batch some number of transactions per commit, you
likely will speed things up.
however, what Brian said was on my mind too... Microsoft SQL Server
provides a rather powerful tool for this exact sort of job. you can
implement business logic within the data extraction system using any
.NET type language. I'd run this stuff on the source server if you're
reading more than you're writing, and on the destination server if
you're writing more than you're reading.
Without a WHOLE lot more specific information, its impossible to give
any better advise. "100+ tables", 'sequence of queries changes
according to the business logic', all leaves lots of gaps. Is the
source database reasonably well normalized, and you use proper joins to
query it? Is the destination database normalized? or do you just
read everything into your perl, mash it about, then insert into your
target tables?
ODBC is not the fastest way to access MS SQL Server, either, as it adds
additional translation and abstraction. ADO is generally quite a bit
faster.
