Have Geek Brain

Sql server table fast loading not

I inherited an SSIS package that loads 500K rows (about 30 columns) into a staging table.

It has been cooked for about 120 minutes now, and it has not been done --- this suggests that it is running at less than 70 lines per second. I know the environment is different, but I think it's a couple of orders of magnitude different from "typical".

Oddly enough, the staging table has a PK constraint on the INT (identity) column, and now I think this might hinder load performance. There are no other constraints, indexes, or triggers on the staging table.

Any suggestions?

---- More information ------ A
source is a tab-delimited file that connects to two separate components of the data stream that adds some static data (start date and batch ID) to the stream, which then connects to the destination adapter OLE DB

Access mode - OpenRowset using FastLoad

FastLoadOptions - TABLOCK, CHECK_CONSTRAINTS

Maximum insert hold size: 0

+1


a source to share


4 answers


I'm not sure about the etiquette of answering my own question - so sorry if this is a better fit for a comment.



The problem was the datatype of the input columns from the text file: they were all declared as "text stream [DT_TEXT]", and when I changed that to "String [DT_STR]", 2,000 rows loaded in 58 seconds, which is now the scope is "typical" - I'm not sure what the text file does when the columns are declared this way, but it's behind me now!

+6


a source


I would say there is some problem, I am roughly inserting an intermediate table from a file with 20 million records or more columns and an identity field in much less time than this and SSIS should be faster than Server 2000 SQL Bulk Insert.



Have you checked for blocking issues?

+2


a source


If it's running in one big transaction, that might explain the situation. Make sure the commit is done from time to time.

You can also check CPU usage, memory and IO to rule out resource issues.

0


a source


Hard to say.

I had a complex ETL, I would check the maximum number of threads allowed in data streams, see if some things could run in parallel.

But it sounds like a simple transmission.

With 500,000 lines, batch setup is an option, but I don't think it's necessary for multiple lines.

PC identification shouldn't be a problem. Do you have any complex constraints or constant computed columns at the destination?

Is it pulling or clicking on a slow network link? Pulling or pushing from a difficult SP or viewing? What is a data source?

0


a source






More articles:


All Articles