C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[5]: １００万件データのInsertでタイムアウト

(過去ログ 45 を表示中)

[トピック内 18 記事 (1 - 18 表示)]　 << 0 >>

■23487 / inTopicNo.1)

　１００万件データのInsertでタイムアウト

▼■

□投稿者/ nana* (18回)-(2008/08/16(Sat) 23:39:10)

分類:[.NET 全般]　

よろしくお願い致します。
　（VB.NET 2005
SQLServer 2005 std）

以下のようなタイムアウトエラーが発生し、原因が分からず対応に困っています。
どなたかご教授お願い致します。

CLRを使用したバッチ処理を実行しようとしています。
処理の一つで１００万件データのINSERTをしようとして「タイムアウトに達しました」と
エラーになります。（errno 5）
INSERT文は「INSERT INTO (項目名) from (Aテーブル) Select CASE field1 WHEN '' THEN '0' ELSE field1 END field1 from (Bテーブル) 」
という形式です。
※全項目にcaseで判定処理をいれています。
INSERTしたいAテーブル,Bテーブルともに項目数は３０個くらいです。
sqlcommandを使ってクエリを実行しようとしているのですが
sqlcommandのTimepoutを０にしてみたのですが、INISERT文を実行する処理でタイムアウトします。
処理が走って３～４分でタイムアウトエラーが発生しています。
ロジックを確認していると、INSERT文を発行するの前のジョブで接続を切断していない箇所がいくつかみられたので
試しにINSERT文の前でsqlconnection.ClearAllコマンドを発行してみたのですが
やはりエラーになります。

何故タイムアウトになるのかわからず苦慮しております。
様々なネット上の文献を確認したのですが　なかなか納得できない状態です。
Aテーブルにはキーが４つ設定してあり、外さない方法を考えたいと思っています。
大変困っています、どなたか似たような経験をされた方が居たらヒントをいただきたく。
よろしくお願いします。

引用返信 編集キー/

■23488 / inTopicNo.2)

　Re[1]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ 片桐 (103回)-(2008/08/17(Sun) 00:04:36)

まず、INSERT文の解析はしてみました？
実行プランでのステップが多ければ多いほどそれだけ時間がかかるわけですから
まずは自分の実行したいSQLの動きを把握することから開始ですね。
その上で、SELECT句のチューニングを行うことが肝要かと

動作しているCPUの数やDBファイルのドライブ配置によっても速度は変化しますから
実行状況の画面で、CXPACKETやI/OのWAITの発生頻度も把握しておくことも必要です。

100万件のINSERTを一気にやるのはなかなかテクニックが必要です。
できるならSQLBULKCOPYメソッドの使用や、BULKINSERTの使用を考えてみてください。
ネット上の情報でいうなら、SQLperformance系の海外サイトが参考になります。

あと、SQLCLRでは非同期クエリ系メソッドは使えませんので、
後はMTAで分割INSERT……それもあまりお勧めしませんが（汗）

引用返信 編集キー/

■23489 / inTopicNo.3)

　Re[2]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ nana* (20回)-(2008/08/17(Sun) 00:17:34)

■No23488 (片桐さん) に返信
> まず、INSERT文の解析はしてみました？
> 実行プランでのステップが多ければ多いほどそれだけ時間がかかるわけですから
> まずは自分の実行したいSQLの動きを把握することから開始ですね。
> その上で、SELECT句のチューニングを行うことが肝要かと
>
> 動作しているCPUの数やDBファイルのドライブ配置によっても速度は変化しますから
> 実行状況の画面で、CXPACKETやI/OのWAITの発生頻度も把握しておくことも必要です。
>
> 100万件のINSERTを一気にやるのはなかなかテクニックが必要です。
> できるならSQLBULKCOPYメソッドの使用や、BULKINSERTの使用を考えてみてください。
> ネット上の情報でいうなら、SQLperformance系の海外サイトが参考になります。
>
> あと、SQLCLRでは非同期クエリ系メソッドは使えませんので、
> 後はMTAで分割INSERT……それもあまりお勧めしませんが（汗）

お返事ありがとうございます。
すごくせっぱつまっている状況なのでありがたいです！
CLRを使用しているのでクエリの解析が出来なかったのですが
（出来る方法はあるのでしょうか？？私は知らないです・・・）
INSERT文だけの解析をしてみました。
SELECT後、INSERT前のSORT処理がもっとも負荷がかかっているようです。
これはISNERTする前にSELECTで抽出したデータを並び変えている？
これ以上どうしようも無いかと判断しました。

パフォーマンスモニタ等監視してみて、メモリはそんな気になる値ではなかったのですが
Average DISK Quelength が処理中に頻繁に１００になっていました。
テーブルにInsertする前にspaceを'0'や固定の値に置き換えたいという要件があって
BULKではなく　Insertselectを使用しています。
今のところInsertselectで出来るところまでやってみたいという方向です。

引用返信 編集キー/

■23490 / inTopicNo.4)

　Re[3]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ オショウ (36回)-(2008/08/17(Sun) 00:45:30)

> CLRを使用しているのでクエリの解析が出来なかったのですが
> （出来る方法はあるのでしょうか？？私は知らないです・・・）

　　SQL Serverプロファイラがありますので、それで解析して下さい。
　　私も御世話になっているツールです。

> INSERT文だけの解析をしてみました。
> SELECT後、INSERT前のSORT処理がもっとも負荷がかかっているようです。
> これはISNERTする前にSELECTで抽出したデータを並び変えている？
> これ以上どうしようも無いかと判断しました。

　　どうしようも無い？？？

　　Order by で指定したフィールドが、テーブル側では、キーに
　　なっていないとか・・・

　　仮にキーになっていた場合、SQL プロファイラで何かチューニング
　　できるかも・・・です。

●　テーブル構造も解らないし、フィールド数も解らないし・・・
　　100万件のレコードが一体どの程度のデータ量かも解らないので、
　　ただ単にタイムアウトするから・・・では、基本的にどうしよう
　　もないです。物理的にＨＤＤ上のデータ領域として読み書きする
　　速度が最高速ですので、それをキー等でレコード的に配置するに
　　はもっと時間がかかる・・・ＨＤＤのキャッシュ量や、データベ
　　ース自体のキャシュ量にも影響しますので・・・

　　搭載メモリ量やSQL ServerでのＣＰＵ負荷の割り当てやら・・・
　　チューニングするところは、沢山あるかと！

参考までに・・・

以上。

引用返信 編集キー/

■23492 / inTopicNo.5)

　Re[4]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ nana* (21回)-(2008/08/17(Sun) 02:31:56)

2008/08/17(Sun) 02:43:47 編集(投稿者)
2008/08/17(Sun) 02:43:36 編集(投稿者)
2008/08/17(Sun) 02:40:58 編集(投稿者)

■No23490 (オショウさん) に返信
>>CLRを使用しているのでクエリの解析が出来なかったのですが
>>（出来る方法はあるのでしょうか？？私は知らないです・・・）
>
> 　　SQL Serverプロファイラがありますので、それで解析して下さい。
> 　　私も御世話になっているツールです。

オショウさん
お返事ありがとうございます。

ちょっと自分の記事を見直しして
状況が伝わりにくい書き方だったなと反省しました。
CLR機能によるストアドプロシージャを作成して、バッチ処理を作成しています。
ストアドを実行する際にSQLサーバに対して発行しているSQL文だけならキャッチ出来ました。
（ストアド内に記述しているIF文やSELECT文まで解析してくれたらいいなと思ったのですが・・　）

> 　　どうしようも無い？？？
>
> 　　Order by で指定したフィールドが、テーブル側では、キーに
> 　　なっていないとか・・・
>
> 　　仮にキーになっていた場合、SQL プロファイラで何かチューニング
> 　　できるかも・・・です。

> ●　テーブル構造も解らないし、フィールド数も解らないし・・・
> 　　100万件のレコードが一体どの程度のデータ量かも解らないので、
> 　　ただ単にタイムアウトするから・・・では、基本的にどうしよう
> 　　もないです。物理的にＨＤＤ上のデータ領域として読み書きする
> 　　速度が最高速ですので、それをキー等でレコード的に配置するに
> 　　はもっと時間がかかる・・・ＨＤＤのキャッシュ量や、データベ
> 　　ース自体のキャシュ量にも影響しますので・・・
>
> 　　搭載メモリ量やSQL ServerでのＣＰＵ負荷の割り当てやら・・・
> 　　チューニングするところは、沢山あるかと！
>

お返事ありがとうございます。
大変ありがたいです。
私が記事に書いていなかったので大変申し訳無かったのですが、
INSERTストアドプロシージャは以下の処理を行っています。
1 ストアドプロシージャの引数としてテーブル名を二つ受け取り、
システムテーブルからAテーブルとBテーブルのフィールド名・構造を調べます。
2 システムテーブルから調べたフィールド名を使用して動的にInsert into Aテーブル (Select **, ** from Bテーブル）
と言うSQL文を生成します。
　（ちなみにorder by句・where句・IN句は使用していません。）

現在開発中のこのバッチ処理は２年後にリプレースが決まっており、お客様にとって
重要なシステムではありません。
私のやりたいことはこうです。
・なるべく工数をかけずに処理を実現したい
　（サーバの再構築とかはしたくない、テーブルの構造も変えたくない）
・何故SqlCommandのTimeout値の設定がきかないか？
　毎回２０００秒前後でタイムアウトしているので　どこかに２０００という設定がされているか？
　Timeout値を０にすると　クエリの実行が終了するまで待機するのではないか？？
・タイムアウトする原因が分かった上で、なるべく工数の少ない方法で回避策をとりたい
　もちろんリプレースシステムについてはパフォーマンスチューニングはきっちりするつもりです

テーブル構造を変えるは最終手段のつもりです・・・
Disk Queue Lengthの値が高いのでDISK要領へのアクセスがボトルネック？
メモリの増設が一番効果的ということですね。

引用返信 編集キー/

■23493 / inTopicNo.6)

　Re[2]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ nana* (22回)-(2008/08/17(Sun) 02:36:56)

引用返信 編集キー/

■23494 / inTopicNo.7)

　Re[5]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ れい (731回)-(2008/08/17(Sun) 05:56:25)

■No23492 (nana* さん) に返信
> ・何故SqlCommandのTimeout値の設定がきかないか？
> 　毎回２０００秒前後でタイムアウトしているので　どこかに２０００という設定がされているか？
> 　Timeout値を０にすると　クエリの実行が終了するまで待機するのではないか？？

200ですよね？
2000って33分です。

CommandTimeoutに0を設定したら
CLRの範囲ではタイムアウトしないはずです。

> 処理の一つで１００万件データのINSERTをしようとして「タイムアウトに達しました」と
> エラーになります。（errno 5）

これでは例外の詳細がわかりません。
errno 5というのも不明です。
SQL Serverのエラーに5は無いと思います。

詳細なメッセージと、コードがあると皆に伝わると思います。

引用返信 編集キー/

■23496 / inTopicNo.8)

　Re[3]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ やじゅ (540回)-(2008/08/17(Sun) 11:44:12)

>■No23493 (nana* さん) に返信
>　Aテーブルにはキーが４つ設定してあり、外さない方法を考えたいと思っています。

更新する場合、インデックスも更新しなければならないので余分に時間がかかります。
一時的にインデックスを外すのもいいかも知れません。

引用返信 編集キー/

■23585 / inTopicNo.9)

　Re[4]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ 片桐 (104回)-(2008/08/18(Mon) 23:04:09)

まず、commandtimeout=0　で実行すると、終了するまで処理は待機しますので、いつか処理が終われます
CLRで作っても、ストアドには違いないので、その当たりは強固に作られているから大丈夫です。
実際、私も430万件レコードのINSERTをCLRで実行したことがありますから、そこは確約できます

SQLの解析方法はいくつかありますが、プロファイラを使用しているなら、
取得するイベントを選択して流せば色々な情報が得られます。
その中にはShowPLANもあって、実行するSQLの解析結果も表示できます

切羽詰っているときこそ、こういったツールを使って正確な情報を把握していかなくてあはならないです、がんばってください

MTAとはMultiThreadApartment、つまり、複数のスレッドを実行して並行処理を行う方法です。
これを使い、インデックスをわざとはずして、MTAで主キーで分割したSELECTレコードを並行にINSERTしてインデックスを戻す、という手を使いました。INSERTのSQLは中には一切計算式をいれず、すべて事前にスキーマバインドビューを使って単純なSELECTINSERTにまで落とし込んで処理しています。コミットタイミングをずらしてCXPACKET発生をギリギリまで抑えてスピードをはかり、最終的には430万件のレコードチェック＆集計＆INSERT処理を10分切るところまでチューンアップしましたがかなり色々と姑息な手が必要でした。

ところどころの情報は私のブログにちょこちょこと書いています。
が、それらの大元の情報はほとんど海外サイトです。

引用返信 編集キー/

■23586 / inTopicNo.10)

　Re[5]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ 黒龍 (120回)-(2008/08/18(Mon) 23:15:23)

コマンド以外にもトランザクションのタイムアウトもありますね。

■No23585 (片桐さん) に返信
> まず、commandtimeout=0　で実行すると、終了するまで処理は待機しますので、いつか処理が終われます
> CLRで作っても、ストアドには違いないので、その当たりは強固に作られているから大丈夫です。
> 実際、私も430万件レコードのINSERTをCLRで実行したことがありますから、そこは確約できます
>
> SQLの解析方法はいくつかありますが、プロファイラを使用しているなら、
> 取得するイベントを選択して流せば色々な情報が得られます。
> その中にはShowPLANもあって、実行するSQLの解析結果も表示できます
>
> 切羽詰っているときこそ、こういったツールを使って正確な情報を把握していかなくてあはならないです、がんばってください
>
> MTAとはMultiThreadApartment、つまり、複数のスレッドを実行して並行処理を行う方法です。
> これを使い、インデックスをわざとはずして、MTAで主キーで分割したSELECTレコードを並行にINSERTしてインデックスを戻す、という手を使いました。INSERTのSQLは中には一切計算式をいれず、すべて事前にスキーマバインドビューを使って単純なSELECTINSERTにまで落とし込んで処理しています。コミットタイミングをずらしてCXPACKET発生をギリギリまで抑えてスピードをはかり、最終的には430万件のレコードチェック＆集計＆INSERT処理を10分切るところまでチューンアップしましたがかなり色々と姑息な手が必要でした。
>
> ところどころの情報は私のブログにちょこちょこと書いています。
> が、それらの大元の情報はほとんど海外サイトです。
>

引用返信 編集キー/

■23587 / inTopicNo.11)

　Re[6]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ れい (736回)-(2008/08/18(Mon) 23:24:11)

■No23586 (黒龍さん) に返信
> コマンド以外にもトランザクションのタイムアウトもありますね。

トランザクションがタイムアウトしたとき、
SQLCommandが投げる例外では区別できないのでしょうか？
メッセージとかって同じなのかしら？

引用返信 編集キー/

■23626 / inTopicNo.12)

　Re[7]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ 黒龍 (122回)-(2008/08/19(Tue) 15:21:54)

例外が別だったはずです。
トランザクションならトランザクション、コマンドならコマンドとエラーメッセージにあった気がします。
「タイムアウトに達しました。～がほげほげ…」みたいな感じで。

■No23587 (れいさん) に返信
> ■No23586 (黒龍さん) に返信
>>コマンド以外にもトランザクションのタイムアウトもありますね。
>
> トランザクションがタイムアウトしたとき、
> SQLCommandが投げる例外では区別できないのでしょうか？
> メッセージとかって同じなのかしら？

引用返信 編集キー/

■23631 / inTopicNo.13)

　Re[6]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ nana* (23回)-(2008/08/19(Tue) 16:39:55)

2008/08/19(Tue) 16:42:26 編集(投稿者)

■No23494 (れいさん) に返信
> ■No23492 (nana* さん) に返信
>>・何故SqlCommandのTimeout値の設定がきかないか？
>>　毎回２０００秒前後でタイムアウトしているので　どこかに２０００という設定がされているか？
>>　Timeout値を０にすると　クエリの実行が終了するまで待機するのではないか？？
>
> 200ですよね？
> 2000って33分です。
>
> CommandTimeoutに0を設定したら
> CLRの範囲ではタイムアウトしないはずです。

確かに２００秒です。
ＭＳＤＮライブラリにも、処理が終了するまで待機します
とあります。
接続プール数にはまだ余裕があるので、はたして、一体何がタイムアウトしているのか・・・。

>>処理の一つで１００万件データのINSERTをしようとして「タイムアウトに達しました」と
>>エラーになります。（errno 5）
>
> これでは例外の詳細がわかりません。
> errno 5というのも不明です。
> SQL Serverのエラーに5は無いと思います。
>
> 詳細なメッセージと、コードがあると皆に伝わると思います。

お返事遅くなってすみません。
確かに、エラー情報が少ないですね、すみませんでした
エラーは
「タイムアウトに達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。」
というエラーです。
エラーコードは取っていないです。

引用返信 編集キー/

■23632 / inTopicNo.14)

　Re[7]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ れい (737回)-(2008/08/19(Tue) 16:50:33)

2008/08/19(Tue) 16:51:43 編集(投稿者)

■No23631 (nana* さん) に返信
> エラーは
> 「タイムアウトに達しました。操作が完了する前にタイムアウト期間が過ぎたか、またはサーバーが応答していません。」
> というエラーです。

ほう。
でも

■No23626 (黒龍さん) に返信
> 例外が別だったはずです。
> トランザクションならトランザクション、コマンドならコマンドとエラーメッセージにあった気がします。
> 「タイムアウトに達しました。～がほげほげ…」みたいな感じで。

ということなので…
トランザクションのタイムアウトではないということですよね。

一方、

■No23494 (れいさん) に返信
> CommandTimeoutに0を設定したら
> CLRの範囲ではタイムアウトしないはずです。

なので…

変ですよね。矛盾です。
何か、どこかがおかしいです。

nana* さん言うことが間違っているか、
私の言ってることがおかしいか、
黒龍さんの言うことが間違っているか。

最初から一個一個検証するべきでしょうね。
報告待っています。

黒龍さんがいっていたトランザクションのタイムアウトあたりは怪しいんじゃないでしょうか？

引用返信 編集キー/

■23636 / inTopicNo.15)

　Re[8]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ 黒龍 (123回)-(2008/08/19(Tue) 17:52:41)

あーエラーメッセージ云々はしっかり覚えてないです。ちゃんと環境作ってから書くべきでしたね。すみませんでした。
タイムアウトに絡む操作はコネクション、トランザクション、コマンドがあってそれぞれで発生します。
コマンド中にもデッドロックがあるのですがこれはCloseしてから例外が飛ぶので別扱いでいいと思います。
コネクションはまぁいいとしてコマンドだけ無限にしてもトランザクションで引っかかるなどはよくあることなので両方チェックが望ましいです。
今回シンプルにコマンドだけに見えるのでSQLサーバ側でクエリガバナ等の処理時間制限をかけていませんか？（これもメッセージが変わったはずですが試せる環境がないです。すみません）

はやいとこ解決するといいですね。

引用返信 編集キー/

■23689 / inTopicNo.16)

　Re[4]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ nana* (24回)-(2008/08/20(Wed) 13:12:41)

■No23496 (やじゅさん) に返信
> >■No23493 (nana* さん) に返信
> >　Aテーブルにはキーが４つ設定してあり、外さない方法を考えたいと思っています。
>
> 更新する場合、インデックスも更新しなければならないので余分に時間がかかります。
> 一時的にインデックスを外すのもいいかも知れません。

お返事ありがとうございます。
そうですね、検討してみます！

引用返信 編集キー/

■23716 / inTopicNo.17)

　Re[5]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ nana* (25回)-(2008/08/20(Wed) 15:47:05)

■No23585 (片桐さん) に返信
> まず、commandtimeout=0　で実行すると、終了するまで処理は待機しますので、いつか処理が終われます
> CLRで作っても、ストアドには違いないので、その当たりは強固に作られているから大丈夫です。
> 実際、私も430万件レコードのINSERTをCLRで実行したことがありますから、そこは確約できます

お返事ありがとうございます。
４３０万件ですが、実績のある方がいると心強いです。
といことは、１００万件でタイムアウトするのは私の方法が間違っているけど
出来る方法はあるということですね。

> SQLの解析方法はいくつかありますが、プロファイラを使用しているなら、
> 取得するイベントを選択して流せば色々な情報が得られます。
> その中にはShowPLANもあって、実行するSQLの解析結果も表示できます
>
> 切羽詰っているときこそ、こういったツールを使って正確な情報を把握していかなくてあはならないです、がんばってください

ありがとうございます。
現在、タイムアウトエラーが収束してしまって　再現待ち状態です。
次に再現した際に是非使用させてもらおうと思っています。

> MTAとはMultiThreadApartment、つまり、複数のスレッドを実行して並行処理を行う方法です。
> これを使い、インデックスをわざとはずして、MTAで主キーで分割したSELECTレコードを並行にINSERTしてインデックスを戻す、という手を使いました。INSERTのSQLは中には一切計算式をいれず、すべて事前にスキーマバインドビューを使って単純なSELECTINSERTにまで落とし込んで処理しています。コミットタイミングをずらしてCXPACKET発生をギリギリまで抑えてスピードをはかり、最終的には430万件のレコードチェック＆集計＆INSERT処理を10分切るところまでチューンアップしましたがかなり色々と姑息な手が必要でした。
>
> ところどころの情報は私のブログにちょこちょこと書いています。
> が、それらの大元の情報はほとんど海外サイトです。

そうなんですね、そんな方法が・・・
恐れいりますが、差し支えなければ片桐さんのブログを教えていｔだけませんか？
今後の参考にさせていただければ、と思います。

引用返信 編集キー/

■23900 / inTopicNo.18)

　Re[5]: １００万件データのInsertでタイムアウト

▲▼■

□投稿者/ おてやわらか (7回)-(2008/08/23(Sat) 12:58:31)

2008/08/23(Sat) 14:48:27 編集(投稿者)

解決済みになっていますが、→見間違えだった？？
新しい開発用ＰＣを作っていて、
面白そうなリンクがありました。
役に立つかどうかわかりませんが

BinaryIntellect.net : Bulk Copy Tool
http://binaryintellect.net/articles/504f073f-b53b-48eb-90e1-4a887f471ab7.aspx

Ｇｏｏｇｌｅ検索ワード：Bulk Copy Tool
http://www.google.co.jp/search?hl=ja&q=Bulk+Copy+Tool&btnG=Google+%E6%A4%9C%E7%B4%A2&lr=

引用返信 編集キー/

トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

- Child Tree -