SELECT INTO taking much too long - Better way? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT INTO taking much too long - Better way?

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-11-04 : 15:15:24

I have a huge table across the network to copy one SQL Server table to another server. I am logged into the destination server. I created a stored procedures with the code below in it. I executed it from SSMS on the destination (which I am remotely logged into from a laptop). It has more than 100 million rows in it, but, being not logged, I didn't think it would take over 2 hours. Both the source and the target are SQL Server. I tried using SSIS, but it would stop after a while with only a percentage (usually about 2/3) of the rows transferred. I can't seem to find a way to reliably transfer these tables. I have about 6 of them and I can't afford for it to take that long. I tried linked servers with INSERT into, but it was even worse. What am I missing? What is positively the fastest way to move a simple, but huge table from server to server. Here is the code I am using right now:

CREATE PROCEDURE [dbo].[Populate_SoftwareInventory_Table] AS

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[staging].[SoftwareInventory]') AND type in (N'U'))
DROP TABLE [staging].[SoftwareInventory]

SELECT * INTO [SAM].[staging].[SoftwareInventory]
FROM OPENQUERY (SCCMSV2DBREPL01, '
SELECT [ClientId]
      ,[ProductId]
      ,[FileId]
      ,[FilePathId]
      ,[ModifiedDate]
      ,[FileModifiedDate]
FROM [CM_007].[dbo].[SoftwareInventory]
')

Thank you.

Duane

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 15:46:14

Just an idea:
create an empty db on the source server
copy your tables there
backup the new db
restore the new db on destination server
copy your tables there

No, you're never too old to Yak'n'Roll if you're too young to die.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-11-04 : 15:48:54

Thanks for the idea, and I will think about it in the future, but I don't have anything but read access to the source server.

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-04 : 16:10:43

Hard to say. It sounds like there are some issues with your system if things are stopping or failing to complete

If you use SSIS, drop all your indexes (probably even clustered if you have one) on the destination table and make sure you don't commit all 100M rows at once. Try smaller batch sizes: 50k or 100k or whatever and see how that does. Then recreate your indexes.

I'd avoid linked servers because of the potential performance implications.

Out of curiosity, how big are these tables, not it rows but width or data size? Do they have 10's or 100's of columns or do they take up 10's or 100's of gigabytes?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-04 : 16:37:38

SSIS or bcp is the way to go here. Linked servers will enlist a distributed transaction with SELECT...INTO, won't affect transaction logging but doesn't speed things up.

If memory or other issues preclude using SSIS, export the data using bcp, then import it with bcp or BULK INSERT.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-11-04 : 17:09:39

Thank you for the tips. If I use BCP, I am not sure how to get the data back into the table the same way. Will the defaults usually work?

Duane

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-04 : 17:34:46

You'd need to do something like this (note the following must go on a single line):

bcp 
"SELECT [ClientId],[ProductId],[FileId],[FilePathId],[ModifiedDate],[FileModifiedDate] FROM [CM_007].[dbo].[SoftwareInventory]"
queryout myFile.txt -S SCCMSV2DBREPL01 -T -c

To import you'd do bcp in:

bcp "[SAM].[staging].[SoftwareInventory]" in myFile.txt -S servername -T -c -h"TABLOCK"

The TABLOCK hint will improve performance, see Books Online for more details on similar hints. Be sure to follow Lamprey's advice about dropping non-clustered indexes on the destination table (if any) and re-creating them after, and also set batch sizes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources