July 23, 2008 at 10:53 am
I have some relatively large tables (approx 15 million rows each) that I need to copy from a database on one server to a database on another server on the same network. Is it more efficient to copy over the data using T-SQL or SSIS and why?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 23, 2008 at 3:11 pm
personally, i would walk through the import\export wizard within the database where you could setup either straight table mappings between servers or write a sql query. Both options allow for updating the mappings manually. at the end of the process, you can execute the process and\or save the SSIS package for a later use.
hth
July 24, 2008 at 11:40 am
Chuck Rivel (7/23/2008)
personally, i would walk through the import\export wizard within the database where you could setup either straight table mappings between servers or write a sql query. Both options allow for updating the mappings manually. at the end of the process, you can execute the process and\or save the SSIS package for a later use.hth
Thanks. That makes complete sense as far as a way to set it up.
Once the set up is done, do you have any idea which one would actually run faster and why?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 24, 2008 at 11:49 am
well, i would only use the query option if i was either pulling data from multiple tables in the same dataset or only selecting certain fields from the table to transfer so as to only grab what i actually need.
if i was pulling the whole table over, i would use the table as the datasource and map that way.
i do not know if you would receive or lose any performance doing the table step if you were pulling across the entire table.
July 24, 2008 at 8:17 pm
Using Sql to do the job will try to commit all the rows at a single so it'll block the server for a few minutes and if there are some problems with transaction log then it can be rolled back
if u use SSIS it can commit 10k rows at a time this will be a bulk insert so the number of logs in transaction log will be minimum and should give better performance
July 29, 2008 at 1:38 am
Thanks, Vinkat.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 1, 2008 at 1:47 am
We have almost a simular situation, but I want to be sure if SSIS or T-SQL will be the best solution for speeding up the process ?
* On Server A we backup a SQLdb of 408GB between a time period of 1 or 2 hours.
Here we use a "Db Maintenance" (=> SSIS)
* On Server B we backup a SQLdb of 78GB and this backup lasts for 4 hours !
Which is quiet a difference.
On Server B we use a T-SQL command.
Can someone explain to me why the backupprocess via T-SQL is that much slower than when it goes via SSIS ?
Or in which situation we should prefer SSIS before T-SQL.
Regards,
J.
August 1, 2008 at 1:15 pm
Well as far as my understanding goes using ssis will always help us b'coz ssis uses some of the resources which are not allocated to sql server 2005 and finally delegates the inserts and updates to tsql....where as using a tsql will fully use the resources which are allocated to sql server 2005 so sometimes there can be a buffer swapping issue or a bulk insert or a bulk update which can block the resources for quite a while. using SSIS this problem is reduced as it does in chunks of 10k or 20k rows at a time which fit the dedicated buffer size.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply