Import running way too slow - SQL Server Forums

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)

Import running way too slow

Author

Topic

Taragor
Starting Member

46 Posts

Posted - 2014-06-18 : 19:32:38

Hi All,

I've got an import issue and I'm not exactly sure how to approach the problem.

For starters I have a table that contains 980 fields all defined as varchar except for the identity field (which is also the primary key)

Now before anyone says anything about the table please keep in mind this is done due to emergency measures and changing it is not optional at this time.

1) The table has 1 identity field indexed and clustered
2) The table has 1 indexed field non clustered
3) The total length of the varchar fields would exceed the maximum allowable row size if all fields were filled to their maximum potential. This will never be the case
4) I'm trying to import from 1 table to another table across 2 seperate servers (same table definitions and indexes) approximately 1 million records.

I created a stored procedure (simple insert into from a select) and it ran for 12 hours before it terminated with an error (transport level so most likely something hickupped in the network)

All this being said. Is there any way for me to reduce the import time?

What am I missing that is causing such a long delay? Paging?

We have other tables that contain millions of records which transfer
without any issues and in a very short time, granted these tables have at most 2 dozen columns and each row is but a fraction in size of the problematic table.

All help would be greatly appreciated.

Sincerely,

Tar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-18 : 19:41:37

I've never dealt with a table that wide. How big is the clustered index in GB? Have you tried using SSIS so that it is done with a bulk method?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-19 : 04:16:14

What is the source of the data?
Are you using SSIS for the import?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-19 : 10:52:06

+1 for SSIS and batch commits.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-06-20 : 09:46:46

You could think about disabling index and rebuilding after the import. This will depend on the stored procedure code you are running. http://www.sqlserver-dba.com/2011/07/disable-index-and-rebuild-index-for-updates-on-very-large-tables.html

Also agree with batch commits suggestion made by @lamprey

There is a link on this post to the Microsoft Data Loading performance guide - which will supply other tips - http://www.sqlserver-dba.com/2011/11/data-loading-performance-guide.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Taragor
Starting Member

46 Posts

Posted - 2014-06-20 : 13:10:24

Hi All,

Sorry for taking so long to reply, things have been hectic and I didn't get a chance.

To answer everyones question in regards to SSIS is no, I have not attempted it that way. All I had (was the simplest solution at the time) was a simple
select x from tableA and insert x into tableB in a stored procedure.

If I get a chance I will create an SSIS package and run a test and see what the difference is.

Thanks for the help.

Tar

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-20 : 14:55:24

I'd do the initial import into a one-time staging table. You could then specify TABLOCKX to reduce locking overhead to a dead minimum.

Once the import is finished, you could copy the rows from the staging table to the main table in batches.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-06-23 : 01:44:48

the other benefit of a staging table - it allows the flexibility to commit any data clean up activities

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-23 : 11:25:19

I'm not sure if you are suggesting a staging table in conjunction with the current process or with SSIS. If with the current process, I'm not sure there will be a gain. I suspect the main issue is what this is a server to server operation. This sort of query is notoriously bad performance-wise. I'm not sure of the amount of actual data as the table is only a million rows. But, due to the width of the table, it could be more than I'd expect. So, I'd lean towards an SSIS solution for the data transfers between servers.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-23 : 11:52:20

quote:
I'm not sure if you are suggesting a staging table in conjunction with the current process or with SSIS. ... I suspect the main issue is what this is a server to server operation. This sort of query is notoriously bad performance-wise.

I'm suggesting a staging table for either method, with no indexes (identity column is OK; I wouldn't cluster on it in this case, but I guess you could without too much overhead). I'm considering that the original q stated:

quote:
We have other tables that contain millions of records which transfer without any issues and in a very short time

I'm not willing just yet to exclude locking/blocking as the primary cause of the delay to load the table, because that's a huge number of very large rows to add to a live table. A staging table in my view is much preferred anyway and will confirm whether the issue is data transfer or table locking/blocking.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources