UPDATE がうまくいかない

質問

はじめまして。

SQL server 2005 と VB2005 を使用してテーブルの更新を
　連続で実行すると、なぜか、250件更新するごとに
　次の9レコード分更新クエリのレスポンスの時間が異常に長い
　のです。その後、また、250件更新の更新クエリは早く
　処理をされるのですが、更に次の9レコード分更新クエリの
　レスポンス時間が異常に長いのです。以後250件レコードの更新
　ごとに9レーコド分更新のレスオンスの時間が異常に長く繰り返
　されます。たぶんエラーが発生していると思われますが、
　どうして250件レーコドに9件のレコードの更新クエリがエラー
　でるのかわかりません。仕方ないので、エラーの出たレコード
　を事前に削除して再度実行しても、やはり、250レコード
　ごとに9件のレコードがエラーが出てレスポンスに時間が掛かります。
　なにか、SQL Serverに設定するのがあるのか、または
　SQL文に間違いがあるのか、教えて下さい。
　接続方法はOLEDBで接続しています。OLEDBの接続では
　ダメなのでしょうか？。

　下にソースを記載します。

dim 肝炎B as long
dim cq as string
dim Db As new System.Data.OleDb.OleDbConnection
Dim CDBkanjya As System.Data.OleDb.OleDbDataReader
Dim CDBkanjyaC As new System.Data.OleDb.OleDbCommand
Dim CDBkanjyaCw As new System.Data.OleDb.OleDbCommand

cq = " SELECT 患者ID , 患者名 " & _
" FROM kanjya " & _
" ORDER BY 患者ID " & _
" "

Db.ConnectionString = "Provider=SQLOLEDB;Data Source=kokoro00;User ID=sa;Password=kokorocenter;Initial Catalog=kokoro"
Db.Open()
CDBkanjyaC.Connection = Db
CDBkanjyaCw.Connection = Db

CDBkanjyaC.CommandText = cq
CDBkanjya = CDBkanjyaC.ExecuteReader

Do While CDBkanjya.Read

.....
.....
.....
.....
.....

cq = " UPDATE kanjya "
cq += " SET 肝炎B = " & 肝炎B.ToString
cq += " WHERE 患者ID = " & CDBkanjya.Item("患者ID").ToString & " "

CDBkanjyaCw.CommandText = cq
CDBkanjyaCw.ExecuteNonQuery()

Loop

CDBkanjya.Close()
CDBkanjyaC.Dispose()
CDBkanjyaCw.Dispose()
Db.Close()

　　尚、SQL Server でうまくいかないので、データベースをアクセスのMDB
　　ファイルに変換して、接続方法を

Db.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=c:\sbj\kokoro\bin\kokoro.mdb"

の JETエンジンでOLEDB接続して、その他は全く同じソースで実行した
　　場合は、なんら問題なく、全件更新出来ます。

回答No.4

kero_mio · ベストアンサー

どうやら私が直接回答しなくても、ヒントから答えを導き出したようですね。
READ_COMMITTED_SNAPSHOTは、2005から追加された分離レベルになりますのである程度、使えるんですが、READ_COMMITTED_SNAPSHOTは、SQL Serverのtempdbに変更前後の内容を書きだしにいくので、若干レスポンスは落ちます。
そのため、READ_COMMITTED_SNAPSHOTを使う場合は、SQL Serverにあるtempdbの設定やチューニングをきちんと行うことが不可欠になりますので、そこだけご注意ください。

＞出来るだけ複雑にしたくないのが正直なところです。

作ったクラスは複雑にはなりますが、他のクラスからみたら、Access、SQL Serverを意識させないので、むしろ他のクラス達から見たら、簡単になるんですけどね・・・
工数の兼ね合いとかおありだと思いますので、最終手段としてご検討頂けるといいかもしれません。

ひとまず、分離レベルをお試し下さい。

お礼

色々ありがとうございました。
大変参考になりました。
分離レベルの運用でいきたいと思います。

回答No.3

kero_mio

日本語がヘンだったので、書きなおします。

＞ＭＤＢのJETエンジンにどの様にして接続するのか

System.Data.SqlClientを使ってAccess MDBにはアクセスできません。
なので、Access MDBの場合は、OleDb系を使うしかありません。

＞出来たら　私も System.Data.SqlClient　を使用したいのですが、今回のプログラムは、
＞SQL Server で運用したソースをそのまま、接続文字列だけの変更だけで、
＞アクセスのデータベースファイルＭＤＢのJETエンジンにも使用したいのです。

Access MDBは、OleDb、SQL Server は、System.Data.SqlClientと接続方法を切り替えるようなクラスを作り、後は行う処理によって、接続方法の切り替えが自動・任意でできる仕組みを作ってみては如何でしょうか？
その自作したクラスの外部からは、OleDbか、System.Data.SqlClientか意識させないようなクラスであると尚良いですが、それもやりたくないということなのでしょうか？

また、Accessではできたことが、SQL Serverでできなかったということは、OleDBとSystem.Data.SqlClientの違いもそうですが、それ以上に、排他ロックの考え方がそもそも違います。
そういったことを、一通り考慮されていますか？
その理解がないと、恐らく、この問題を相談しても解決には
導けないと思いますし、他からの回答も得られないと思います。

下記は、私からの提案になりますが、下記のやり方でやることはできないでしょうか？

・ExecuteReaderで取得したものは、すべてDataSetに落とし込みます。
・ExecuteReaderで結果を格納したインスタンス（この例だとCDBkanjya）をDataSetに落とし込み終わった後、Close＆Disposeしてあげます。
・後は、DataSet/DataTableに対してREADする処理を記述し、ループ中でUPDATE文を実行する

こうすればレスポンスの問題もロックの問題も解消でき、エラーも消えると思うのですが、如何でしょうか？

読み取るテーブルと更新のテーブルが同じテーブルで、なおかつ、読み取りのカーソル（OleDbDataReader）をCloseしないうちに、同じテーブルに更新してしまうと、少なくとも今回のコードの場合は、排他ロックの問題でうまく処理できないことがあるんじゃないかと推測します。

＃分離レベルが設定されていないので、あまり細かいことは言えないのですが、ひょっとしたら、上記以外にも分離レベルを設定することで回避できるかもしれませんが、自信はありません。

一度、上記を考慮の上、再度、ロジックを見なおすことをお勧めします。

回答No.2

kero_mio

＞ＭＤＢのJETエンジンにどの様にして接続するのか

System.Data.SqlClientで、JETエンジンを使用するMDBへはアクセスできません。なので、Access MDBに接続したい場合は、OleDb系を使うしかありません。

＞出来たら　私も System.Data.SqlClient　を使用したいのですが、今回のプログラムは、
＞SQL Server で運用したソースをそのまま、接続文字列だけの変更だけで、
＞アクセスのデータベースファイルＭＤＢのJETエンジンにも使用したいのです。

そしたら、Access用は、OleDbで、SQL Server は、System.Data.SqlClientと接続方法を切り替えるようなクラスを作って、そこで処理によって切り替え可能な仕組みを作ればいいだけの話なんですが、それは極力やりたくないということでしょうか？

また、Accessではできたけど、SQL Serverでできなかったということは、OleDBとSystem.Data.SqlClientの違いもそうですが、それ以上に、ロックの考え方がそもそも違います。

その辺りを考慮して、ロジックを組み替えなおしてみては如何でしょうか？

例：
ExecuteReaderで取得したものは、一旦、DataSetに落とし込んだ後、
すぐに、ExecuteReaderで結果を格納したCDBkanjyaをすぐ
Close＆Disposeしてあげます。
同時に、ExecuteReaderで使用したConnectionについても一旦Closeします。

その後、DataSetに対してREADする処理と、ループ中でUPDATE文を実行するようにすれば、レスポンスもロックの問題も解消でき、エラーも消えるんですけどね。

読み取りと更新のテーブルが同じで、なおかつ、読み取りのテーブルのカーソル（OleDbDataReader）をCloseせずに、同じテーブルに更新してしまうと、少なくとも今回のコードの場合は、排他ロックの問題でうまく処理できないことが稀にあるんじゃないかと推測します。

＃分離レベルが設定されていないので、あまり細かいことは言えないのですが、ひょっとしたら、上記以外にも分離レベルを設定することで回避できるかもしれませんが、自信はありません。

一度、上記を考慮の上、再度、見なおすことをお勧めします。

お礼

更なる回答ありがとうございます。
色々ヒントを教えて頂きまして感謝しています。

>Access MDBは、OleDb、SQL Server は、System.Data.SqlClientと接続方法を切り替えるような
>クラスを作り、後は行う処理によって、接続方法の切り替えが自動・任意でできる仕組みを
>作ってみては如何でしょうか？
>その自作したクラスの外部からは、OleDbか、System.Data.SqlClientか意識させないような
>クラスであると尚良いですが、それもやりたくないということなのでしょうか？

出来るだけ複雑にしたくないのが正直なところです。
プログラムは出来るだけシンプルなのが理想（自論）なので。上の手段は最終兵器として検討します。

その後、色々調べたところ、

Oracle と JETエンジン は、SELECT 文は デフォルトでは　共有モードで処理されるのに対して、
SQLServer は排他ロックで処理がされるのが原因と分かりました。

また、SQLServer　は、2005バージョンから、共有モードが追加されたみたいですが、
デフォルトでは、SELECT 文は排他ロックで処理されるみたいです。

SQLServer2005では、READ_COMMITTED_SNAPSHOTのオプションが追加されたみたいで、
テーブル作成時に、READ_COMMITTED_SNAPSHOT　を ON にすることで、
そのテーブルを　SELECT 文処理した場合、デフォルトで共有モードで処理される
みたいです。まだ実機でテストしていませんが、テーブル作成時点でREAD_COMMITTED_SNAPSHOTを
指定すればいいだけならば、ソースレベルでは、JETエンジンとSQLServerとを全く同じソースで
運用できる可能性が出てきました。
現場には、２週間後にいくので、その時点で調べてきます。

>ExecuteReaderで取得したものは、すべてDataSetに落とし込みます。
>ExecuteReaderで結果を格納したインスタンス（この例だとCDBkanjya）をDataSetに落とし込み終わった後、Close＆Disposeしてあげます。
>後は、DataSet/DataTableに対してREADする処理を記述し、ループ中でUPDATE文を実行する

本筋はこの様に処理するのが正しいかもしれません。
ただ今回のシステムはそれほど、排他処理は問題がなく、排他処理をした事によるシステムの負荷、障害とかの
方が問題なので、今のところ検討しませんが、最終的に明確な対策がないと結論になった場合、利用する事にします。

回答No.1

kero_mio

OleDb系(OleDbCommandとか、OleDbConnection)ではなく、SQL Serverなら、System.Data.SqlClient
名前空間にある、SqlConnectionやSqlDataReaderやSqlCommand使えば問題解決するかもしれませんが、自信はありません。

後は、気になったのは、同じテーブルを読んでいる最中、同じテーブルを更新するわけですよね？ロックがかかったために残りの９件の更新がおかしくなった可能性はありますが、こちらも正直、自信なしです。

ただ、気になった点としては、上記のプログラムを見る限りでは、FETCH（Read）させなくても、UPDATE文で更新すれば辻褄合いますよね？結局、下記と同じことだと思いますが、下記じゃダメですか？

dim 肝炎B as long
dim cq as string
dim Db As new System.Data.OleDb.OleDbConnection

Dim CDBkanjyaCw As new System.Data.OleDb.OleDbCommand

Db.ConnectionString = "Provider=SQLOLEDB;Data Source=kokoro00;User ID=sa;Password=kokorocenter;Initial Catalog=kokoro"
Db.Open()
CDBkanjyaCw.Connection = Db

cq = " UPDATE kanjya "
cq += " SET 肝炎B = " & 肝炎B.ToString

CDBkanjyaCw.CommandText = cq
CDBkanjyaCw.ExecuteNonQuery()

CDBkanjyaCw.Dispose()
Db.Close()

FETCH（SELECT）条件が他にあるならばいいのですが、他にSELECTするキーもなく全件取得なSELECTして、その取得した全件をわざわざ1件ずつ患者IDをキーに更新条件つけて1件ずつ更新しなくても、良いのでは？
ここに質問する際、まずい箇所を削除した関係でソースを公開されたのであれば、良いのですが、ちょっと気になったもので・・・

回答になってなくてすいませんが、１つずつ整理して上記をお試しください。

お礼

回答ありがとうございます。

>OleDb系(OleDbCommandとか、OleDbConnection)ではなく、SQL Serverなら、System.Data.SqlClient
>名前空間にある、SqlConnectionやSqlDataReaderやSqlCommand使えば問題解決するかもしれませんが

出来たら　私も System.Data.SqlClient　を使用したいのですが、今回のプログラムは、
SQL Server で運用したソースをそのまま、接続文字列だけの変更だけで、
アクセスのデータベースファイルＭＤＢのJETエンジンにも使用したいのです。

自分なりには、System.Data.SqlClientで接続したソースでは、そのままでは、
ＭＤＢのJETエンジンには使えないと思っています。これが使えるようだといいのですが？
ご存知ですか？。また、使えたとした場合、その場合System.Data.SqlClientで
ＭＤＢのJETエンジンにどの様にして接続するのか教えて下さい。

ＭＤＢのJETエンジンは、System.Data.oledbClient も使用出来るとのことだし、
SQL Server も System.Data.oledbClient が使えるとの事なので、両方のデータベースの
どちらでも可能かなと思って、System.Data.oledbClient を使用しています。

>ただ、気になった点としては、上記のプログラムを見る限りでは、
>FETCH（Read）させなくても、UPDATE文で更新すれば辻褄合いますよね？
>結局、下記と同じことだと思いますが、下記じゃダメですか？

単に、データベースにUPDATE するだけなら、それでよいのですが、

この場合

Do While CDBkanjya.Read

.....
.....
..... <==== ここに、レーコドをよんで、その内容で複雑な
..... <==== 計算式が入ります。この計算はSQL文だけでは
..... <=====解決出来ないくらい複雑なので。

cq = " UPDATE kanjya "
cq += " SET 肝炎B = " & 肝炎B.ToString
cq += " WHERE 患者ID = " & CDBkanjya.Item("患者ID").ToString & " "

CDBkanjyaCw.CommandText = cq
CDBkanjyaCw.ExecuteNonQuery()

Loop

ループの間に複雑な演算が４０行程入ります。UPしたソースには、「・・・・」で
略しています。説明不足ですみませんでした。

再度色々と確認してみようと思います。