none
db RoundTrips with SaveChanges? RRS feed

  • Question

  •  

    I've been trying to figure out how to use SQLProfiler to see how many messages are being sent to SQL Server when I call SaveChanges and have, for example, 4 rows to update. But I haven't found the setting yet, so I thought I would ask:

     

    Are the commands batched up to the server or do they go one at a time? If they are batched, do we have control over that, as we do with the UpdateBatch property when using DataAdapter Update?

     

    thanks

     

    julie

    Thursday, September 6, 2007 1:26 PM

Answers

  • No, the commands are not batched. There are several reasons for that:

    1. We do optimistic concurrency checks, so updates may fail when db record has been changed since the last time it was read.
    2. We need to be able to retrieve primary key values for newly inserted rows (where IDENTITY is used to generate them) so sometimes we need to do SELECT and use values returned by it in subsequent updates.
    3.  We need to be able to read back server generated values (for example timestamp columns or columns updated in a trigger) -  again - SELECT is needed here.

    Jarek
    Thursday, September 6, 2007 2:50 PM
    Moderator

All replies

  • No, the commands are not batched. There are several reasons for that:

    1. We do optimistic concurrency checks, so updates may fail when db record has been changed since the last time it was read.
    2. We need to be able to retrieve primary key values for newly inserted rows (where IDENTITY is used to generate them) so sometimes we need to do SELECT and use values returned by it in subsequent updates.
    3.  We need to be able to read back server generated values (for example timestamp columns or columns updated in a trigger) -  again - SELECT is needed here.

    Jarek
    Thursday, September 6, 2007 2:50 PM
    Moderator
  • Thanks. It's tough writing technology that has to be suitable for so many scenarios . All of these make sense.

     

    It's still good to know. With regards to performance, it won't be the common case that someone would update huge numbers of records in one object context that would be pushed up in one SaveChanges call. But for those times that people are doing that, they should know to consider the various requirements, performance impliations and possibly using a dfferent mechanism (eg SQL Bulk Copy, etc.) if necessary.

     

     

    Thursday, September 6, 2007 3:02 PM