Re: (Fwd) DBI Performance for SQL Server

  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.