none
Update文の挙動について RRS feed

  • 質問

  • ストアドプロシージャで、あるテーブルに対して大量のデータをUpdateまたはInsertしようとしています。
    テスト環境で何度か流しているのですが、まったく同じデータを処理しても、Update文の処理時間が1度目とそれ以降で全く異なり、困っています。実運用では全く同じデータを処理することはないのですが、どうも挙動が理解できないので質問させていただきます。

    以下、プロシージャの流れです。

    1.外部からデータが書き込まれるテーブルを読み取る
    2.読み取ったデータを変換する
    3.変換済みデータをUpdateまたはInsertで取りこむ

    テストでは、1度目に起動した場合は3のUpdateが20分ほどかかります。(かなりDisk Queue Lengthのカウンタが振れます。)
    2度目に全く同じデータを読み取って処理させると、3のUpdateが5分ほどになっています。
    それ以外の処理時間にあまりずれはありません。(1.のテーブルは起動時に毎度Truncateされてからデータがロードされるので、読み取り&変換は毎度ほぼ同じ処理時間に落ち着いているようです。)

    当然1度目で新規のレコードはすべてInsertされているので、2度目はUpdateのみが実行されます。
    つまり、2度目の起動ではすでにあるデータに対してまったく同じデータで更新をかけていることになります。
    ちなみに、同じデータといっても更新日時、Row Versionなどは更新されています。

    これらの状況を総合すると、単純に更新されるコラムの数がそのまま処理時間の差に反映されているように思えます。

    そこでUpdate文の挙動について質問です。
    1)Update文は内部的には対象となる行がDelete&Insertされるものと理解していますが、その理解は正しいのでしょうか?
    Delete&Insertであれば更新のあったコラム数に限らず行に対してアクションが発生するので、更新にかかる処理コストは同じように思えます。内部的には更新箇所のみのUpdateであれば、この差が処理時間の差となっていると考えられます。

    2)Updateされた結果はデータキャッシュに格納されるのでしょうか?
    1度目で更新した結果がキャッシュに残っていたため、2度目ではUpdate時にディスクアクセスが軽減されたとも考えられます。

    いずれにしても、Updateの挙動が自分の意図しないものであったため、質問させていただきました。
    よろしくお願いいたします。

    noh
    2008年6月21日 17:32

回答

  •  米田です。

     

    >1)Update文は内部的には対象となる行がDelete&Insertされるものと理解していますが、その理解は正しいのでしょうか?
     間違っています。

     そのレコードをDelete 後、Insertが必要か、一部データの更新ですませるかは定義やそのページの状況で異なります。

     またSQL Server のバージョンも含め、変化します。

     

     影響が気になるなら、完全復旧モデルに設定した検証用のデータベースを準備して、

    ・BACKUP LOG

    一回目

    ・BACKUP LOG

    二回目

    ・BACKUP LOG

    とでもして、後ろ2つのバックアップデータサイズを比較してみてください。

     

    2008年6月21日 22:35

すべての返信

  •  米田です。

     

    >1)Update文は内部的には対象となる行がDelete&Insertされるものと理解していますが、その理解は正しいのでしょうか?
     間違っています。

     そのレコードをDelete 後、Insertが必要か、一部データの更新ですませるかは定義やそのページの状況で異なります。

     またSQL Server のバージョンも含め、変化します。

     

     影響が気になるなら、完全復旧モデルに設定した検証用のデータベースを準備して、

    ・BACKUP LOG

    一回目

    ・BACKUP LOG

    二回目

    ・BACKUP LOG

    とでもして、後ろ2つのバックアップデータサイズを比較してみてください。

     

    2008年6月21日 22:35
  • 回答ありがとうございました。

    UpdateはDelete&Insertではないということで、大変勉強になりました。
    今後はこのことに注意を払ってコーディングしようと思います。
    ちなみに、今回使用していたSQL Serverのバージョンンは2005です。

    ご指摘いただいたバックアップデータサイズの比較ですが、やはり1回目のサイズの方が大きかったです。つまり、2回目では一部データの更新のみで処理されているようです。

    ありがとうございました。


    2008年6月22日 16:27