none
Bulk update and delete from table RRS feed

  • Question

  • Hi,

    We have delete 8000 records from DB at same time. While going for...each loop and obj.delete(), we are facing performance issue. Can someone help for bulk insert and delete from DB.

    We found chunk of codes in forums, but it not it not helps.

    Thanks,

    Mukesh Kumar

    Friday, July 17, 2015 4:45 PM

Answers

All replies

  • Since you haven't told us what technology you are using for DB and for DB access this is just a guess.

    When you need to do a bulk anything in the DB it is much better (assuming the DB has the functionality) to use a single (again an assumption) statement to delete all the rows.

    What it would seem that you are doing is sending all the data for the 8000 rows to your app, then issuing 8000 deletes, no wonder it is slow.

    If you let us know what tech you are using we can help better.


    Lloyd Sheen

    Friday, July 17, 2015 5:19 PM
  • Almost all databases support sql and many stored procedures.

    Stored procedures can be more efficient that raw sql.

    Basically you want to run a delete statement on the server one way or another:

    http://www.w3schools.com/sql/sql_delete.asp

    Like

    Delete from Fruits where Type='Citrus';
    You must be connecting to that database somehow but ADO.net or entity framework would both allow you to run such a piece of sql.


    Friday, July 17, 2015 6:25 PM
  •    

    We are using .Net Entity framework 4.0.
    Below is code to add one record into database.
    Customer cust = new Customer
    {
    CustomerID="DVC"
    CompanyName = "DotNetCurry"
    ContactName = "Suprotim Agarwal"
    };

    context.Customers.AddObject(cust);
    context.SaveChanges();

    We have insert 8000 records in single event, while making this AddObject(cust) in foreach loop, system becomes very slow and its not working. 

    We are looking for any bulk insert for all 8000 records into DB. 

    Thanks, 

    Mukesh Kumar


    Saturday, July 18, 2015 9:06 PM
  • EF is great for end user crud, one record at a time.

    It's the wrong tool in your toolbox for many records at a time.

    If you're going to insert 8000 records at a time then entity framework just isn't the way to go.

    That data must be coming from somewhere.

    You have a spreadsheet, a file or something with 8,000 records.

    Use ssis or just a stored procedure.


    Sunday, July 19, 2015 8:34 AM
  • This questions has nothing to do with WPF so you are in the wrong forum but you should call SaveChanges() once after you have added all records in the loop:

    for(int i = 0; i < 8000; i++)
    {
    Customer cust = new Customer
     {
    ...
     };
    
     context.Customers.AddObject(cust);
    }
    
    context.SaveChanges();
    

    The database are not involved at all until the SaveChanges method is called.

    You can also try to disable change detection. Please refer to the following page for more information: http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework

    But please ask any further questions about EF you may have in the EF forum: https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

    ...or on StackOverflow using the entity-framework tag: https://social.msdn.microsoft.com/Forums/en-US/7980871d-0271-47dc-8185-43fdcbeea57f/-ef-team-no-longer-uses-this-forum-?forum=adodotnetentityframework

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Thursday, July 23, 2015 7:45 PM
  • Thanks,

    I will check.

    Monday, July 27, 2015 12:57 AM