Best approach for Data transfer of large number of rows

Question

Post reply

Best approach for Data transfer of large number of rows

Megha P

SSCommitted

Points: 1976
More actions
April 3, 2014 at 12:28 am

#289998

Hello,
we have our database in sql 2005 version Enterprise Edition .
Because of performance issue we have normalized database and now required data transfer from denormalized database to normalized database.all tables has been created.
Denormalized database have 429 tables, some of them have greater than 5 millions rows.
In this condition one table data may needs to be transferred in 3 different tables of normalized database.
which would be the best approach to do data transfer from one database to another
i.e script or ssis package..?
Thanks..

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

If the databases are on the same server, you can use SQL scripts.

Make sure logging is minimized to reduce overhead.

What kind of performance issues were you having with the denormalized system?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Megha P SSCommitted Points: 1976 More actions · Answer 2

Actually, we have tables with more than 150 columns and it's harder to manage it (insert) .

so tables are denormalized now.

source and destination databases are on different server.

Thanks,

megha

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Megha P (4/3/2014)
Actually, we have tables with more than 150 columns and it's harder to manage it (insert) .
so tables are denormalized now.
source and destination databases are on different server.
Thanks,
megha

If they databases are on different servers, I would go for SSIS.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Megha P SSCommitted Points: 1976 More actions · Answer 4

ok...thanks..SSIS will be fast over script in case of different server data transfer?

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

In addition to Koen; you can also drop all constrains on the destination table if no. of rows are more which will expedite the process. Once SSIS is done you can creat them again.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

Megha P (4/3/2014)
ok...thanks..SSIS will be fast over script in case of different server data transfer?

Yes, because with scripts you would have to use linked servers. SSIS will be faster.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Megha P SSCommitted Points: 1976 More actions · Answer 7

Hi,

Is there any other advantages of using SSIS over using script , except faster loading for data transfer?

Thanks,

Megha

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Megha P (4/4/2014)
Hi,
Is there any other advantages of using SSIS over using script , except faster loading for data transfer?
Thanks,
Megha

You don't have to use linked servers.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP