Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seldenm
Starting Member
5 Posts |
 Posted - 2009-05-08 : 16:19:14
|
| I have several databases with company records. Each company record has a primary key (CompanyID), and several child records such as Address, Phone, etc. I am copying these records all into a new database. The company ID values will need to change, since there are overlaps in the original databases. And since the Company ID changes, then the corresponding CompanyID needs to be changed in the child records. Also, each Company record has a PrimaryAddressID value, PrimaryPhoneID, etc. and these all need to change too.So what I did was create a stored proc in the new database (proc_NewCompany) to create a new company record along with it's primary address, primary phone, etc. records. This proc does an insert into the new company table, and gets the new company ID with SELECT SCOPE_IDENTITY(), and then uses that new company ID when inserting the new address records, etc. Once that's done it returns the new CompanyID value, and I call other stored procedures to create the new child Address, Phone etc. records.This all works, but it's taking way too long. At the current rate it will take 24 days to import all the company records into the new database.I've tried running the index tuning wizard, making the code as lean as possible, but none of this has had any real effect.Each call to proc_NewCompany takes from 2 to 4 seconds (the server is old and slow).I've tried creating a dts package, but basically it boiled down to calling proc_NewCompany which is what is eating up all the time. Can anyone think of a more efficient way to do this? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-08 : 16:59:34
|
| A better approach is to work each table at a time, not each row at a time. In other words, generate all the new Company rows and match their old IDs to the new IDs. Do the same for Addresses, Phones, etc. You probably can't use the stored procedure for this, you'll either have to modify it or create new code.You may need to create staging tables that are separate from the source and destination tables. You can add additional columns to track the migration, perform intermediate updates, etc., in order to clean the data up so you can do a straight insert into the destination. |
 |
|
|
|
|
|
|
|
How Do I Speed This Up?