none
Best practise to perform inserts? RRS feed

  • Question

  • <!-- /* Font Definitions */ @font-face {font-family:Verdana; panose-1:2 11 6 4 3 5 4 4 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:536871559 0 0 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-update:auto; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:9.0pt; mso-bidi-font-size:12.0pt; font-family:Verdana; mso-fareast-font-family:"Times New Roman"; mso-bidi-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:70.85pt 70.85pt 70.85pt 70.85pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    Look at this code example:

     

    foreach (XElement x in xml.Descendants("A"))

    {

                          foreach (XElement xx in x.Descendants("B"))

                          {

                                                myObj o = new myObj();

                                                o.SerialNum = int.Parse(xx.Attribute("SN").Value);

                                                o.PartNumID = pn;

                                                ...

                                                ...

                                                ...

     

                                                dataContext.MyObjs.InsertOnSubmit(o);

                                                dataContext.SubmitChanges();

                          }

    }

     

    Here is my questions:

    -What’s the best way performance wise to do inserts in a database using Linq to SQL?

    -Inside the first or second foreach-loop?

    -Or is there a better way?

     

    The Dude

    Friday, September 3, 2010 8:45 AM

Answers

  • Hmm perhaps I am misreading the situation... As I said little info

    Look a datacontext is very lightweight, they consume very little, however a connection to the database has an overhead. So in most scenarios is better to send 100 mb in 10 packects than 10mb in 100 packets, but consider extending the db timeout.

    Regards

    • Marked as answer by liurong luo Friday, September 10, 2010 4:43 AM
    Wednesday, September 8, 2010 12:29 PM
  • I'd recommend performing a SubmitChanges every hundred rows and then creating a new instance of the data context.   (Don't forget to also do this at the end so you don't loose the last 75 rows).

    This has two benefits.

    1. Your TSQL insert statements get batched up into groups of 100

    2. You release the memory so you're only ever holding 100 in memory at any one time.

    [)amien

    • Marked as answer by liurong luo Friday, September 10, 2010 4:43 AM
    Thursday, September 9, 2010 3:01 PM
    Moderator

All replies

  • You should minimize operation to the database because they are costly.

    Most people do it in one place and send it in big packets.

    However it may make sense in your case to do it more frequently, I don't have the benefit of the whole picture.

    Sometimes common practice gets overturned by needs.  However as a rule of thumbs if you need constant access to the database then there is something wrong with your design.

    Regards

    Friday, September 3, 2010 1:10 PM
  • I'm not sure the quesiton can be answered from the above information. In addition to what Serguey says, what is the scale of the operation? Are we talking about adding two, three, half a dozen entries, or thousands or more? It's not really about which loop is it best to put the insert in, but how does your application design deal with database traffic given the scale it is designed for.

    I think your question as phrased is just over-simplistic. Which loop to put the insert in just misses the point of domain design really.

    Friday, September 3, 2010 8:00 PM
  • It's a client application communication with a local database. There is only a few user accessing the database. The code I posted shows how new data is inserted by the use of an xml file (in the client application). Normally this file will contain between 10-100 thousands data fields. This operation, inserting new data can take over five minutes to complete, so I'm curious is there a better/faster way to do these inserts?

    The Dude

    Monday, September 6, 2010 2:08 PM
  • Hello,

    Not sure if it is the best, but you could use SqlBulkCopy class to bulk insert data to database. See here for an example:
    http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

    Wednesday, September 8, 2010 9:02 AM
  • Hmm perhaps I am misreading the situation... As I said little info

    Look a datacontext is very lightweight, they consume very little, however a connection to the database has an overhead. So in most scenarios is better to send 100 mb in 10 packects than 10mb in 100 packets, but consider extending the db timeout.

    Regards

    • Marked as answer by liurong luo Friday, September 10, 2010 4:43 AM
    Wednesday, September 8, 2010 12:29 PM
  • I'd recommend performing a SubmitChanges every hundred rows and then creating a new instance of the data context.   (Don't forget to also do this at the end so you don't loose the last 75 rows).

    This has two benefits.

    1. Your TSQL insert statements get batched up into groups of 100

    2. You release the memory so you're only ever holding 100 in memory at any one time.

    [)amien

    • Marked as answer by liurong luo Friday, September 10, 2010 4:43 AM
    Thursday, September 9, 2010 3:01 PM
    Moderator