CODEWITHSUNDEEP
c#asp.netsql
atrljoe
atrljoe

Reputation: 8151

What's the best way to deal with the updating of several columns

If I have a profile form with 20 fields a user can update on a page. It seems like it would be a waste to run an update statement that would update every single column if the user only changed two fields.

I was wondering what would be the best way to handle this for the best performance.

Since I only have 20 fields its not really an issue but I am just wondering for future development of where maybe it could be more.

What does everyone else do, do you just update every column for that row?

Upvotes: 0

Views: 154

Answers (4)

Jeremy Gray
Jeremy Gray

Reputation: 1418

CREATE PROC [dbo].[UpdateMe]
(
    @pkey int,
    @Col1 int = null,
    @Col2 int = null,
    ...,
    @Col20 int = null
)
AS BEGIN

    UPDATE [Table]
           SET Col1 = ISNULL(@Col1,Col1),
           Col2 = ISNULL(@Col2,Col2),
           ...
           Col20 = ISNULL(@Col20,Col20)
    WHERE pkey = @pkey

END

Keep track of what has changed on the client and send things accordingly. If you are worried about sending to much over the wire, use named parameters and only send what has changed. Since there are defaults on the procedure, it doesn't cost you anything. The seek time in sql is where the cost is, so updating a column to itself should be pretty cheap.

Upvotes: 0

marc_s
marc_s

Reputation: 754598

Tools like Entity Framework (or other ORM tools) will handle this for you, for "free".

They track what has changed in your objects, and they will issue a tailor-made UPDATE statement that will only update those columns that have actually changed.

You need not worry about any of those details anymore. All taken care of for you. Try it!

Resources:

Upvotes: 4

gepatino
gepatino

Reputation: 168

I can only imagine fetching the current db record, compare field by field with the user input and then update only the changed fields.

This will be more expensive than just updating all fields in a single update statement, so I'd go with single update choice.

Upvotes: 0

Andomar
Andomar

Reputation: 238116

Your average network packet is 1500 bytes long. That can probably contain your 20 fields with ease. So you would save no network load by updating less than 20 fields.

SQL Server stores data in pages of 8000 bytes. If you update 1 bit, SQL Server will write it away as a fully changed page: 8000 bytes of data. So you save no disk activity by updating less than 20 fields.

Optimizing for performance is often a mistake. Databases are very fast these days. Optimize for simple and clear code instead.

Upvotes: 7

Related Questions