CODEWITHSUNDEEP
performancelinqlinq-to-sqldata-access-layer
Marcus L
Marcus L

Reputation: 4080

LINQ update procedures - best practice

What would be considered best practice for updates when using LINQ:

Right now I'm in a project where each cell for a record has its own static update method

public static void CellName(int id, int data)
{
     using (var scope = new someDataContext())
     {
          var TableName = scope. CellName.Single(v => v.ID == id);
          TableName.CellName = data;
          scope.SubmitChanges();
     }
}

These can then be set as a property in a business object, following the usual TableName.CellName = [something] scheme.

But I've also done projects where we've used an instantiated db/facade class. Where you first set the new values like properties and then call a database.SubmitChanges() method which then does the update for the entire record (or records).

From a design perspective, I like the first option better - when a property is set, the changes are instantaneous, and it's treated like any other object. But from a technical perspective, I suspect there's performance to be gained by doing updates in bulk.

So which method should I chose - or is there other options I should consider?

Upvotes: 2

Views: 286

Answers (1)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28698

Updating invidual cells is extremely inefficient. The main overhead in updating a database is instantiating a connection, sending & receiving a reply, and finding the row to update in the table. If you update per cell then you'll need to perform these steps for every cell - if you update per row then it's once per row.

Updating cells individually is the equivalent of writing SQL like

-- new command
UPDATE [Table] SET [Column1] = 'Value1' WHERE [Id] = 1
GO
-- new command
UPDATE [Table] SET [Column2] = 'Value2' WHERE [Id] = 1
GO
-- new command
UPDATE [Table] SET [Column3] = 'Value3' WHERE [Id] = 1
GO

where the commands are processed serially, and each command waits until the previous command completes before executing. Whilst this might not be much slower than updating a whole row at a time, it is likely to be slower, and is definitely not faster.

The preferred method is to update all the properties at once, and then send a single SQL command.

UPDATE [Table] 
SET [Column1] = 'Value1', [Column2] = 'Value2', [Column3] = 'Value3'
WHERE [Id] = 1

There are a few steps involved and if you think about it physically and practically it should all make sense.

Firstly, LINQ-to-SQL retrieves the entire row so you can update properties. The 'per cell' or 'per row' operations both need to do this, so it takes the same amount of time.

// the "Single" operator retrieves an entire row 
var TableName = scope.CellName.Single(v => v.ID == id);
var row = scope.MyTable.Single(v => v.Id == id); // more accurate description

-- sql looks something like this
SELECT TOP 1 * FROM [MyTable] WHERE [Id] = @id

This involves

  • compiling a query
  • opening a connection / retrieving a connection from the pool
  • sending the command to the SQL server
  • receiving a reply from the SQL server

Communicating with another server can take anywhere from a few milliseconds up to many seconds, depending on distance, performance, server load, etc.

You then update a property. 

row.Column1 = data;

This takes mere cycles. It is an immeasurably small component of the overall operation time.

And then you submit the changes.

scope.SubmitChanges();

-- sql looks like this
UPDATE [MyTable] SET /* set of columns to update */ WHERE [Id] = @id

This again involves a number of steps

  • compile the query
  • sending the command to SQL Server
  • receiving the response from SQL Server

There's nothing 'instantaneous' about updating cells individually - the cell will be updated in the same time as a whole row would be updated using the 'per row' pattern. It's just that the remaining cells will take longer to be updated.

Not only that, from the look of your question you're also going to have hundreds of boilerplate UpdateProperty methods. I've never seen or heard of such a pattern before but to be honest it sounds disasterous - you're sending many times more SQL commands than are necessary, and you're multiplying your database latency by the number of columns in your table.

Upvotes: 1

Related Questions