Data Import/Export Wizard

  • 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.

  • 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

  • And SSIS uses Bulk Insert which is a lot faster when copying a large amount of data.

    Greg

  • 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