INSERT or UPDATE, which is faster? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

INSERT or UPDATE, which is faster?

INSERT or UPDATE, which is faster?# Database - 数据库

n*u2008-09-23 07:09

1 楼

I am writing C# with SQL statement to update a record which has four text
fields (Introduction, What am I looking for, etc)
When a user, first time, inputs texts into these fields, Of course use
INSERT.
But when the user decides to modify one of these, use UPDATE or INSERT?
If I choose INSERT, I need to delete old record, INSERT a new one.
If I choose UPDATE, I could just UPDATE record, but I am afraid the text
field may take longer time to update.
I feel like INSERT is always faster than UPDATE

t*i2008-09-23 07:09

2 楼

do insert when you have a new row; do update when you need to modify a row.
delete + insert is for sure more expensive than a update.

n*u2008-09-23 07:09

3 楼

Thanks!

【在 t*********i 的大作中提到】

: do insert when you have a new row; do update when you need to modify a row.
: delete + insert is for sure more expensive than a update.

c*d2008-09-23 07:09

4 楼

嗯,如果要操作的数据量小,update要比delete+insert快
如果要操作的数据量大,update要比truncate+insert慢

【在 t*********i 的大作中提到】

: do insert when you have a new row; do update when you need to modify a row.
: delete + insert is for sure more expensive than a update.

x*e2008-09-23 07:09

5 楼

I have to say, this is very misleading.
It really depends on what database you are talking about and the design.

【在 t*********i 的大作中提到】

: do insert when you have a new row; do update when you need to modify a row.
: delete + insert is for sure more expensive than a update.

B*g2008-09-23 07:09

6 楼

砸变truncate了？

【在 c*****d 的大作中提到】

: 嗯,如果要操作的数据量小,update要比delete+insert快
: 如果要操作的数据量大,update要比truncate+insert慢

c*d2008-09-23 07:09

7 楼

大数据量的时候用truncate+insert比Update快
陈述一个事实,哈哈

【在 B*****g 的大作中提到】

: 砸变truncate了？

n*u2008-09-23 07:09

8 楼

I agree.
Last time it would took 3 days to update 320,000 records (just update MSN
etc.)
But I truncated table first, INSERT... SELECT..., 30 seconds, done.

【在 c*****d 的大作中提到】

: 大数据量的时候用truncate+insert比Update快
: 陈述一个事实,哈哈

S*t2008-09-23 07:09

9 楼

truncate is different from delete

【在 n****u 的大作中提到】

: I agree.
: Last time it would took 3 days to update 320,000 records (just update MSN
: etc.)
: But I truncated table first, INSERT... SELECT..., 30 seconds, done.

n*u2008-09-23 07:09

10 楼

Yes, I talked to my colleague this morning. He thinks DELETE might take long
time. So his suggestion is UPDATE.
Well I will look at the original PHP code to find out how that team did.Then
I would write similar one in C#.
Thanks all!

【在 S*********t 的大作中提到】

: truncate is different from delete

B*g2008-09-23 07:09

11 楼

read coolbid's post again.

long
Then

【在 n****u 的大作中提到】

: Yes, I talked to my colleague this morning. He thinks DELETE might take long
: time. So his suggestion is UPDATE.
: Well I will look at the original PHP code to find out how that team did.Then
: I would write similar one in C#.
: Thanks all!

n*u2008-09-23 07:09

12 楼

Never mind, just got fired half hour ago. hehe

【在 B*****g 的大作中提到】

: read coolbid's post again.
:
: long
: Then

c*e2008-09-23 07:09

13 楼

not necessarily

【在 t*********i 的大作中提到】

: do insert when you have a new row; do update when you need to modify a row.
: delete + insert is for sure more expensive than a update.

c*e2008-09-23 07:09

14 楼

what?

【在 n****u 的大作中提到】

: Never mind, just got fired half hour ago. hehe

n*u2008-09-23 07:09

15 楼

A senior guy was fired two hours before I got fired.
Not sure what happened in big boss's mind.

【在 c*******e 的大作中提到】

: what?

n*62008-09-23 07:09

16 楼

What happends when you need the log file?
Truncate does not generate the log, does not fire the trigger.

【在 c*****d 的大作中提到】

: 大数据量的时候用truncate+insert比Update快
: 陈述一个事实,哈哈

c*d2008-09-23 07:09

17 楼

我ft
为什么呀?

【在 n****u 的大作中提到】

: Never mind, just got fired half hour ago. hehe

a*t2008-09-23 07:09

18 楼

if the updated columns are not indexed, update is faster

【在 n****u 的大作中提到】

: I am writing C# with SQL statement to update a record which has four text
: fields (Introduction, What am I looking for, etc)
: When a user, first time, inputs texts into these fields, Of course use
: INSERT.
: But when the user decides to modify one of these, use UPDATE or INSERT?
: If I choose INSERT, I need to delete old record, INSERT a new one.
: If I choose UPDATE, I could just UPDATE record, but I am afraid the text
: field may take longer time to update.
: I feel like INSERT is always faster than UPDATE

c*e2008-09-23 07:09

19 楼

comfort

【在 n****u 的大作中提到】

: A senior guy was fired two hours before I got fired.
: Not sure what happened in big boss's mind.

c*d2008-09-23 07:09

20 楼

不乖,发几个包子安慰一下吧

【在 c*******e 的大作中提到】

: comfort

c*e2008-09-23 07:09

21 楼

嗯,本版很穷,才66.8个伪币,都发了

【在 c*****d 的大作中提到】

: 不乖,发几个包子安慰一下吧

n*u2008-09-23 07:09

22 楼

多谢老大啦

【在 c*******e 的大作中提到】

: 嗯,本版很穷,才66.8个伪币,都发了

n*62008-09-23 07:09

23 楼

how to make/create bao zi?

【在 c*******e 的大作中提到】

: 嗯,本版很穷,才66.8个伪币,都发了

w*e2008-09-23 07:09

24 楼

多灌水

【在 n********6 的大作中提到】

: how to make/create bao zi?

B*g2008-09-23 07:09

25 楼

此处不留爷，自有留爷处。

【在 n****u 的大作中提到】

: A senior guy was fired two hours before I got fired.
: Not sure what happened in big boss's mind.

k*e2008-09-23 07:09

26 楼

Hence truncate doesn't work with replication and logshipping

【在 n********6 的大作中提到】

: What happends when you need the log file?
: Truncate does not generate the log, does not fire the trigger.

j*n2008-09-23 07:09

27 楼

truncate vs. delete
http://www.mssqltips.com/tip.asp?tip=1080

【在 k********e 的大作中提到】

: Hence truncate doesn't work with replication and logshipping

t*i2008-09-23 07:09

28 楼

My gosh!
Just read this...Hope you feel better now.

【在 n****u 的大作中提到】

: Never mind, just got fired half hour ago. hehe

t*i2008-09-23 07:09

29 楼

I am curious...can you give a example (or document) that a update would be
more expensive than a delete+insert.
I kind of think all relational DBs have similar structures...

【在 x***e 的大作中提到】

: I have to say, this is very misleading.
: It really depends on what database you are talking about and the design.

x*e2008-09-23 07:09

30 楼

the different between structures, you should be able to get some reference
online.
One example here
one of the big difference between SQL server and Oracle is the transaction
log.
SQL use single log process, while Oracle is is multi.
Based on that, lots of code need to be re-writen.
So you can not just say it is universal for all DBs.
They could be the same in the future, but not now.
Give you one example for update could be much more expensive.
You have an update trigger for the table.
Generall

【在 t*********i 的大作中提到】

: I am curious...can you give a example (or document) that a update would be
: more expensive than a delete+insert.
: I kind of think all relational DBs have similar structures...

t*i2008-09-23 07:09

31 楼

I don't know much about Sql server, here is what I found:
http://www.windowsitlibrary.com/Content/77/21/1.html
A direct, same-page Update occurs when a record is deleted from a page and
inserted onto that same page, usually because of increasing row length.
Because the record does not move to a new page, none of the indexes needs to
be updated, unless, of course, an indexed field is updated. This type of
Update gets two records, an op code of 5 (Delete), followed by an op code of
6 (Indirect Ins

m*82008-09-23 07:09

32 楼

in MS SQL,internally, an update = delete + insert
so u figure the rest.

x*e2008-09-23 07:09

33 楼

I do not know about that.
I only know it is case by case.
And I am not work for MS either....
I can not image in the following case the update >= insert + delete
in any of the SQL env.
you have a table
Id1, id2, text
while ID1 is unique index, id2 is an int none indexed column and text is a
text column.
and you want to update the id2 column....based on id1....

to
of

【在 t*********i 的大作中提到】

: I don't know much about Sql server, here is what I found:
: http://www.windowsitlibrary.com/Content/77/21/1.html
: A direct, same-page Update occurs when a record is deleted from a page and
: inserted onto that same page, usually because of increasing row length.
: Because the record does not move to a new page, none of the indexes needs to
: be updated, unless, of course, an indexed field is updated. This type of
: Update gets two records, an op code of 5 (Delete), followed by an op code of
: 6 (Indirect Ins

t*i2008-09-23 07:09

34 楼

Just see your reply :)
for the case you mentioned.
**************************************************
you have a table
Id1, id2, text
while ID1 is unique index, id2 is an int none indexed column and text is a
text column.
and you want to update the id2 column....based on id1....
****************************************************
I still think in Oracle, Update is quicker than delete+insert
in SQL server (and all other DB system that one update = one delete + one
insert), that should be no much

L*d2008-09-23 07:09

35 楼

Another example is, if you try to update a blob or update a big chunk of
data that could cause chains.
//i am not a dba