locked
InsertOnSubmit() or InsertAllOnSubmit()? RRS feed

  • Question

  • Which is preferable, InsertOnSubmit() or InsertAllOnSubmit()?

     

    At first I thought InsetAllOnSubmit() is preferable because all the inserts are sent in only one database trip.  Apparently, that's not true.  The profiler trace shows the inserts are sent one at a time, the same way as InsertOnSubmit().

     

    In the same vein, I tried these:

     

    var q = dc.Addresses.Where( a => a.ID < 10);

    foreach( var a in q)

    {

       a.City = ...

       dc.SubmitChanges();  // inside the loop

    }

     

    is the same as below.  In both cases, the updates are sent to the database one at a time.

     

    var q = dc.Addresses.Where( a => a.ID < 10);

    foreach( var a in q)

    {

       a.City = ...

    }

    dc.SubmitChanges();  // outside the loop

     

    Sunday, January 27, 2008 3:35 AM

All replies

  • First of all, InsertOnSubmit and InsertAllOnSubmit does the same thing: they are used to add pending inserts to the unit-of-work (DataContext) to be committed to the database later upon call to SubmitChanges. Regarding the differences between the two pieces of code you have written, there are several ones:

    • In the first code, for each call to SubmitChanges, a new connection is opened to the database and is closed at the end of the function call, While in the second code, a single connection is opened and it is closed after all update operations. Since opening database connections is a costly task, the second code is expected to be noticably faster for large number of update operations. I have run a test of inserting 5000 rows in a database table with both of the above approaches and found out that using the single-SubmitChanges version is 35% faster.
    • Each call to SubmitChanges has its own transaction i.e. means that all database updates in a call to SubmitChanges are done in one transaction. Of course, you can make the multiple-SubmitChanges version into a single transaction by putting it in a TransactionScope block.
    • In your foreach loop, If instead of changing City member for each record once, you change City for each record several times, then the multiple-SubmitChanges version generates an update command for each value change while the second approach generates an accumulative update command for each entity.

    At last, LINQ to SQL does not have any mechanism for bulk update commands like UPDATE <someEntity> SET <someProp>=<someValue> WHERE <someCondition>. If you really need it, you should either implement it yourself or use other LINQ-enabled tools that have this functionality (I'm not sure but I think a tool named Seamless LINQ has this feature enabled).
    Sunday, January 27, 2008 2:14 PM
  • Thanks for your helpful insight.

     

     CompuBoy wrote:
    First of all, InsertOnSubmit and InsertAllOnSubmit does the same thing: they are used to add pending inserts to the unit-of-work (DataContext) to be committed to the database later upon call to SubmitChanges.

     

    So if they are the same, it seems strange that the designers decided to give us two of them?  It would seem that they intended one to be used in one scenario and the other in a different scenario. 

     

     CompuBoy wrote:
    In the first code, for each call to SubmitChanges, a new connection is opened to the database and is closed at the end of the function call, While in the second code, a single connection is opened and it is closed after all update operations. Since opening database connections is a costly task, the second code is expected to be noticably faster for large number of update operations. I have run a test of inserting 5000 rows in a database table with both of the above approaches and found out that using the single-SubmitChanges version is 35% faster.

     

    I ran a similar test and I concur with your 35% faster findings.  What you say above makes sense.  But I did not see in the profiler that a connection is opened for each SubmitChanges().  The profiler says;

     

    -- only one connection

    exec sp_reset_connection

    -- a series of updates

    exec sp_executesql N'UPDATE [dbo].[Addresses]
    SET [City] = @p2
    WHERE ([ID] = @p0) AND ([Version] = @p1)

     

    I interpret the above as only one connection is made for all the SubmitChanges() in my loop.  Am I perhaps interpreting the profile trace wrongly?

    Monday, January 28, 2008 1:37 AM
  •  

    InsertAllOnSubmit() simply loops over all the elements in the IEnumerable<T> collection and calls InsertOnSubmit() for each element.

     

    In the first code example, the reason the log looks the same is the because the statements being submitted are the same, the difference is that if you put a break point at the last "}" in the foreach loop you would see that the connection is being opened, the statement is being executed, then the connection is close. This is being done every time the loops executes.

     

    In the second example a connection is opened once. The statements are still being sent individually, but they are being sent without closing the connection. After all changes have been made, the connection is closed. So, the same SQL is being generated and sent, the difference is how many times the connection is opened and closed (the slowest part).

    Wednesday, December 3, 2008 1:57 AM