Bulk Insert

Question

Bulk Insert

dgyanani

SSC Rookie

Points: 26
More actions
January 21, 2004 at 11:18 pm

#107143

We are bulk inserting data in a single table with 'bulk insert' statment. The table in which we are doing this bulk insert has got 1 clustered index on an bigint field and 6 non clustered indexes.

The insertion of first 10-12 millions are done in 7-8 hrs. After that each million takes 3-4 hours. It is very clear that this difference in performance is not gradual, but very much sudden.

We are not sure about the reason behind this sudden degradation in performance. We assume that this is happening because of some configuration/tweaking that needs to be done at the SQL server end.

Configuration of the machine running DB server: Dual processor(2.4 GHz), 2 GB RAM, 512 HDD.

What could be the possible reason for it? Do we need to do some database tweaking for it?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Green Armchair Hall of Fame Points: 3561 More actions · Answer 1

Is the data you are loading sorted by the clustered index already? If so, use the ORDER argument.

If not, is it possible to drop the indexes before loading, then rebuild when loading is complete? It should be quicker to load data into the table with no indexes and then rebuild them after.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

Also, I'm not sure if the Bulk Insert task automatically sets the database into Bulk-Logged recovery mode, but if not you need to do that else you are logging every insert. Check to see if your transaction logs are showing every insert.