Re: Comparitive UPDATE speed

On Tue, 2002-10-01 at 16:51, Josh Berkus wrote:
> Relative performance question:
>
> I have 2 UPDATE queires in a function.
>
> table_a: 117,000 records
> table_b: 117,000 records
> table_c: 1.5 million records
>
> #1 updates table_a, field_2 from table_b, field_1 based on a joining field_3.
> Around 110,000 updates
> #2 updates table_a, field_5 from table_c, field_2 joining on field_3.
> Around 110,000 updates.
>
> #1 takes 5-7 minutes; #2 takes about 15 seconds. The only difference I can
> discern is that table_a, field_2 is indexed and table_a, field_5 is not.
>
> Is it reasonable that updating the index would actually make the query take
> 20x longer? If not, I'll post actual table defs and query statements.

Absolutely. You are doing lots of extra work.

For each of the 110,000 updates, you are deleting a leaf node from one
part of the index tree and then inserting it into another part of the
tree.

It will get even worse as you add more rows to table_a, since the
index tree will get deeper, and more work work must be done during
each insert and delete.

--
+------------------------------------------------------------+
| Ron Johnson, Jr. mailto:ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "What other evidence do you have that they are terrorists, |
| other than that they trained in these camps?" |
| 17-Sep-2002 Katie Couric to an FBI agent regarding the 5 |
| men arrested near Buffalo NY |
+------------------------------------------------------------+