Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Import/Export Wizard Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 1:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 2:01 AM
Points: 797, Visits: 1,040
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.
Post #448722
Posted Wednesday, January 30, 2008 6:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, Visits: 655
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
Post #449342
Posted Thursday, January 31, 2008 6:22 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 4:51 PM
Points: 4,065, Visits: 5,298
And SSIS uses Bulk Insert which is a lot faster when copying a large amount of data.

Greg
Post #449894
Posted Thursday, January 31, 2008 11:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 29, 2009 2:34 PM
Points: 56, Visits: 77
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



Post #450115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse