g*****r
发帖数: 130 | 1 我有一个表，9百多万records. I need to update the ageCode based on the value
of the age field for each row. For example, if age is between 20 and 30, set
ageCode to A, if age is between 30 to 40, set ageCode to B; otherwise, set
ageCode to C. In this case, which of the following is faster?
1. update the whole table using case statements. This means it only executes
update once.
2.update the table three times using the "where" clause? This means it
executes the update statement three times.
Thank you. |
c*****d
发帖数: 6045 | |
g*****r
发帖数: 130 | 3 Thank you! Could you tell me why?
【在 c*****d 的大作中提到】
: 1
|
g***l
发帖数: 18555 | 4 UPDATE的速度一是看有没有INDEX，二是看UPDATE多少因为会有LOG，因为你UPDATE所有
RECORDS，用WHERE反而会因为没有INDEX而变慢 |
y****w
发帖数: 3747 | 5 假如你有权限又每条都更新的话,建个新表倒数据，改名。
value
set
set
executes
【在 g*****r 的大作中提到】
: 我有一个表，9百多万records. I need to update the ageCode based on the value
: of the age field for each row. For example, if age is between 20 and 30, set
: ageCode to A, if age is between 30 to 40, set ageCode to B; otherwise, set
: ageCode to C. In this case, which of the following is faster?
: 1. update the whole table using case statements. This means it only executes
: update once.
: 2.update the table three times using the "where" clause? This means it
: executes the update statement three times.
: Thank you.
|
y****9
发帖数: 144 | 6 in (1) you do full table scan once
in (3) you do full table scan three times
to speed up (1), you may choose parallel executions
【在 g*****r 的大作中提到】
: Thank you! Could you tell me why?
|
y****9
发帖数: 144 | 7 Also with a 9m table containing age and agecode column, seems this is a
violation of 3 normal form for table design.
An alternative design may be creating a look up table with two column age
and agecode. the original table only includes a col called date of birth,
the acutual aga could be computed - in this design you don't need to do such
update in the first place. But i am just speaking in general, maybe your
application is some kind of data warehousing, denomalization could be valid
..
【在 y****9 的大作中提到】
: in (1) you do full table scan once
: in (3) you do full table scan three times
: to speed up (1), you may choose parallel executions
|
n*w
发帖数: 3393 | 8 这种情况，data warehouse也应该搞个age dimension。
data warehouse的fact table也经常是3nf的（但不一定总是）。denormalize常在
dimension table上发生。
such
valid
【在 y****9 的大作中提到】
: Also with a 9m table containing age and agecode column, seems this is a
: violation of 3 normal form for table design.
: An alternative design may be creating a look up table with two column age
: and agecode. the original table only includes a col called date of birth,
: the acutual aga could be computed - in this design you don't need to do such
: update in the first place. But i am just speaking in general, maybe your
: application is some kind of data warehousing, denomalization could be valid
: ..
|
y****w
发帖数: 3747 | 9 define a view for such simple logic.
such
valid
【在 y****9 的大作中提到】
: Also with a 9m table containing age and agecode column, seems this is a
: violation of 3 normal form for table design.
: An alternative design may be creating a look up table with two column age
: and agecode. the original table only includes a col called date of birth,
: the acutual aga could be computed - in this design you don't need to do such
: update in the first place. But i am just speaking in general, maybe your
: application is some kind of data warehousing, denomalization could be valid
: ..
|
s*********1
发帖数: 2 | 10 建议你新建一个表，加一个AgeGroup的column，insert，select，会比update快一些，
然后用新表replace旧表吧。 |
b*****x
发帖数: 3786 | 11 see if u can get a way with a computed column or a view.
value
set
set
executes
【在 g*****r 的大作中提到】
: 我有一个表，9百多万records. I need to update the ageCode based on the value
: of the age field for each row. For example, if age is between 20 and 30, set
: ageCode to A, if age is between 30 to 40, set ageCode to B; otherwise, set
: ageCode to C. In this case, which of the following is faster?
: 1. update the whole table using case statements. This means it only executes
: update once.
: 2.update the table three times using the "where" clause? This means it
: executes the update statement three times.
: Thank you.
|
g***l
发帖数: 18555 | 12 这个最好用SELECT INTO
【在 s*********1 的大作中提到】
: 建议你新建一个表，加一个AgeGroup的column，insert，select，会比update快一些，
: 然后用新表replace旧表吧。
|
