Speeding up SQL inserts (SQL vs SSIS, MSSQL 2008 R2)

[jasonp45]

The following insert query took ~8 mins on 1 million rows, going from an SQL table to another SQL table on a linked server:

INSERT INTO [LinkedServer].[LinkedDatabase].[dbo].[LinkedTable]
SELECT * FROM [LocalDatabase].[dbo].[LocalTable]

However, creating an SSIS package and specifying 'Fast Load', the same table insert only took 8.3 *seconds*.

Why is SSIS so much faster, and what SQL commands can speed up my query?

I have looked into table hints and would love some examples of what will speed things up the most. For what I'm doing I don't need logging, roll-back, etc.

http://msdn.microsoft.com/en-us/library/ms187373.aspx

 

[gmmastros]

I suggest you read this: faq183-3141

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

Fast load is a bulk operation, and minimally logged. The T/SQL Insert statement isn't. Also the T/SQL statement requires a distributed transaction which the SSIS package doesn't require.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[markros]

There is a good new article by Doron Farber

http://www.dfarber.com/computer-con...reds-of-millions-records-got-much-easier.aspx

PluralSight Learning Library