DOBON.NET DOBON.NETプログラミング掲示板過去ログ

ローカルなデータベースでの速度向上

環境/言語:[XP　VＳ2002.NET (C#)]
分類:[.NET]

おはよう　ございます。エツです。
いつも参考にさせて頂いてます。
ローカルなデータベース（PC 内で使用）での速度向上について教えて下さい。
（ローカルのためセキュリティは不問でOK）

【現状】
データベース、データセット　データアダプター　を使用したデータベース

Access　97 形式で提供された空のデータベースをAccess　2000 形式の
データベースに変換し（Access　2000でデータベーステーブルが確認、修正できるため）
Select文はSELECT * FROM テーブル名　でInsert、Delete、Updata文はコマンドビルダーで作成しています。

データベースからスキーマを読み込みデータセットにテーブルを追加し
そのテーブルに行を追加し最後にまとめてUpdate でデータベースに追加しています。

【問題点】
時間測定のため空のデータベースに追加し測定しています。結果は追加行が多いため
Update　の部分で８分程度の時間が掛かっています。

【やりたいこと】
今はデータベースの構築段階ですので今の内に早いデータベースを作成すべく
Access　2000 形式のデータベースをMSDE　形式のデータベースに変換して
現状の手順で時間比較をしたいのです。（SQL Server は有料なので対象外です）

Access　2000 形式のデータベースをMSDE　形式のデータベースにキー、
データ型、文字列型の最大文字数などのスキーマを維持して変換する方法がわかりません。
ご教授の程宜しくお願いします。　NET(C#、VB 何れでも可）

【今後の課題】
変換が成功したらデータの読み込み、追加、更新、削除の時間を比較し時間の早い方を
採用します。
ローカルPC内でのパフォーマンスならMSDEよりACCESS2000のMDBの方が
速いような気がしますが。
１．クエリビルダで自動生成せず自前でSQL文を発行する
２．それをパラメータクエリにしてクエリ本体はMDB内に保管する
これで少しは速度UPしそう
■No14370に返信(medakaさんの記事)
medakaさん　すばやい回答有難うございます。

> ローカルPC内でのパフォーマンスならMSDEよりACCESS2000のMDBの方が
> 速いような気がしますが。
そうですか？　実験用の簡単なテーブルをMSDEとACCESS2000のMDBを作り、多くの行を追加して比較してみます。

> １．クエリビルダで自動生成せず自前でSQL文を発行する
テーブルの列数が１００を越えるのもありますので、C#のデザイン画面を使わずにコードでデータアダプター、コネクション、データセットを作成しています。

>自前でSQL文を発行する
行の追加が主なので　Select文　SELECT * FROM テーブル名　を使用してコマンドビルダーでInsert文を自動作成しても自前でSQL文（Insert SQL文）を発行するのと内容は同じと思います。コマンドビルダーでInsert文を作成する時にSelect文を発行しますので仰る通り多少パフォーマンスが悪くなりますが各テーブル毎に追加行を一気にデータベースのテーブルに追加しますので、パフォーマンスの劣化は無視できると思います。

> ２．それをパラメータクエリにしてクエリ本体はMDB内に保管する
クエリ本体はMDB内に保管するとはどのようなことでしょうか？

> これで少しは速度UPしそう
>

テーブル数４５、各テーブルの列数６０～１２０列程度のデータベースですから基本設計を重視しています。
データセット内テーブルに追加データを準備するのは15秒位で速いのですがデータセット内テーブルからデータベースに追加行を転送するのに８分くらい、Update のコード１行部分で８分位頑張っています。
念のためOpen,Close を追加しています。
Cn.Open;
Da,Update(Ds,テーブル名）;　＜――　ここで８分頑張っている（結構長い）
Cn.Close;
としています。
いい方法がありましたらお願いします。
>>ローカルPC内でのパフォーマンスならMSDEよりACCESS2000のMDBの方が
>>速いような気がしますが。
> そうですか？　実験用の簡単なテーブルをMSDEとACCESS2000のMDBを作り、多くの行を追加して比較してみます。

DBの構造にもよりますが、MDB の方が「常に速い」ってことは無さそうです。

> 念のためOpen,Close を追加しています。
> Cn.Open;
> Da,Update(Ds,テーブル名）;　＜――　ここで８分頑張っている（結構長い）
> Cn.Close;
> としています。

DataSet も遅いですからねぇ。
８分をどれくらいに短縮したいですか？

また、MSDE よりも SQL Server 2005 Express Edition の方がパフォーマンス的に期待できるはずですし、DataSet を使うなら .NET Framework 2.0 の方が高速なはずです。
■No14379に返信(渋木宏明(ひどり)さんの記事)
渋木宏明(ひどり)さん回答有難うございます。

>
> DataSet も遅いですからねぇ。
> ８分をどれくらいに短縮したいですか？

無茶かも知れませんが１分位を希望しています。
現状は８分でAccess　2000 形式空のデータベース 928KB から 36360KBに増加していますので 35432KB 転送に８分です。これが速いのか遅いのか私には判断できないのですが、HDDにテキストデータを書き込む速度と比較すると極端に遅いと思います。
データベースを使わないでHDDへ直接テキストデータの書き込み／読み出しでデータセットにテーブル追加（リレーションシップを使うため）も検討しましたが、HDDから必要なデータを抽出する時にSQL文のような柔軟性が無いため後々使えないシステムになると思ったのでデータベースを使うようにしました。

>
> また、MSDE よりも SQL Server 2005 Express Edition の方がパフォーマンス的に期待できるはずですし、DataSet を使うなら .NET Framework 2.0 の方が高速なはずです。
>

VS2002(C#)なので .NET Framework 2.0 は無理ですよね？使えるならすぐに .NET Framework 2.0 にアップデートしますが？

SQL Server 2005 Express Edition は知りませんでした。無償で使えるようですから使用法を今から調べてみます。

最初の質問に戻りますがAccess　2000 形式空のデータベース のスキーマを読み込み、これから SQL Server 2005 Express Edition のテーブルを作成する事は出来るのでしょうか？
宜しくお願いします。
>>８分をどれくらいに短縮したいですか？
>
> 無茶かも知れませんが１分位を希望しています。
> 現状は８分でAccess　2000 形式空のデータベース 928KB から 36360KBに増加していますので 35432KB 転送に８分です。

インデックスやリレーションシップはどうなっているでしょうか？
これらが複雑であればあるほど、mdb は不利です。

> VS2002(C#)なので .NET Framework 2.0 は無理ですよね？

無理です。

.NET Framework 2.0 を使用することで８分がいきなり１分になることは決してありませんが、IDE の使いやすさなど様々な面で有利であるため、出来るだけ Visual Studio 2005 へアップグレードすることをオススメします。

Visual Studio .NET 2002 からは、数万円で Visual Studio 2005 Standard Edition や同 Professional Edition へアップグレードすることが出来ます。

また、予算的に厳しいなら、Visual Studio 2005 Express Edition の利用も検討に値するかもしれません。

> 最初の質問に戻りますがAccess　2000 形式空のデータベース のスキーマを読み込み、これから SQL Server 2005 Express Edition のテーブルを作成する事は出来るのでしょうか？

SQL Server 2005 Express Edition よりも上位のバージョンでは確実にできます。

SQL Server 2005 Express Edition で出来るかどうかは私は分かりません。
インポートのためのツールが提供されていないかもしれません。
■No14385に返信(渋木宏明(ひどり)さんの記事)

渋木宏明(ひどり)さん回答有難うございます。

VS2005は欲しかったので回答を参考に検討します。
インデックスやリレーションシップの詳細は未定です。多分最後に決まると思います。Access　2000 形式からMSDE、SQL Server 2005 Express Edition　への変換プログラム作成にチャレンジしてみます。
そんなことしている間にVS2005日本語版も販売されるでしょうし、インデックスやリレーションシップの詳細も決まるでしょう。

丁寧に教えて頂き、非常に参考になりました。有難うございました。
今後とも宜しくお願いします。



> >>８分をどれくらいに短縮したいですか？
>>
>>無茶かも知れませんが１分位を希望しています。
>>現状は８分でAccess　2000 形式空のデータベース 928KB から 36360KBに増加していますので 35432KB 転送に８分です。
>
> インデックスやリレーションシップはどうなっているでしょうか？
> これらが複雑であればあるほど、mdb は不利です。
>
>>VS2002(C#)なので .NET Framework 2.0 は無理ですよね？
>
> 無理です。
>
> .NET Framework 2.0 を使用することで８分がいきなり１分になることは決してありませんが、IDE の使いやすさなど様々な面で有利であるため、出来るだけ Visual Studio 2005 へアップグレードすることをオススメします。
>
> Visual Studio .NET 2002 からは、数万円で Visual Studio 2005 Standard Edition や同 Professional Edition へアップグレードすることが出来ます。
>
> また、予算的に厳しいなら、Visual Studio 2005 Express Edition の利用も検討に値するかもしれません。
>
>>最初の質問に戻りますがAccess　2000 形式空のデータベース のスキーマを読み込み、これから SQL Server 2005 Express Edition のテーブルを作成する事は出来るのでしょうか？
>
> SQL Server 2005 Express Edition よりも上位のバージョンでは確実にできます。
>
> SQL Server 2005 Express Edition で出来るかどうかは私は分かりません。
> インポートのためのツールが提供されていないかもしれません。
>
解決済み!
とにかく速度第一という事でしたら
MSDEで
DTSかBCPでテンポラリテーブルに入れる
テンポラリテーブルから一括でINSERTする
のが一番速いかも
2006/01/10(Tue) 11:54:40 編集(投稿者)

■No14421に返信(medakaさんの記事)

medakaさんおはようございます。お世話になります。
返信有難うございます。


> とにかく速度第一という事でしたら
> MSDEで
> DTSかBCPでテンポラリテーブルに入れる
> テンポラリテーブルから一括でINSERTする
> のが一番速いかも
>

わずか５行の文章ですが難解です。
DTS を調べましたがこれまた機能が多くて難解です。
DTSで Access　2000形式からMSDE形式のテーブルが出来るような、出来ないような？
（データ転送でなくてMSDE形式のテーブルそのものが作成出来るか否か）

medakaさんの回答の趣旨は次で宜しいのでしょうか？
１．MSDE形式のソーステーブルを　えっちら、おっちら手入力で作成する。
２．テンポラリテーブル（データセットから切り離した単独のMSDE形式のテーブルのことですか？）
３．データセット内MSDE形式テーブルから　テンポラリテーブル　にコピーする。
４．DTS を使用してMSDE形式のソーステーブルにテンポラリテーブルからデータ転送する。（これを一括でINSERTすると表現しているんでしようか？）


お手数をかけまして申し訳ありませんが、
> MSDEで
> DTSかBCPでテンポラリテーブルに入れる
> テンポラリテーブルから一括でINSERTする
この部分をもう少し具体的に教えて下さい。方針が理解できれば一歩前進でもっと高度な質問ができると思います。
宜しくお願いします。
> お手数をかけまして申し訳ありませんが、
>>MSDEで
>>DTSかBCPでテンポラリテーブルに入れる
>>テンポラリテーブルから一括でINSERTする
> この部分をもう少し具体的に教えて下さい。方針が理解できれば一歩前進でもっと高度な質問ができると思います。
> 宜しくお願いします。

http://www.microsoft.com/japan/sql/msde/howtobuy/msdeuse.mspx
MSDEでDTSの作成できるのか調べた所、新規作成はできないようでした。
すいません。
変換だけならDTS使わずにリンクテーブルを作成し一括してINSERTしては
どうでしょうか？

>Update のコード１行部分で８分位頑張っています。
データセットの行数と更新対象の行数にもよりますが
列数が多いと自動生成のSQL文ではwhere条件が列数分できますので
不利になると思います。
timestamp列を追加して自前でSQL文を発行したほうが速いと思います。
■No14424に返信(medakaさんの記事)
medakaさん　返信有難うございます。

> http://www.microsoft.com/japan/sql/msde/howtobuy/msdeuse.mspx
> MSDEでDTSの作成できるのか調べた所、新規作成はできないようでした。
> すいません。

DTS ですが　これはSQL Server,MSDE を使いこなすツールだと思います。
DTS の作成は出来そうですが、これをどう使うかいまいちイメージが掴めません。
Updateの代わりに使って速度が向上するのでしょうか？


> 変換だけならDTS使わずにリンクテーブルを作成し一括してINSERTしては
> どうでしょうか？

この場合の変換とはデータの変換だと思いますが、私の場合Access2000形式のテーブルが多数提供されているだけで、中身（データ）は空です。同等のMSDE形式のテーブル（データは空）が作成できればSQL Server用のデータアダプターを使用してデータをInsertできます。（速度はあまり変わらないみたいですが）
ただ中身が空の今の内に将来を考えてMSDEやSQL Server 2005 Express Editionのデータベースソースに変えておきたい気持ちです。


> >Update のコード１行部分で８分位頑張っています。
> データセットの行数と更新対象の行数にもよりますが
> 列数が多いと自動生成のSQL文ではwhere条件が列数分できますので
> 不利になると思います。
> timestamp列を追加して自前でSQL文を発行したほうが速いと思います。
>

where条件は今のところキー列だけなので全列にwhere条件があるとやはり不利ですね。
この部分は検討します。

DTSを使ってUpdateの速度を上げる方法をもう少し教えて下さい。（リンクテーブルとは何でしょうか？）
宜しくお願いします。
こんにちは。

私も以前同じような事で悩みましたが結果として、記事14370でmedakaさんが
言われているように、データアダプタでUpdateを実行するよりもパラメータ
クエリでInsertをしたほうが速く処理ができました。

作りとしては、更新したいデータテーブルを引数にしたメソッドを用意し
①データテーブルの名称及びカラム名からパラメータクエリ用のInsert文を作成
②Insertしたいデータテーブルの件数だけパラメータクエリに値をセットしSQLを実行
です。

また、あまり速度差は出ないと思いますが、①のSQLをデータテーブルから動的に作成
するのではなく、テーブル毎にINSERT文を定数等で用意する事でミリ秒レベルで速くなると思います。
（比較はしてません）

一応作ったコードをのせておきます。

※コネクションやトランザクションも呼び出し元から引数で渡しています。

サンプルコード ************************************************************************************************************
private void ExecuteUpdate(DataTable adtTargetTbl, int aiStartIdx, SqlConnection aSqlConn, SqlTransaction aSqlTran)
{
StringBuilder sbInsert = new StringBuilder();
SqlCommand CmdInsert;

//①
// ********* SQL作成 *********
sbInsert.Append("INSERT INTO ");
sbInsert.Append(adtTargetTbl.TableName);
sbInsert.Append(" (");

for (int i = 0; i < adtTargetTbl.Columns.Count; i ++)
{
sbInsert.Append(adtTargetTbl.Columns[i].ColumnName);

if (i < adtTargetTbl.Columns.Count - 1)
{
sbInsert.Append(", ");
}
}

sbInsert.Append(") VALUES(");

for (int i = 0; i < adtTargetTbl.Columns.Count; i ++)
{
sbInsert.Append("@");
sbInsert.Append(i.ToString());

if (i < adtTargetTbl.Columns.Count - 1)
{
sbInsert.Append(", ");
}
}

sbInsert.Append(")");

CmdInsert = new SqlCommand(sbInsert.ToString(), aSqlConn);
CmdInsert.Transaction = aSqlTran;
// ***************************

// ②
// パラメータセット
for (int i = aiStartIdx; i < adtTargetTbl.Rows.Count; i ++)
{
CmdInsert.Parameters.Clear();

for (int j = 0; j < adtTargetTbl.Columns.Count; j ++)
{
CmdInsert.Parameters.Add("@" + j.ToString(), adtTargetTbl.Columns[j].DataType);
CmdInsert.Parameters["@" + j.ToString()].Value = adtTargetTbl.Rows[i][adtTargetTbl.Columns[j].ColumnName];
}

CmdInsert.ExecuteNonQuery();
}
}
■No14427に返信(TSUKAさんの記事)

TSUKAさん 貴重な資料有難うございます。
CmdInsert.ExecuteNonQuery();とUpdate()は接続型、非接続型の違いで
速度には大差ないと思っていました。
しかし少しでも速くするためにはCmdInsert.ExecuteNonQuery();が有利なのですね。
このコードを拝見しますと、SQL　Server、MSDE　用ですね。
やはり　SQL　Server、MSDE　が将来的にいいのでしょうか？
解説と実際のコードを書いて頂いてますので、非常に良く解ります。
medakaさん、渋木宏明(ひどり) さん、TSUKAさん　大変お世話になりました。今後とも宜しくお願いします。
解決済み!
こんにちは

こちらの開発環境を書いていませんでした。
DBは使用できるSQLや将来的な事、開発時の利便性を考えてMSDEを使用していました。

SQLについては市販のSQLハンドブックを見ればAccessとSQL Server(MSDEも）の
違いがわかると思います。

将来的な事では、現在はクライアントのみで動作しているがMSDEを使用する事で
接続文字列を変更するだけでSQLサーバへ切替が可能だったためです。
もちろんユーザ認証の仕組みや排他制御の処理を設計時から考慮していましたが。。。

開発時では、テストデータ等をSQLサーバを使用する事で簡単に共有できたり
SQLのチューニングをする場合もSQLサーバのツールを使用する事で比較的簡単に
行えとても便利でした。
ただ、Accessと比較して配布する際にMSDEは非常に苦労しましたけど・・・。

何を選択するかは開発するシステムによって違うので、これじゃなければダメと
言うのはないと思います。

以上です。
解決済み!
DOBON.NET | プログラミング道 | プログラミング掲示板