How to optimize UPDATE statement?

Question

I have a table with almost 1 million records and another table with a few hundred records. I would like to update the large table with values from the small table with the following

UPDATE tableA ta, tableB tb
SET price=tb.price
WHERE ta.id=tb.id

Using the above SQL statement, the query is taking a really long time (more than 1 hour). Is there a method that I can use to make this operation faster?

Here is the schema for both tables.

Table A

id         name   descrip   region   price
0          a      abc       def      1.7
1          b      abc       def      2.2
3          c      abc       def      3.4
4          d      abc       def      5.3
....       .      ...       ...      ...
999999     e      abc       def      4.5
1000000    f      abc       def      7.9

Table B

id      price
0       0.7
1       2.5
3       1.9
4       7.9

Result Table A

Table A

id         name   descrip   region   price
0          a      abc       def      0.7
1          b      abc       def      2.5
3          c      abc       def      1.9
4          d      abc       def      7.9
....       .      ...       ...      ...
999999     e      abc       def      4.5
1000000    f      abc       def      7.9

zerkms · Accepted Answer · 2011-06-29 02:28:09Z

2

If your tableB has index, that covers id (obviously it is) - then you have no other ways to speed it up. Since the slowest thing here is physical changing of the value.

Probably you can change your where to:

WHERE ta.id=tb.id and ta.price <> tb.price

to avoid of updating the price to the same value

answered Jun 29, 2011 at 2:28

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to optimize UPDATE statement?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related