Take the 2-minute tour ×

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.

share|improve this question
No clustered index on the table? –  billinkc Feb 22 '12 at 21:04
Disable your original dataflow and add a second dataflow task to the control flow. Call it speed test Use your flat file source and connect it to a row count transformation. Run the package as you would for production. This should provide your theoretical maximum throughput. Knowing the fastest you can read the data off disk will help determine whether it's a source or destination issue. What else is happening on that server? If you simulate the insert same operation (300 select statements unioned together would suffice) from ssms/sqlcmd, does it take a comparable amount of time? –  billinkc Feb 22 '12 at 21:08
add comment

3 Answers

up vote 0 down vote accepted

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.

share|improve this answer
Hi, I have 3 indexes each one in this columns: -Access_number(varchar 10,null) -Received_date(datetime,null) -Creation_date(datetime,null They are NONCLUSTERED and the 3 have this options WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] They were created with the default options as I can see (fill factor 0 = 100%) Should I Rebuild them with another fill factor like 80%.Cause my table is more like read intended and the writing just happens once a day –  JC_Robledo Feb 22 '12 at 20:47
I would try lowering the fill factor after reading this: msdn.microsoft.com/en-us/library/ms177459.aspx –  Sam Feb 22 '12 at 23:28
Actually the correct document for 2005 is this one: msdn.microsoft.com/en-us/library/ms177459(v=sql.90).aspx –  Sam Feb 22 '12 at 23:35
You should have a clustered index as well. support.microsoft.com/kb/297861/en-us –  Sam Feb 22 '12 at 23:36
Well I Rebuild the indexes with a fill factor of 80%, and the time drops significantly. It took 30 minutes instead of almost 3hours!!! –  JC_Robledo Feb 23 '12 at 23:45
show 1 more comment

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.

share|improve this answer
thanks HGELM, the table doesn't have any trigger. And I dont use merge join. Thats the weird thing of all, it's just a data load from a file, simple, well at least I thought it was simple hehe. Any other advise or thing that I'm missing? –  JC_Robledo Feb 22 '12 at 18:56
add comment

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.

share|improve this answer
add comment

Your Answer


