Thread

1. Comparitive UPDATE speed

   Josh Berkus <josh@agliodbs.com> — 2002-10-01T21:51:29Z

   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.
   
   -- 
   Josh Berkus
   josh@agliodbs.com
   Aglio Database Solutions
   San Francisco
   
   

2. Re: Comparitive UPDATE speed

   Ron Johnson <ron.l.johnson@cox.net> — 2002-10-02T10:48:01Z

   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.l.johnson@cox.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                             |
   +------------------------------------------------------------+