Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2002
    Posts
    33

    Arrow Unanswered: The fastest way to insert lots of rows?

    Hi all,

    our application needs to insert lots of rows from time to time, i;m talking about 500-2000 at a time. I found that the most efficient way to do this is to combine all INSERT SQL statements into groups of 50 and execute them like that.

    Is there any other better faster way to do this?

    any help is appreciated,
    wojo.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    have you checked out BCP.exe or Bulk Insert or DTS. Excelent documentation can be found in Books Online. These have the ability to load 100+ rows a second.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Where is the Data coming from (logically)

    Are you importing it ?

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Sep 2002
    Posts
    33
    no, im bulding data dynamically.

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Then it's probably down 2 the fastest way your application can dump/output the data

    - then say use BCP.exe (possibly using a stored procedure to call it) if it's dumped to a text file

    what language u writing your app in ?

    seems strange your app should only want to do it in Chunks

    methinks more info required

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Sep 2002
    Posts
    33
    im using VB under COM+

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    We have a Java front end but face this problem often. We found it faster to write a delimited file to a server and then bulk insert that file to a hold ing table. Once all the data is in the holding table we then move the data to it's final home.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Existing Data held in

    Array, ResultSet , Collection or ?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Sep 2002
    Posts
    33
    hmm, data is being queried and worked on, it computes match % and throws the result into collection for later insertion.


    thanks GWilly

  10. #10
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Cool k collection thx

    k collection thx

    U workin Late Shiz

    Hope they payin U enuff !!

    methinks VB Collection <<>> SQL Server
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  11. #11
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    If the Data is in a VB Collection then this will be the performance Bottleneck Cycling Through them.

    If you did'nt want to insert the data directly into the target table by passing the Row Field Values to a SP using Passed Variable's each time you loop through the collection

    Then I would Suspect you're probably as well creating a temporary #Table inserting each row or Multiple Rows (Bulk Insert)

    Then Calling a SP to Bulk Insert the Temp Table Data into it's final home after any neccesary Sanity Checks on the Data and Transaction Wrapping.

    I'm not sure if this is the best way - there are certainly a number of ways to do it - but your data is already in a high overhead position to start with.

    Hope this helps & Hope that someone else posts other idea's as I would assume getting data from a VB Collection into SQL Server is a common Occurence

    Good Luck

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  12. #12
    Join Date
    Sep 2002
    Posts
    33
    Thank you GWilliy and Paul Young for your suggestions, i will let you know if it helped.

    regards,
    wojo

Posting Permissions

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