heihei heihei - 14 days ago 8
SQL Question

Is it necessary to reduce update times even use group by statement?

There are two tables

Table A col1,col2,col3


  • 100,200,aaa;

  • 101,200,bbb;

  • 102,200,ccc;



Table B col1,col2,col3


  • aaa,1,ok;

  • aaa,2,ok;

  • aaa,3,ok;

  • bbb,1,fine;

  • bbb,3,fine;



Assume table A is a very large table and table B is a small table. In table B, col1 only have one col3 value, e.g, if col1 is 'aaa', col3 must be 'ok'

case 1:

update a set a.col2 = b.col3
from A a, B b
where a.col3 = b.col1


case 2:

update a set a.col2 = b.col3
from A a, (select col1, col3 from B group by col1,col3) b
where a.col3 = b.col1


The result of case 1 and case 2 are the same, but I just want to ask which statement is better? Whether case 1 will update table A for 5 times? Will the group by statement in case 2 consume more calcuation?

Answer

You should run EXPLAIN on both these queries to see how your database is actually handling things. That being said, one thing does stand out in terms of performance. In your first query:

update a set a.col2 = b.col3
from A a, B b
where a.col3 = b.col1

you are joining table A with B via the col3 and col1 columns. If there were an index on B.col1 then the join could proceed much faster than if the database were forced to do a full table scan of B. But an index on B.col1 probably would not help in your second query:

update a set a.col2 = b.col3
from A a, (select col1, col3 from B group by col1,col3) b
where a.col3 = b.col1

Here you are joining A to a table derived from B and as such no index is likely available. So I would opt for your first query.

By the way, you are using the old pre ANSI-92 syntax for joining in your first query and you might want to update it.

Comments