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 |
|
kusumjain
Starting Member
21 Posts |
 Posted - 2008-09-23 : 10:18:17
|
| I am pulling about a million rows over the netwrork from SQL Server 2000 to SQL Server 2005. Source residing in NJ and Destination Server residing in FL. I have almost 227 columns from the source. It takes about 25 minutes to pull in the data. I am not sure if I can do any better in terms of performance. Any help is appreciated.Thanks,Kusum Jain |
|
|
rakesh4u
Starting Member
3 Posts |
Posted - 2008-09-24 : 01:57:52
|
| check if there are any indexes on the destination table. if exists drop them and create after copy. And also change the recovery model to bulk logged during the copy. hope this helps to certain extent. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-24 : 02:09:17
|
| How are you transfering data in SSIS?there's an option called fastload which will give you much faster performance |
|
|
|
kusumjain
Starting Member
21 Posts |
Posted - 2008-09-24 : 10:03:27
|
| My database recovery model is Simple. So changing to Bulk logged would it make any difference.I do have OpenRowset Using FastLoad setting on.Thanks,Kusum Jain |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-09-24 : 10:06:49
|
| Yes Bulk Logged will make it faster as it won't log the transactions in the Log file and save some time |
|
|
|
kusumjain
Starting Member
21 Posts |
Posted - 2008-09-24 : 11:00:01
|
| I did some testing using the local server and it took only 3 minutes to pull 100 columns and 700,000 rows. The same over the network(NY to FL) is taking almost 20 minutes.Thanks,Kusum Jain |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-24 : 11:04:05
|
| The question is how are you pulling data? Can you Bulk-insert,Export/Import Wizard,SSIS instead? It all depends on network bandwidth you have with your destination Server. |
|
|
|
kusumjain
Starting Member
21 Posts |
Posted - 2008-09-24 : 11:32:52
|
| I run a daily job that uses the SSIS package to bring down the data.My interest was to bring down the time to get the data. This would have saved me a lot of time and help my other process to complete earlier.But looks like it is the bandwith problem.ThanksKuusm Jain |
|
|
|
|
|
|
SSIS performance