January 29, 2008 at 1:21 am
Can anyone tell me why the data import/export wizard is so much faster than using T-SQL to shift data from one table to another? Moving 1m rows between instances only took about a minute using the wizard but over an hour using an INSERT/SELECT statement across a linked server.
January 30, 2008 at 6:43 am
In an nutshell the Import/Export Wizard creates an SSIS (SQL Server Integration Services) package for you. The SSIS engine is the second generation DTS engine from SQL Server 2000.
The SSIS "front end" is the Business Intelligence Development Studio or BIDS under START >> All Programs >> Microsoft SQL Server 2005 >> SQL Server Business Intelligence Development Studio. It is has a Visual Studio-like screen design and layout.
For more information see:
SQL Server 2005 Books Online (September 2007)
SQL Server Integration Services
http://msdn2.microsoft.com/en-us/library/ms141026.aspx
Hope This Helps
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 31, 2008 at 6:22 am
And SSIS uses Bulk Insert which is a lot faster when copying a large amount of data.
Greg
January 31, 2008 at 11:48 am
In a nutshell what I think you are looking at is the difference in 1M seperate insert/ update statements vs a batch transaction of one insert/update statement that contains 1M additions. So the overhead is accounted for by all the times the 1M seperate insert statements are committing data back to the database which is 1M times vs. once for the batch. This is set theory in practice.
--Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply