Insert or update

Question

Insert or update

ramana3327

SSCoach

Points: 19572
More actions
January 18, 2014 at 1:36 pm

#396125

Hi,
I have a table with some columns & rows. I want to do insert and update on that table. Which action is faster inserting 1 row or updating 1row? Please let me know the answer with detailed explanation.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

ramana3327 (1/18/2014)
Hi,
I have a table with some columns & rows. I want to do insert and update on that table. Which action is faster inserting 1 row or updating 1row? Please let me know the answer with detailed explanation.

This sounds a whole lot like an interview question that I might ask of a Senior DBA or a test question I might ask if teaching. I suggest you lookup INSERT and UPDATE along with Indexes, Foreign Keys, and Triggers. After you discover the answer, then post it here with a "detailed explanation" and we'll let you know if you got it right or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

I would ask why it matters. If you need to change a value in a row, you need to update. If you need to put a new row in the table, you need to insert. Whether one is a few microseconds faster than the other isn't really relevant here, they're not substitutable for each other.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Further, it's completely dependent on a number of factors that you haven't outlined. You're not comparing apples to apples in this question.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

ramana3327 SSCoach Points: 19572 More actions · Answer 4

Hi,

Thanks to everyone.

I read that update is faster. Why because already table space is fixed, and you are just changing the value but for the insertion it needs to find the space for the insertion. I hope this is right one, please let me know.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Maybe, maybe not. There's a hell of a lot of factors involved.

Again, why is it a concern? It's not as if you can replace an insert with an update just because the update is a couple microseconds faster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Like I said, it's probably an interview question where there's not an actual correct answer other than "It Depends". It's a nice short question that requires a lot of knowledge of the things I mentioned without coming out to ask specific questions about each of those items and also prevents "rote" answers because it will cause an extended discussion if the person actually knows those things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

ramana3327 (1/19/2014)
Hi,
Thanks to everyone.
I read that update is faster. Why because already table space is fixed, and you are just changing the value but for the insertion it needs to find the space for the insertion. I hope this is right one, please let me know.

What about a case where modifying the data changes a clustered index key value? Will that still be faster? Again, you're comparing apples and hammers. UPDATE is one operation and INSERT is a different. They serve different purposes and have different requirements. One absolutely doesn't substitute for the other in any way, so why would you compare them?

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Poor ol' lonely DELETE. No one ever asks questions about that. :laugh:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Perry Whittle SSC Guru Points: 233792 More actions · Answer 9

could it be the OP is actually confused and is really enquiring about the MERGE option?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Greg Edwards-268690 SSC-Insane Points: 20667 More actions · Answer 10

Grant Fritchey (1/19/2014)
ramana3327 (1/19/2014)
Hi,
Thanks to everyone.
I read that update is faster. Why because already table space is fixed, and you are just changing the value but for the insertion it needs to find the space for the insertion. I hope this is right one, please let me know.
What about a case where modifying the data changes a clustered index key value? Will that still be faster? Again, you're comparing apples and hammers. UPDATE is one operation and INSERT is a different. They serve different purposes and have different requirements. One absolutely doesn't substitute for the other in any way, so why would you compare them?

And what if you actually care about the previous value?

Either for audit, or is a slow changing dimension?

Speed is usually only one of the factors in determining how you would choose one over the other.

And in some cases, you might find yourself doing both an update and an insert. :w00t:

So I guess I wouldn't have an answer, but might have some other questions.