Speed up Bulk Insert

Question

Speed up Bulk Insert

aamirault

SSC Rookie

Points: 36
More actions
January 25, 2008 at 6:22 pm

#186061

Hello,
I am looking for advise on speeding up an insert.
Typically the process we use is:
Truncate the load (temp) table
Load in data from a flat file
Flip the load table with the production table.
We do this daily.
We have noticed this process takes 10+ hours to complete if SQL has been running for some time on the active node of the cluster, however if we fail over the cluster, launch the import, it takes 2 hours to insert the same number of records.
This cluster is the busiest we have with an average of 400 user connections.
Apart from trying to minimize the number of connections and therefore free up some worker threads to prevent thread pooling, is there anything anyone can suggest to speed up this process without a fail over needing to occur?
Since we truncate the table before insert I would think rebuilding the indexes would be of little use since there is no data to index.
Thanks for all your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 1

You could drop the indexes before the bulk insert and rebuild after the insertion. the insertion will be quicker becuase it will not have to modify the indexes on each insert. I have done before during a "Back record conversion" exercise and a 12 hour process became 4.

just becuase there is no data in the table, the indexes will exist and each insert will also have to insert or update the indexes... which can resul in fragmenation. so i ould recmommend rebuiling them after the bulk insert anyhow.

How many indexes are on the table?

Gethyn Elliswww.gethynellis.com

toniupstny Hall of Fame Points: 3986 More actions · Answer 2

If you are not using SIMPLE or BULK LOGGED recovery models and your processing methods permit, you may want to consider doing so. They will do less logging and help speed recovery.

Toni

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

How many rows are you loading with BULK INSERT?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Depending on your load, you might care to drop all indexes EXCEPT the clustered. MS' case study on the matter seems to think that that tends to be the overall fastest method (versus leaving them all on and dropping them all). This would be particularly true if the "natural load order" (like, say, the physical order of a flat file) lined up with the clustered index you were planning to use.

Of course - as always - your mileage may vary...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?