データベースからの抽出を早くする方法

環境/言語:[xp VC#2005、SQLServer2005]
分類:[.NET]

こんにちは　エツです。　いつもお世話になります。

データベースの構築をしますので構築方針についてアドバイスをお願いします。
xp VC#2005 Express Edition、SQLServer2005 Express Editionの環境です。

お聞きしたいのは、データベースからの抽出速度を速める為のデータベース及びテーブル、その他関連部分の仕様をどのようにするかです。
テーブルは１０個位あり、抽出時にテーブル間の参照もあります。テーブルの項目数は多いもので１４０項目、少ないもので１０項目です。テーブルの項目は変更できないです。各テーブルには２０万行以上のデータがあります。抽出されるデータは少なく１回の抽出で１００行程度です。

抽出したデータは単独のTable、DataSetのTable、DataView、List のいずれかの速度が早いのに格納したいです。表示は抽出したデータを加工して、DataGrid　に表示します。

抽出内容は各テーブルの項目の　年月、日時、大分類、中分類、小分類、製作者　などの特定の項目で抽出しｌます。
（例）
１．　２００６年１月１日〜２００６年１月３１日の全てのデータ
２．　製作者　A　で２００６年１月１０日〜２００６年１月２０日　範囲　で大分類　ガラスのデータ抽出　などです。

現状は
１．年月、日時、大分類、中分類、小分類、製作者　を　PRIMARY KEY にする。
２．データベースの更新は　殆どが挿入データの為　Insert Cmd.ExecuteNonQuery(); を行い　PRIMARY KEY 違反があれば Update Cmd.ExecuteNonQuery(); 　を行います。
３．読み出しはSqlDataReader dr = Cmd.ExecuteReader();　で行い　結果をデータセットのTable　に格納しています。SQL文はStringBuilderで毎回作成しています。パラメータは使用していません。ローカルなので、セキュリティは問題にしていません。

データベースの更新は１週間に１回なので、気になりませんが、抽出は煩雑に行う為速度が大変気になります。データベースからの抽出が遅く、その後のデータ加工、表示の速度はOKです。データベース全体をパソコンのメモリに取り込むのは、メモリ不足で無理です。

少しでも抽出を早くする方法があれば試してみたいのでアドバイスをお願いします。

結局DataTableに入れるのに、なんで間にSqlDataReader使っているのですか？
結果的に使用するメモリが一緒なのに、1行ずつフェッチするような動作をとる必要はないかと。
SqlDataAdapterのSelectCommandに、生成したSQL文で作るSqlCommandをセットして、
Fillメソッドを使えばいいのでは？

それとも、取得したデータをDataTableに入れるまでに何かデータ加工処理があるんですか？
（その場合でも、たいていの場合、SQLのSELECT時点で加工まで済ませることができると思います。よほどでない限り。）

2006/09/16(Sat) 23:38:54 編集(投稿者)

■No17585に返信(こど。さんの記事)
こど。さん回答有難うございます。

> 結局DataTableに入れるのに、なんで間にSqlDataReader使っているのですか？

大意はありません。
データの挿入、更新に　Cmd.ExecuteNonQuery()　を使う方が　Fillメソッドを使うより早いと思っていましたので、同様にSqlDataReaderの方がFillメソッドより早いと思って使用しました。

読み出しにFillメソッドを試してみます。
なおデータベースへのデータの挿入、更新に関しても、挿入に失敗したデータを更新していましたが、予め挿入データとデータベースのデータを比較し、新規データは挿入、更新データは更新と区別してみます。(SqlDataAdapterのFillメソッドを試してみます）

ありがとうございます。

■No17584に返信(エツさんの記事)

> 抽出内容は各テーブルの項目の　年月、日時、大分類、中分類、小分類、製作者　などの特定の項目で抽出しｌます。
> （例）
> １．　２００６年１月１日〜２００６年１月３１日の全てのデータ
> ２．　製作者　A　で２００６年１月１０日〜２００６年１月２０日　範囲　で大分類　ガラスのデータ抽出　などです。
>
> 現状は
> １．年月、日時、大分類、中分類、小分類、製作者　を　PRIMARY KEY にする。
> ２．データベースの更新は　殆どが挿入データの為　Insert

テストした訳ではないので、速くなると言う保障は無いです。

あくまで速度を求める場合、僕ならどうするか？、と言う視点で書きます。

PrimaryKey は、別途連番などを設けて、上記項目でよく検索に使いそうな項目にインデックスを設定します。

必要なデータだけを SqlDataReader から直接 DataGrid に入れる。

以下も目を通されると良いと思います。

「ADO.NET パフォーマンスの向上」
http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt12.asp

2006/09/18(Mon) 16:21:57 編集(投稿者)

■No17589に返信( Ｒ・田中一郎さんの記事)
>PrimaryKey は、別途連番などを設けて、上記項目でよく検索に使いそうな項目にインデックスを設定します。

■No17590に返信(trapemiyaさんの記事)

> 以下も目を通されると良いと思います。
>
> 「ADO.NET パフォーマンスの向上」
> http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt12.asp


Ｒ・田中一郎さん、trapemiyaさん　回答ありがとうございます。
大変参考になりました。

インデックスは前々から気になっていたのですが、どんなのか判らなく、どのように使用するかもわからなかったのです。
trapemiyaさんから紹介して頂いたサイトの関連にインデックスの概要があり、重要である事を強調していました。
http://www.microsoft.com/japan/msdn/enterprise/pag/scalenetchapt14.asp#scalenetchapt14_topic8
インデックスを易しく解説したサイトを御存知でしたら教えて下さい。
#次のサイトが見つかりました。
http://msdn2.microsoft.com/ja-JP/library/ms190197.aspx

trapemiyaさんから紹介して頂いたサイトは大変参考になります。
データベース構築ができそうな気がしてきました。

■No17592に返信(エツさんの記事)

皆様　回答有難うございました。

現状ではインデックスを設定して無かったので、PrimaryKey　で勝手に付けられていました。
テストとして、インデックスを付け直すと、大幅に抽出速度が向上しました。（恥ずかしい限りです）

皆様のアドバイスのお陰で設計できそうです。（最初から作り直します）

またなにかあれば御指導の程お願いします。