CODEWITHSUNDEEP
performancesql-server-2005file-uploadssis
JC_Robledo
JC_Robledo

Reputation: 3

SSIS File Load WAY TOO SLOW in Large Destination Table

this is my first question, I've searched a lot of info from different sites but none of them where conslusive.

Problem: Daily I'm loading a flat file with an SSIS Package executed in a scheduled job in SQL Server 2005 but it's taking TOO MUCH TIME(like 2 1/2 hours) and the file just has like 300 rows and its a 50 MB file aprox. This is driving me crazy, because is affecting the performance of my server.

This is the Scenario: -My package is just a Data Flow Task that has a Flat File Source and an OLE DB Destination, thats all!!! -The Data Access Mode is set to FAST LOAD. -Just have 3 indexes in the table and are nonclustered. -My destination table has 366,964,096 records so far and 32 columns -I haven't set FastParse in any of the Output columns yet.(want to try something else first)

So I've just started to make some tests:

-Rebuild/Reorganize the indexes in the destination table(they where way too fragmented), but this didn't help me much -Created another table with the same structure but whitout all the indexes and executed the Job with the SSIS package loading to this new table and IT JUST TOOK LIKE 1 MINUTE !!!

So I'm confused, is there something I'm Missing??? -Is the SSIS package writing all the large table in a Buffer and the writing it on Disk? Or why the BIG difference in time ?

-Is the index affecting the insertion time?

-Should I load the file to this new table as a temporary table and then do a BULK INSERT to the destination table with the records ordered? 'Cause I though that the Data FLow Task was much faster than BULK INSERT, but at this point I don't know now.

Greetings in advance.

Upvotes: 0

Views: 4389

Answers (3)

JC_Robledo
JC_Robledo

Reputation: 3

Well I Rebuild the indexes with another fill factor (80%) like Sam told me, and the time droped down significantly. It took 30 minutes instead of almost 3hours!!! I will keep with the tests to fine tune the DB. Also I didnt have to create a clustered index,I guess with the clustered the time will drop a lot more.

Thanks to all, wish that this helps to someone in the same situation.

Upvotes: 0

Sam
Sam

Reputation: 7678

If it works fine without the indexes, perhaps you should look into those. What are the data types? How many are there? Maybe you could post their definitions?

You could also take a look at the fill factor of your indexes - especially the clustered index. Having a high fill factor could cause excessive IO on your inserts.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

One thing I might look at is if the large table has any triggers which are causing it to be slower on insert. Also if the clustered index is on a field that will require a good bit of rearranging of the data during the load, that could cause an issues as well.

In SSIS packages, using a merge join (which requires sorting) can cause slownesss, but from your description it doesn't appear you did that. I mention it only in case you were doing that and didn't mention it.

Upvotes: 1

Related Questions