locked
Need to find a faster way to create many records RRS feed

  • General discussion

  • There are times in my application where I need to create on the order of thousands of new records.

    The current workflow is to create objects by parsing a file that the user wishes to import. After creating the objects, I get a reference to my entity and use the AddNew() method. With a for loop I create the new record and fill in the fields from the objects. I then display the new records in a datagrid. From here, the user can save to the database after reviewing the datagrid.

    Parsing the file, instantiating the objects, and filling the datagrid are all extremely quick but the process of calling AddNew() and actually filling the record is very slow. 

    It seems like I am very CPU limited. My first thought was to use multple threads to create the new records(aka AddNew(), not for saving) but I don't know of any way to do this since Lightswitch requires all access to be from the screen logic thread.

    Is there a better way for me to do this?

    Thursday, January 15, 2015 7:07 AM

All replies

  • In your currently workflow, the user can review the record to be imported.

    Is that a requirement, but that really matters for the architecture you need.

    In case review is not a requirement, you can just transfer the whole file to the server and do the import over there. For really big loads you can use the BCP utility server side, to really make it amazingly fast. See : http://technet.microsoft.com/en-us/library/aa196743%28v=sql.80%29.aspx


    paul van bladel ==independent enterprise application architect== http://blog.pragmaswitch.com

    Thursday, January 15, 2015 9:01 AM
  • Yeah if at all possible I'd like to keep the current workflow.
    Thursday, January 15, 2015 6:56 PM
  • but what exactly do you want to speed up, multiple threads would not make any difference.

    can you show the code you are using?


    paul van bladel ==independent enterprise application architect== http://blog.pragmaswitch.com

    Thursday, January 15, 2015 7:03 PM
  • You can use code such as this:

    string connString = System.Web.Configuration.WebConfigurationManager .ConnectionStrings["_IntrinsicData"].ConnectionString; SqlConnection conn = new SqlConnection(connString); string sql = "INSERT INTO MessageLogs (LogMessage, Created) VALUES (@LogMessage, @Created)"; conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@LogMessage", LogMessage); cmd.Parameters.AddWithValue("@Created", DateTime.UtcNow); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); conn.Close();

    See:

    http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/2224/Sending-Asynchronous-Emails-Using-LightSwitch-HTML-Client.aspx


    Unleash the Power - Get the LightSwitch 2013 HTML Client / SharePoint 2013 book

    http://LightSwitchHelpWebsite.com

    Thursday, January 15, 2015 8:34 PM
  • I have a couple of thoughts for you:

    I think that you will have to upload the file to the web server and load from there.  That will get you the most improvement in speed and that may be fast enough.

    The next thing that you can do is to use a stored procedure to insert the records instead of Entity Framework via AddNew().  You could use Michael's code to insert records individually, which would be much faster than AddNew, or you could do a batch insert for best performance.  If you are only inserting a thousand records or so, it won't make much difference.

    See the following for what I have found to be the fastest insert method:

    Inserting records using stored procedure with table-valued parameter

    Another user used this method and got the following results:

    Got it to work in a small test program entering 19MLN records and the results are just fabulous:

    vb.net record entry on the server -> 8 hours

    stored procedure per records -> 30 minutes

    stored procedures with table parameter -> 3 minutes

    (That's 100,000 records per second).

    Another thing that you might want to do is to first insert the records to a temp table; the user could then approve/reject records.  If they approve, run a stored proc to copy from the temp table to the final table.  (When I say temp table, I don't mean a real SQL server temp table, but another table that is just used to hold the data while reviewing.)

    Mark



    • Edited by marks100 Friday, January 16, 2015 6:48 PM
    Friday, January 16, 2015 5:35 PM
  • Thanks for the replies everyone. Very Helpful.

    I have a much better idea of some alternatives I could use to speed up this routine now.

    Friday, January 16, 2015 8:32 PM