How to improve performance for update statement?

Question

Suppose I have a lookup table mylookup(lkid, lkname, ...)

Then I have other 2 tables:

mytab(id, parentid, name, lookname, ...)
yourtab(id, parentid, ...)  --id, parentid coming from mytab

Then I have a update try to get lkid and it's parent lkid, the sql like:

 update yourtab set columnx = 
    case 
    when (select lkid from mylookup a join mytab b on a.lkname = b.lkname where b.id = c.parentid ) > 3
        and (select lkid from mylookup a join mytab b on a.lkname = b.lkname where b.id = c.parentid )  >
            (select lkid from mylookup where lkname = c.lkname )
    then 1
    else 0
    end
    from yourtab c

this sql performance is not good. (select lkid from mylookup a join mytab b on a.lkname = b.lkname where b.id = c.parentid ) is called 2 times for each row in yourtb.

how to rewrite sql to improve performance for this case?

Are you using this query in store proc ? , if yes you can place it temp variable and use it in when condition . — Peter Jerald, May 29 '14 at 19:05

score 0 · Answer 1 · answered May 29 '14 at 19:11

0

Maybe something like this..

It's untested would need a SQLFiddle with some sample data to really understand inner workings.

Update yourtab set columnx = 
case when  not null (
  SELECT lkid 
  FROM mylookup a 
  INNER join mytab b 
    on a.lkname = b.lkname 
  WHERE b.id = c.parentid 
    and a.lkid > 3 
    and a.lkname >  c.lkname) then 1 
  else 0 
 end 
FROM yourtab c

answered May 29 '14 at 19:11

xQbert

34,733
2
41
62

modify it like: update yourtab set columnx = 1 from yourtab where not null(...) – KentZhou May 29 '14 at 20:34

score 0 · Answer 2 · answered May 29 '14 at 20:21

update yourtab
   set columnx = 1
  FROM yourtab c
  join mylookup a 
    on a.lkname > c.lkname 
   and a.lkid > 3
  join mytab b 
    on a.lkname = b.lkname
   and b.id = c.parentid  
 where columnx <> 1


update yourtab
   set columnx = 0
  FROM yourtab c
  left join mylookup a 
    on a.lkname > c.lkname 
   and a.lkid > 3
  left join mytab b 
    on a.lkname = b.lkname
   and b.id = c.parentid  
 where columnx <> 0
   and ( a.lkname is null or b.id is null )

Two statements but is saves taking a lock if the value is already correct

How to improve performance for update statement?

2 Answers2