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

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

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

Re[2]: Insert文が低速 高速方法


(過去ログ 130 を表示中)

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

■77178 / inTopicNo.1)  Insert文が低速 高速方法
  
□投稿者/ 二時間 (1回)-(2015/09/19(Sat) 10:54:14)

分類:[.NET 全般] 

こんにちは。c#でSQL(INSERT)文を作って、実行をかけています。1万件ほどのデータをInsertするには問題ありませんが、100万件ほどのデータをInsertするとOutOfMemoryExceptionが出てしまいます。
そして、Insertがかなり低速で1万件ほどのデータを挿入するのに10秒程度かかってしまいます。

質問1.OutOfMemoryExceptionにならないようにするには何か良い方法はあるでしょうか?
質問2.Insertを高速にする方法はあるでしょうか?下記のサイトを参考に作っています。→http://uniunix.com/blog/?p=154

※A Fast CSV Readerというモジュールを使用しています。→http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader

どうぞ、よろしくお願い致します。

using (CsvReader csv = new CsvReader(new StreamReader(selectedFile, Encoding.GetEncoding("Shift_JIS")), hasHeader))
{
using (SQLiteConnection cn = new SQLiteConnection(コネクションストリング))
{
cn.Open();
using (SQLiteTransaction trans = cn.BeginTransaction())
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = "PRAGMA journal_mode = MEMORY"; // ジャーナルファイルをメモリ上に持つ
cmd.ExecuteNonQuery();

while (csv.ReadNextRecord()) //1行読み込み
{
SQLiteCommand cmd = cn.CreateCommand();
StringBuilder CSVVALUE = new StringBuilder(); //VALUESの値を保存する
StringBuilder insertSQL_And_VALUES = new StringBuilder(); //INSERT文とVALUESを結合したものを保存

for (int i = 0; i < csv.FieldCount; i++)
{
CSVVALUE.Append("'" + csv[i] + "',"); //VALUESで使用するデータを連結する
}
CSVVALUE = CSVVALUE.Remove(CSVVALUE.Length - 1, 1) ; //末尾のカンマを削除
insertSQL_And_VALUES.Append("INSERT INTO T " + " VALUES(" + CSVVALUE + ")"); //Insert SQL構築
cmd.CommandText = insertSQL_And_VALUES.ToString();
cmd.ExecuteNonQuery(); //Insert トランザクションコミット
}
trans.Commit(); //最後の最後にコミット
}
}
引用返信 編集キー/
■77181 / inTopicNo.2)  Re[1]: Insert文が低速 高速方法
□投稿者/ なちゃ (81回)-(2015/09/19(Sat) 11:50:56)
> cmd.CommandText = "PRAGMA journal_mode = MEMORY"; // ジャーナルファイルをメモリ上に持つ
かつ、最後にまとめてコミットでは無理がある気がします。
ちょっとSQLiteの詳細知らないのではっきりとは言えませんが。
引用返信 編集キー/
■77182 / inTopicNo.3)  Re[2]: Insert文が低速 高速方法
□投稿者/ なちゃ (82回)-(2015/09/19(Sat) 12:16:42)
No77181 (なちゃ さん) に返信
>>cmd.CommandText = "PRAGMA journal_mode = MEMORY"; // ジャーナルファイルをメモリ上に持つ
> かつ、最後にまとめてコミットでは無理がある気がします。
> ちょっとSQLiteの詳細知らないのではっきりとは言えませんが。

とりあえずトランザクションを、ある程度のレコード数ごとにコミットするようにしてみてください。
メモリオーバーはそれで回避できる感じがします。

高速化については、パラメータ使うとか文字列処理の仕方とか多少改善の余地はありそうです。
コマンドの作成がそれなりに重いなら、パラメータクエリにするのは効くかも知れません。
文字列処理はまあ誤差範囲の可能性が高いですが。
引用返信 編集キー/
■77183 / inTopicNo.4)  Re[3]: Insert文が低速 高速方法
□投稿者/ なちゃ (83回)-(2015/09/19(Sat) 12:19:24)
SQLiteは素人ですので、特有の高速化ノウハウや、SQLiteの特性は全く知りません。
なので外してるかもしれませんが、常識的な感覚で推測できる範囲でのアドバイスです。
そういう前提で参考にしてください。
引用返信 編集キー/
■77184 / inTopicNo.5)  Re[4]: Insert文が低速 高速方法
□投稿者/ Hongliang (347回)-(2015/09/19(Sat) 12:44:12)
テーブル構造やインポート前のテーブル内容、インポートするデータにも依りますけど、インデックスや外部キー制約があるなら一旦それを無効化するとか(SQLiteだと削除する必要があるんでしたっけ?)
引用返信 編集キー/
■77185 / inTopicNo.6)  Re[5]: Insert文が低速 高速方法
□投稿者/ 二時間 (2回)-(2015/09/19(Sat) 16:45:16)
なちゃ様

http://www.atmarkit.co.jp/ait/articles/0307/12/news002_2.html
を参考にSqlParameterCollection.Add メソッドを使用しましたが、結果はほとんどかわりませんでした。
こまめにコミットする方法を取り入れたいと思います。ご回答ありがとうございます。

Hongliang様

テーブルのフィールド数は50程度です。1フィールドあたりのデータも5文字程度しか入っていません。
インデックスははってあるので、無効化してどうなるか試してみたいと思います。
またデータベースも auto_vacuumを行なっているので、その辺が影響しているかもしれません。
ご回答ありがとうございます。
引用返信 編集キー/
■77186 / inTopicNo.7)  Re[6]: Insert文が低速 高速方法
□投稿者/ 二時間 (3回)-(2015/09/19(Sat) 16:53:58)
こまめにcommitしようと思い、コードを一部変更しました。内容的には、1000件毎にコミットするといった使用に変更しました。
それを行うと、下記のようなエラーメッセージが表示されてしまいます。

ArgumentNullExceptionはハンドルされませんでした。
値を Null にすることはできません。
パラメーター名:No connection associated with this transaction

何が違っているのでしょうか?


long counter = 1; //←-----------------------追加

using (CsvReader csv = new CsvReader(new StreamReader(selectedFile, Encoding.GetEncoding("Shift_JIS")), hasHeader))
{
using (SQLiteConnection cn = new SQLiteConnection(コネクションストリング))
{
cn.Open();
using (SQLiteTransaction trans = cn.BeginTransaction())
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = "PRAGMA journal_mode = MEMORY"; // ジャーナルファイルをメモリ上に持つ
cmd.ExecuteNonQuery();

while (csv.ReadNextRecord()) //1行読み込み
{
SQLiteCommand cmd = cn.CreateCommand();
StringBuilder CSVVALUE = new StringBuilder(); //VALUESの値を保存する
StringBuilder insertSQL_And_VALUES = new StringBuilder(); //INSERT文とVALUESを結合したものを保存

for (int i = 0; i < csv.FieldCount; i++)
{
CSVVALUE.Append("'" + csv[i] + "',"); //VALUESで使用するデータを連結する
}
CSVVALUE = CSVVALUE.Remove(CSVVALUE.Length - 1, 1) ; //末尾のカンマを削除
insertSQL_And_VALUES.Append("INSERT INTO T " + " VALUES(" + CSVVALUE + ")"); //Insert SQL構築
cmd.CommandText = insertSQL_And_VALUES.ToString();
cmd.ExecuteNonQuery(); //Insert トランザクションコミット

//↓-----------------------追加
if(counter % 1000 == 0)
{
trans.Commit();
}
counter++;
//↑-----------------------追加

}
//trans.Commit(); //最後の最後にコミット
}
}
引用返信 編集キー/
■77187 / inTopicNo.8)  Re[7]: Insert文が低速 高速方法
□投稿者/ しま (102回)-(2015/09/19(Sat) 19:37:31)
No77186 (二時間 さん) に返信

ソースコードを貼るときは図表モードで投稿して下さい。インデントが分からないのでソースを追うのが苦痛です。

> それを行うと、下記のようなエラーメッセージが表示されてしまいます。
>
> ArgumentNullExceptionはハンドルされませんでした。
> 値を Null にすることはできません。
> パラメーター名:No connection associated with this transaction
>
> 何が違っているのでしょうか?
どの行でそのエラーが発生しているのか示してください。読み手に判断させるのは誤解を招く基です

> using (SQLiteTransaction trans = cn.BeginTransaction())
この後ろにブロック "{...}" が無いのが致命的です。

引用返信 編集キー/
■77188 / inTopicNo.9)  Re[7]: Insert文が低速 高速方法
□投稿者/ なちゃ (85回)-(2015/09/19(Sat) 19:40:29)
トランザクションはコミットしたら新たに開始する必要があります。
あと、パラメータクエリですが、クエリはちゃんと再利用するようにしてますかね?
毎回作り直してたら意味ないので念のためです。
引用返信 編集キー/
■77208 / inTopicNo.10)  Re[1]: Insert文が低速 高速方法
□投稿者/ 甕星 (1回)-(2015/09/26(Sat) 06:22:46)
No77178 (二時間 さん) に返信

> そして、Insertがかなり低速で1万件ほどのデータを挿入するのに10秒程度かかってしまいます。

何に比較してですか?
1レコード50フィールド、1フィールド当たり5文字程度ってことは、長さの情報も含めると10バイト程度ですよね。
1レコード500バイト、1万レコードで5MB。4KBセクタと仮定すると1250セクタです。
平均シークタイム8msと仮定すると、10,000ms。約10秒になります。

妥当な速度と思いますけど?


引用返信 編集キー/
■77215 / inTopicNo.11)  Re[2]: Insert文が低速 高速方法
□投稿者/ 二時間 (4回)-(2015/09/27(Sun) 15:32:58)
なちゃさん ご回答ありがとうございます。トランザクションの事をしっかりわかっていませんでした・・・
甕星さん ご回答ありがとうございます。感覚的に遅いと感じただけでした・・・

解決済み
引用返信 編集キー/


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

このトピックに書きこむ

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

管理者用

- Child Tree -