Fastest way of inserting into linked SQL Server from local SQL Server using SQL vs SSIS?

The following query took ~8 mins to execute with a million rows:

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

Open in new window


However, creating an SSIS package and specifying 'Fast Load', inserting only took 8.3 *seconds*.

Why is SSIS so much faster, and what SQL commands can speed up my query?
mdoolittleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>Why is SSIS so much faster, and what SQL commands can speed up my query<<
If you take the time to look at something like MS Profiler you would have noticed that SSIS uses something like BULK INSERT which minimizes the logging, hence the big difference in speed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdoolittleAuthor Commented:
Yes- I was thinking along those lines- i changed logging to 'Simple' still slower. Is there any way to do some kind of 'bulk-insert' through just SQL from table-to-table? I googled a lot and can't find anything that works. Seems absolutely crazy that output to text file- then bulk-insert up would be faster then INSERT INTO?
0
Anthony PerkinsCommented:
>>Yes- I was thinking along those lines- i changed logging to 'Simple' still slower.<<
Right, I would not expect that to make much difference.

>>Seems absolutely crazy that output to text file- then bulk-insert up would be faster then INSERT INTO? <<
Not really.  It is the very basis of ACID.  SQL Server is transactional after all.

Why cannot you not use SSIS?  How many rows are you transmitting?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Alpesh PatelAssistant ConsultantCommented:
If you want automated transfer then use SSIS. If have more iteration of import then SSIS is good.
0
mdoolittleAuthor Commented:
Not really.  It is the very basis of ACID.  SQL Server is transactional after all.

Why cannot you not use SSIS?  How many rows are you transmitting?

I can use SSIS, but for some operations it's much simpler to use a query. However if SQL queries are many orders of magnitude slower I'd like to speed them up.

So what you are saying is that SSIS has access to capabilities on SQL-to-SQL transfers that are entirely unavailable from within SQL Server itself? I was thinking that some combination of table hints was likely to bring SQL queries on par with SSIS.
0
Anthony PerkinsCommented:
>>So what you are saying is that SSIS has access to capabilities on SQL-to-SQL transfers that are entirely unavailable from within SQL Server itself? <<
No.  What I mean is that when you are using SSIS to import data with Fast Load it is not using INSERT statement,a s it assumes you do not need some of the transactional capabilities that are built into most modern DBMS and in a sense you are bypassing them entirely.  Yes, it is dramatically faster, but don't expect for example point-in-time restores.

>> I was thinking that some combination of table hints was likely to bring SQL queries on par with SSIS.<<
They will never be on par, but you should be able to improve using something like TABLOCK, however you need to be in Simple or Bulk-Logged Recovery Model.
0
mdoolittleAuthor Commented:
Thanks for the insight on the underlying differences between SQL and SSIS.

I will keep this question open in the hopes that someone familiar with doing so can provide actual examples of speeding up inserts using SQL, since that is the basis of my original question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.