Massive import is bogging down. Why?

Question

Post reply

Massive import is bogging down. Why?

dndaughtery

SSChampion

Points: 11290
More actions
April 14, 2009 at 6:19 am

#129307

I am importing data from an SSIS Raw File. When I began the import I just ran it from the debugger so I can monitor how many records are being inserted. When it first started it would insert/import an avg of 10,000 records per 3 seconds. Now that the number of inserted records is 60 million its bogging down to 10,000 records every 48 seconds. I set the packet size of the connection mgr to 32,767 and the Max Insert size of the OLEDB connection to 2,147,483,647. Why is the process bogging down? Is it due to the number of records in the table or does it have something to do with my SSIS package? Is there a way to make this process faster?

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 1

are you locking the table ? or checking constraints on the data flow destination?

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Maybe you could try INSERTing the data from another SQL Server table and comparing times - to work out whether the problem is something to do with the import or something to do with the destination.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

dndaughtery SSChampion Points: 11290 More actions · Answer 3

Yeah, I have the "Table Lock", "Check Constraints", and "Keep identity" check boxes checked. Now that you pointed that out, I'm pretty sure that I should uncheck the check constraints but shouldn't the "Table Lock" and "Keep identity" stay checked? But if having the "Check Constraints" checked is why it is slow now, how come the import was running so much faster in the beginning and slowing down to a crawl now? IF that was the problem wouldn't it have started out slower?

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 4

On what connection are you trying this on? I mean are the source and destinations on the same m\c or on a network (comp network? T1?).

Using the fast load actually does a Bulk insert; now u could try changing the batch size to maybe 10000 and also the commit size to test it. Do u have many indexes on your target? u may want to drop indexes before the operation and recreate them after u have done ur inserts.

dave-0 Mr or Mrs. 500 Points: 550 More actions · Answer 5

dave-0

Mr or Mrs. 500

Points: 550

April 14, 2009 at 8:31 am

#977269

Try a BULK INSERT.

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 6

David, i have question - doesnt the data flow task execute Bulk Insert when u use the fast load option? (if u see from profiler it says Insert Blulk but behavior is similar to bulk insert)

dndaughtery SSChampion Points: 11290 More actions · Answer 7

Well, I was gonna to try a Bulk Insert but I couldn't find an answer to a problem I was having with the format file see the post at the link below:

http://www.sqlservercentral.com/Forums/Topic695887-148-1.aspx

dave-0 Mr or Mrs. 500 Points: 550 More actions · Answer 8

BULK INSERT gives you a level of control for sizing the amount of data-moving that is difficult to achieve in SQL.

http://msdn.microsoft.com/en-us/library/ms188365.aspx

dndaughtery SSChampion Points: 11290 More actions · Answer 9

Sorry Vish for got to answer you. Yes there is a pk index on the table. This is all taking place on the same machine so I wouldn't think there would be a problem with the network. I'll have to remove the index as well next time. Wow, thanks a lot, you mentioned several things.

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 10

Just have the table lock checked, remove any null checks, constraint checks.

set the batch size to 100000 and commit size the same.

Do you have any transformations between the RAW source and the SQL Server destination?

What is your destination provider - OLEDB or ADO.NET?

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 11

David, u can change the batch size and commit size the destination adapter...any other difference?

dndaughtery SSChampion Points: 11290 More actions · Answer 12

Its an OLEDB Destination and no there are no transformations between the raw file source and the OLEDB Destination

dave-0 Mr or Mrs. 500 Points: 550 More actions · Answer 13

Those are the main two:

TABLOCK is very useful in an online enviroment.

Are there any clustered views? Complex Indexes? Refreshing you statistics will help performance too.

Run a Performance Monitor to get an idea what could be the cause. Could come down to hardware.

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 14

david,

i think the TABLOCK is already being specified.

dndaughtery , Maybe your transaction log is getting filled up or growing at a very rapid rate (ok, on this point i may not be sure if the data flow task is logging to transaction log, but keep an eye on it) . Change the db recovery mode to simple and test.

Let me know if it worked.