none
Batch Updates and one network roundtrip RRS feed

  • Question

  • I have the following scenario.

    I perform updates on multiple existing entities and then after Im finished updating I call dc.SubmitChanges() once.
    I notice LINQ makes one RPC call for EACH entity. i.e. one UPDATE per entity. This is evident by looking at the network traffic going over the wire (via wireshark) and my SQL Server Profiler registers one rpc call per update. Ideally, I would want to reduce this to one network call.

    Does LINQ support batch updates? If not, is there a recommended workaround to reduce network traffic? Are batch updates planned for a future release?

    thanks
    Dano

    Thursday, May 8, 2008 9:07 PM

Answers

  • LINQ to SQL will not batch updates. One reason is that LINQ to SQL needs to manage server-generated fields like IDs and timestamps that get updated for each entity.

     

    One option you have, of course, is to write a stored procedure and map it to a method on the DataContext, and then invoke it from within your code.

     

    Hope that helps,

     

    --Samir

     

     

    Saturday, May 10, 2008 12:13 AM

All replies

  • Hi Dano,

     

    Have you considered looking at using a TransacationScope?  This may solve your problem.

     

    Thanks,

    Kathy

    Saturday, May 10, 2008 12:10 AM
  • LINQ to SQL will not batch updates. One reason is that LINQ to SQL needs to manage server-generated fields like IDs and timestamps that get updated for each entity.

     

    One option you have, of course, is to write a stored procedure and map it to a method on the DataContext, and then invoke it from within your code.

     

    Hope that helps,

     

    --Samir

     

     

    Saturday, May 10, 2008 12:13 AM
  • Hi Kathy,

    I dont think the issue is with transactions. From what I've observed, one transaction is created and committed when the SubmitChanges() method is invoked. For example, if I make changes to three entities in my code, only one transaction is created when i call SubmitChanges(). Here is whats happening in the profiler...

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION
    go
    exec sp_executesql N'UPDATE [dbo].[MyEntity]
    .....
    go
    exec sp_executesql N'UPDATE [dbo].[
    MyEntity]
    ....
    go
    exec sp_executesql N'UPDATE [dbo].[
    MyEntity]
    ....
    go
    COMMIT TRANSACTION


    The above is what I would expect to happen as far as transactions are concerned. However, the issue is that each "
    exec sp_executesql N'UPDATE [dbo].[Event]" call is being sent over the wire as a different RPC call. So if i have 100 entities to update, 100 RPC calls with be made to the server. Ideally, I would want to batch all of these calls together and make a one shot RPC call to SQL Server.

    thanks
    Daniel
    Monday, May 12, 2008 10:35 AM
  • Hi Samir,

    Im going to go down the stored procedure route. I was investigating the limitations of using pure LINQ on our data access layer. The fact that LINQ cannot do batch updates without the use of Sproc's makes it a non-runner for parts of my DAL.

    thanks
    Dano
    Monday, May 12, 2008 10:42 AM