Slow UPDATE statement

• Lucio Fassio

  Grasshopper

  Points: 22

  More actions

  May 17, 2007 at 10:37 am

  #116812

  I have this SQL:

   UPDATE ARC_SG40MasterDelibere_DelibereApprovate

   SET  FinePubblicazione = FinePubblicazione

   WHERE (IDDelibereApprovate IN

     (SELECT     TOP 200 IDDelibereApprovate

      FROM          TEMP_SG40))

   

  I get these messages:

  (1 row(s) affected)

  (1 row(s) affected)

  .....

  (1 row(s) affected)

  It seems that the update command is repeated 200 times; it takes 1' 10".

  I have this other SQL, apparently similar:

   UPDATE ARC_SG10MasterDelibere

   SET  DataOdG = DataOdG

   WHERE (IDMasterDelibera IN

     (SELECT     TOP 200 IDMasterDelibera

      FROM          TEMP_SG10))

  The message is:

  (200 row(s) affected)

  It takes 2"! What can I do to get the first SQL run faster ?

  Notes:

  1. the 2 tables gets a fake update just to force a replication

  2. ARC_SG10 is a master-detail 1:1 with ARC_SG40

  Thanks a lot for your time!

• Gordon Pollokoff

  SSCommitted

  Points: 1930

  More actions

  May 17, 2007 at 10:48 am

  #707217

  any chance ARC_SG40MasterDelibere_DelibereApprovate has an INSTEAD OF trigger on it, causing seperate updates to occur?

  Gordon Pollokoff

  "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

• Lucio Fassio

  Grasshopper

  Points: 22

  More actions

  May 17, 2007 at 10:54 am

  #707218

  Bingo!

  There is a trigger wich acts when the field FinePubblicazione is updated!

  I'll try updating another field.

  Thanks a lot!