SqL Query performance issue

1
votes

The query below seems to take about 20 seconds to execute and because it's run multiple times in a single transaction it's affecting the the performance badly. 

[update table1
            set column3 = 'new_str'  
          where column1||','||column2 in  
        (select table1.column1||','||column2  
           from table1   
       join table2 on table1.column1 = table2.column1  
      where table2.column4 = 'value4'  
        and table1.column2 = 'value2'  
        and column3 = 'old_str')]

table 1
column1 - char (12) - primary key
column2 - char (30) - primary key
column3 - char (25)

table2
column1 - char (12) - primary key (foreign key in table 1)
column4 - char (12)

The above tables have about 1009578 and 1082555 records.

3
sqlperformance
With this many records, I'd look into saving more time, other than query optimizing. Checking indexes that could be updated, removed or optimized, and utilizing a view with some basic criteria that would eliminate some of the records you're combing through. Maybe records from the last month, or ones that are in X status, something along those lines. What DB are you using? - Just Aguy

3 Answers

0
votes

Can't test it, but in my opinion breaking up that criteria based in a calculated field should speed up the update quite a lot. Something (might be something missing) like this should work better:

[update table1
         set column3 = 'new_str'  
          where column1 in   
        (select table1.column1  
           from table1   
      where table1.column2 = 'value2'  
        and column3 = 'old_str')
        and 
         column2 in 
    (select table2.column2  
           from table2   
      where table2.column1 = column1
        and table2.column4 = 'value4')
        ]
0
votes

I think you are doing unnecessary query to Table1. Try this:

 update table1 t1
 set column3 = 'new_str'  
 where EXISTS 
  (select *          
   from table2 t2
   where 
     t1.column1 = t2.column1 -- this is your link from t1 to t2
     and t2.column4 = 'value4'  
     and t1.column2 = 'value2'  
     and t2.column3 = 'old_str'
   )
0
votes

I guess that IN cause is not necesary here:

update table1
    set column3 = 'new_str' 
    from table1 join table2 on table1.column1 = table2.column1  
      where table2.column4 = 'value4'  
        and table1.column2 = 'value2'  
        and table1.column3 = 'old_str'

Write us with solutions is fastest ;] !