Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Posts
    15

    Unanswered: fast, consistent way to bulk insert?

    I'm looking for a way to insert 50k records into a SQL Server table, and need to get it done faster. right now using BULK INSERT takes 5-10 seconds, but faster would be better, and even better if it were a consistent amount of time.

    I've heard of DTS but don't know quite how to use it - would be offer any performance gains? any clue what the bottleneck is for BULK INSERT? hard drive speed? amount of RAM (this was on a 512mb machine)? parsing the fields?

    thanks for any ideas.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    512mb is rock bottom for running a production SQL box. Are you appending these records to an existing table? Is it something you're recreating every time? There's other options available if you're starting from scratch each go...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...and as I understand it Bulk Insert is faster than anythting DTS can offer. BCP? I don't know about comparisons of BULK INSERT and BCP - maybe oters do.

    Also you can try inserting into another table and then inserting from that. If you validate the data in the intermediary table you can use the NOCHECK option on constraints to speed up inserting.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    There's a comparison of bulk insert methods here. All the comparisons are for 2005 though.

    http://weblogs.sqlteam.com/mladenp/a.../17/10634.aspx

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What I have heard is that BULK INSERT is slightly faster than bcp mainly because bcp is a client side utility and BULK INSERT is internal to SQL Server. I could be wrong.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Want faster imports?

    Drop the indexes and use bcp
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2006
    Posts
    15
    thanks for the suggestions, I'll check them out. 512mb is definitely low, and will be upgraded once approved (ah bureaocracy)

    this bulk insert will actually be done several times during the day, adding to the table instead of updating, and has to be able to be initiated from code (i.e. .NET app). looks like SSIS is something to check out.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also, if you use bcp it can be considerably faster if you specify the TABLOCK hint in your query. You can pass the TABLOCK hint using the -h flag.

    More tips here:

    http://www.databasejournal.com/featu...le.php/3095511

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •