none
同じ値のUPDATE実行について RRS feed

  • 質問

  • いつもお世話になっております。

    SQLのUPDATE文発行についての質問です。

    UPDATEしようとする内容と、すでにテーブルに存在するレコードの値が
    同じであった場合、UPDATE文が実行されずにスキップされているように
    見受けられるのですが、実際はどうなっているのでしょうか。

    スキップされるのであれば、その情報をどこから得たのかも教えていただけると助かります。
    例えば、 人から聞いた、 ネットに載ってた、 本に載ってた …etc

    よく調べればどこかにあるのかもしれませんが、私がちょっと調べた限りでは見つからなかったので。

    ちなみになぜ気になったかといえば、
    C#でプログラミングするときにDataAdapterやDataTableを使いますが、
    Select文で得たDataTableの内容のうち、処理上で変更されなかったものまでUPDATEされるのかどうか
    気になったからです。実際はRowState等でC#側でUPDATEするしないを判断できてるみたい
    でしたが、SQL Server側はそういう処理をしないのか気になりましたので。

    よろしくお願いします。
    2009年4月1日 14:29

回答


  • 次のようなトリガを作成して試してみると、違う値で更新した場合はTESTUPDDATE が更新されますが、同じ値で更新した場合はTESTUPDDATE が更新されません。

    Create Trigger [test].[utg_TEST_upddate]
    on [test].[TEST]
    For Update
    As
    Begin
     set nocount on
     update TEST set TESTUPDDATE = getdate()
     where TESTID=(select TESTID from deleted)
    End

    またInstead Of Updateトリガでも試してみましたが、やはり同じ値で更新する場合には発動されませんでした。
    以上より、SQL Serverは同じ値で更新する場合、更新をスキップしているのではないでしょうか?

     トリガーをコピペして、自分の環境(SQL Server 2008 Standard)で試してみたんですが、同じ値で更新してもトリガーも更新する度に実行されています。
     ちなみに実行はSQL Server Management Studio上で実行しています。
    (その他のバージョンは、環境は作成しきれていないので、調べられていないです)

     同じ値で更新した場合のログの扱いですが、ログの確認方法を調べているような状態なので、不明です。

     ちなみに、パラメータを使わず、アドホックなクエリーを実行した場合、SQLの構文が全て同じでないと、キャッシュされたクエリープランが使用されないため、SELECTやUPDATEが遅くなります。
     逆にパラメータクエリーを使ったり、全く同一のクエリーを発行すると、キャッシュされたクエリープランが使用されますので、SELECTやUPDATEが早くなります。
     今回の場合、「同じ値での更新が速くなる」というのは、キャッシュされたクエリープランが使用されたからだとは思うのですが…。

    2009年4月12日 15:25
  • やはり私の勘違いでした。というよりServer Management StudioおよびVS2008の動作に惑わされました。検証不足でした。すみません。
    Server Management Studioにおいてグリッド形式でテーブルの値を表示し、それを手で同じ値に修正した場合、Update文が発行されませんでした。したがって更新トリガも発動しませんでした。同じ値の更新であっても、Update文を直接実行すると、きちんと更新トリガが発動しました。
    プロファイラで確認しました。VS2008も同様の動作をしました。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2009年4月13日 2:43

すべての返信

  • SQL Server 上で、更新処理を実施する場合に、現在挿入されている値をわざわざ確認後、更新をするかしないかを判断していないと思います。(コストが逆に掛かるので)
    アプリケーション側で同じ値の更新処理をSQL Server側へ処理を行わないという制御を実施していれば別ですが。

    SQL Server プロファイラ から トレース情報を見ることにより、簡単に処理が行われているか行われていないかについて、確認することができます。

    ちなみに、どんな挙動をみて、スキップされているように思われたのかについて興味がありますね。

    2009年4月1日 14:52
  • C#でプログラミングするときにDataAdapterやDataTableを使いますが、
    Select文で得たDataTableの内容のうち、処理上で変更されなかったものまでUPDATEされるのかどうか
    気になったからです。実際はRowState等でC#側でUPDATEするしないを判断できてるみたい
    でしたが、SQL Server側はそういう処理をしないのか気になりましたので。

    DataTableでは同じ値かどうかを確認しているわけではなく、単に編集されれば書かれているようRowStateがModifiedに変わります。つまり、この行は変更されましたという目印が付きます。DataAdapterのUpdateメソッドを実行すると、このModifiedになっている行を探し出し、データベースに対してSQLのUpdate文を発行するという仕組みです。
    もし同じ値を入力された場合はSQLのUpdate文を発行したくなければ、各列についてDataRowVersion.OriginalとDataRowVersion.Currentを比較し、全て同じであればAcceptChangesメソッドを実行して変更が無かったということにすれば実現できます。
    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2009年4月2日 0:28
  • 回答ありがとうございます。

    返信が遅くなり恐縮です。


    NOBTAさん

    >SQL Server 上で、更新処理を実施する場合に、現在挿入されている値をわざわざ確認後、更新をするかしないかを判断していないと思います。(コストが逆に掛かるので)

    SQL Server側ではしていないのですね。

    >ちなみに、どんな挙動をみて、スキップされているように思われたのかについて興味がありますね。

    ということなので、
    私の作ったバッチの一部を抜粋してみました。C#です。

    for(int i=1;i<5000;i++)
    {
       using(Sqlcommand sqlCmd = new SqlCommand())
       {
          sqlCmd.Connection = sqlCon2;
          sqlCmd.CommandText = "UPDATE testtable WITH(NOWAIT) SET COLUMN = 'after' WHERE NUMBER ='" + i + "'";
          sqlCmd.ExecuteNonQuery();
          Console.WriteLine(i.ToString());
       }
    }

    このコードの前にテーブルを読み込んだりする処理はありません。
    NUMBERは主キーです。
    COLUMNの値を'before'にしておいて、このバッチを1度実行し、少し経ってから(終わらないうちに)もう1度実行します。
    よって、同じバッチが2つ流れることになります。
    このとき、最初の実行より後からの実行の方が、コンソールの表示間隔が早く、途中で最初の実行に追いつきエラーとなりました。
    肉眼でも速度の違いが確認できたくらいですから、速度の差はあると思います。
    ただ、これによってUPDATE文をスキップしてると言えるかと言われると、言えないですね…。
    C#側で何かやってるんでしょうか。



    trapemiyaさん

    いつもお世話になっております。

    >もし同じ値を入力された場合はSQLのUpdate文を発行したくなければ、各列についてDataRowVersion.OriginalとDataRowVersion.Currentを比較し、全て同じであればAcceptChangesメソッドを実行して変更が無かったということにすれば実現できます。
    RowStateの動作はわかっていたつもりでしたが、教えてくださった方法だと全レコード編集したとしてもUPDATE文を実行しなくて済みそうですね。
    参考にさせて頂きます。

    2009年4月6日 12:16
  • 回答ありがとうございます。

    返信はNOBTAさんのところに一緒に書いてしまいましたので、そちらを見てくださいm(_ _)m
    2009年4月6日 12:32
  • このとき、最初の実行より後からの実行の方が、コンソールの表示間隔が早く、途中で最初の実行に追いつきエラーとなりました。
    肉眼でも速度の違いが確認できたくらいですから、速度の差はあると思います。
    ただ、これによってUPDATE文をスキップしてると言えるかと言われると、言えないですね…。

    次のようなトリガを作成して試してみると、違う値で更新した場合はTESTUPDDATE が更新されますが、同じ値で更新した場合はTESTUPDDATE が更新されません。

    Create Trigger [test].[utg_TEST_upddate]
    on [test].[TEST]
    For Update
    As
    Begin
     set nocount on
     update TEST set TESTUPDDATE = getdate()
     where TESTID=(select TESTID from deleted)
    End

    またInstead Of Updateトリガでも試してみましたが、やはり同じ値で更新する場合には発動されませんでした。
    以上より、SQL Serverは同じ値で更新する場合、更新をスキップしているのではないでしょうか?
    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2009年4月6日 15:17
  • 処理速度の違いですが、同じ値でUPDATEした場合はログファイルに出力しないようです。
    (英語を流し読みした程度しなので、あんまり自信がありませんが…)
    http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

     あと、USのTechnetフォーラムですが、SQL Server 2000とSQL Server 2005以降では(同じ値でのUPDATEについて)挙動が違うみたいな情報もありましたので、それ関連しているかもしれません。
    http://social.technet.microsoft.com/Forums/ja-JP/sqldatabaseengine/thread/c01f7ef6-a7bc-45c0-8f37-c20cac7d1b14

     その他、trapemiyaさんが指摘された、トリガーが実行される/されない件については仕様としてどうなんでしょう?
    確かに同じ値だったらトリガーを実行する意味がないと言えばないですけど、更新ログを取るため等にトリガーを作成しているのであればトリガーが実行されるべきと思いますし。
    (まあ、MSに問い合わせたら「By Design」と言われそうですが)

     自分も気になるので、環境を作って確認してみたいと思います。
    2009年4月7日 3:30
  • アプリケーションからではなく、直接 クエリを実行した結果なのですが、同じ内容のUPDATE文を実行した場合においても、トレース上には UPDATE 文がトレースされ、該当のテーブルに対して ロックを取得していました。
    ただ、だからと言って、UPDATE がスキップされているかどうかは、その先の話になるので、なんとも言えませんね。
    これ以上は SQL Server の内部動作となるんでしょうね。

    CatTail さんのログファイルに出力しないという内部動作を、SQL Server が行っている可能性は確かに考えられますね。
    ちょっとテストしてみます。

    2009年4月7日 10:05

  • 次のようなトリガを作成して試してみると、違う値で更新した場合はTESTUPDDATE が更新されますが、同じ値で更新した場合はTESTUPDDATE が更新されません。

    Create Trigger [test].[utg_TEST_upddate]
    on [test].[TEST]
    For Update
    As
    Begin
     set nocount on
     update TEST set TESTUPDDATE = getdate()
     where TESTID=(select TESTID from deleted)
    End

    またInstead Of Updateトリガでも試してみましたが、やはり同じ値で更新する場合には発動されませんでした。
    以上より、SQL Serverは同じ値で更新する場合、更新をスキップしているのではないでしょうか?

     トリガーをコピペして、自分の環境(SQL Server 2008 Standard)で試してみたんですが、同じ値で更新してもトリガーも更新する度に実行されています。
     ちなみに実行はSQL Server Management Studio上で実行しています。
    (その他のバージョンは、環境は作成しきれていないので、調べられていないです)

     同じ値で更新した場合のログの扱いですが、ログの確認方法を調べているような状態なので、不明です。

     ちなみに、パラメータを使わず、アドホックなクエリーを実行した場合、SQLの構文が全て同じでないと、キャッシュされたクエリープランが使用されないため、SELECTやUPDATEが遅くなります。
     逆にパラメータクエリーを使ったり、全く同一のクエリーを発行すると、キャッシュされたクエリープランが使用されますので、SELECTやUPDATEが早くなります。
     今回の場合、「同じ値での更新が速くなる」というのは、キャッシュされたクエリープランが使用されたからだとは思うのですが…。

    2009年4月12日 15:25
  •  トリガーをコピペして、自分の環境(SQL Server 2008 Standard)で試してみたんですが、同じ値で更新してもトリガーも更新する度に実行されています。

    私は会社で試しましたのでどの環境か忘れてしまいましたので、明日(もう今日ですね)にでも確認してみます。私が確認した方法は、SQL Server Management Studio上でテーブルのデータをグリッドで表示させ、そこの値を手打ちにて直接編集し、更新日時(TESTUPDDATE)が自動的に更新されるかどうかというものです。ひょっとしたらSQL Server Management Studioが賢くて、同じ値を入力した際はUpdate文を投げないのかもしれません。ちょっと考えにくいですが・・・。
    いずれにしましても、追加テストととしてupdate文を実行することを加え、もう一度確認したいと思います。
    ちなみにInstead Of Updateトリガの方も同様に、同じ値でも発動しましたでしょうか?

    #同じ値の更新であっても更新トリガが発動してくれる方がありがたいので、私の勘違いであればよいのですが・・・。もしくは何かオプションでもあるのかな???
    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2009年4月12日 15:53
  • やはり私の勘違いでした。というよりServer Management StudioおよびVS2008の動作に惑わされました。検証不足でした。すみません。
    Server Management Studioにおいてグリッド形式でテーブルの値を表示し、それを手で同じ値に修正した場合、Update文が発行されませんでした。したがって更新トリガも発動しませんでした。同じ値の更新であっても、Update文を直接実行すると、きちんと更新トリガが発動しました。
    プロファイラで確認しました。VS2008も同様の動作をしました。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/
    2009年4月13日 2:43
  • みなさまご回答ありがとうございます。

    いろいろな検証方法があることを知り、とても参考になりました。
    また、自分は全然足元にも及ばないなーと痛感しました。
    もっと勉強しなければ。

    今後ともよろしくお願いします。

    2009年4月13日 13:09