実行速度を早くするには？
お世話になります。いつも勉強させていただいております。
いま、データベースへの書込みプログラムを作成して、テストしているのですが、どうも実行速度が遅いのです。しかし、原因がわかりません。（これが最速なのかもわかりません）
データベースへの書き込みは一方的です。
削除、編集はなく、新規の行を挿入し続けます。（数千万件になる見込み）
改善前は500件/分だったのですが、こちらで以前教えていただいたことや、コードの見直しなどを行い、現在は一万件処理するのにおよそ170〜180秒かかります。
ただ、データベースへの接続などで遅いのか、コードが悪いのかもわからない状態です。データベースへ接続しないで処理すると非常に高速なため、データベースが疑われているところです。
説明不足だとは思いますがよろしくお願いします。
環境：WinXPSP2 VB6SP5 ACCESS2003 ADO MDB （ネットワークアクセス、複数ユーザアクセスではありません）
動作：ある条件を満たした情報を、新規の行に挿入する。
テーブルに、インデックス項目は付いていますか?
インデックスが無い方が、データの追加は高速化しますので、
重複データが無い事がわかっている場合は、データの追加中は、
主キーも含めたすべてのインデックスを外しておくのも、一つの手です。
それと、速度を求めるのであれば、ADO ではなく、DAO を選択した方が
良い結果を得られます。DAO は JET API に直接アクセスするため、
OLE DB層を介する ADO に比べて、処理速度が向上します。
例えば、10 万件のランダムなデータを、
"CREATE TABLE [TABLE12] ([id] INTEGER, [data] INTEGER)"
のような単純なテーブルへ追加した場合、下記のコードでは、
DAO版は 2 秒程度で追加できましたが、ADO版については、
その 10 倍の時間を要しました。
# id列を主キーにした場合は、それぞれ 40% 増しの時間。
'========== ADO 版 ==========
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseServer
RS.Open "TABLE1", Cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
Set fID = RS.Fields("id")
Set fData = RS.Fields("data")
'実験用のダミーデータを 10万件追加
For ID = 1 To 100000
RS.AddNew
fID.Value = ID
fData.Value = Int(Rnd() * 12345)
RS.Update
Next
RS.Close
Set fID = Nothing
Set fData = Nothing
Set RS = Nothing
'Cn.Close
'========== DAO 版 ==========
Set RS = Db.OpenRecordset("TABLE1", dbOpenTable)
Set fID = RS.Fields("id")
Set fData = RS.Fields("data")
'実験用のダミーデータを 10万件追加
For ID = 1 To 100000
RS.AddNew
fID.Value = ID
fData.Value = Int(Rnd() * 12345)
RS.Update
Next
RS.Close
Set RS = Nothing
Set fID = Nothing
Set fData = Nothing
'Cn.Close
試しては無いのですが、ADOでAppend-Only Rowsetプロパティを使えば、
追加専用のレコードセットが作れるそうです。
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Properties("Append-Only Rowset") = True
rs.Open "SELECT * FROM TABLE1"
魔界の仮面弁士さん、即効レスありがとうございます。
また、以前もお世話になりました。ありがとうござます。
早速、頂いたご意見で試してみたいと思います。
初耳です！！インデックスはつけることで高速化が実現するものだと思っていました！
ただ、今回は重複するものがすくなからずあるので主キーや、インデックスはありません。（念のため確認してみます）
ネットで検索して、ADOとDAOを比較しているサイトなども合ったのですが、どちらを使うべきかわからず、とりあえずADOを選択ました・・・。
10倍も違うのですか・・・。
早速試して、報告します。
ADOのみですが、実行にかかっている時間を以下に記します。
何かの参考になれば。。。
10,000*n->SEC（10,000件ごとの処理時間）
1->179.97
2->174.39
3->179.80
4->177.39
5->172.24
6->187.26
7->175.27
8->200.63
9->189.64
10->218.05
11->252.39
12->264.83
13->258.75
14->258.09
15->281.72
16->219.58
17->241.38
18->403.56
19->871.41
20->798.07
21->360.63
22->168.95
23->167.44
24->168.11
25->169.31
26->1202.30
.
.
.
≫ LESIAさん
> 試しては無いのですが、ADOでAppend-Only Rowsetプロパティを使えば、
> 追加専用のレコードセットが作れるそうです。
そのコードに、Connection の指定を加えて試してみたところ、
当方環境では、186〜203秒という時間で処理されました。
元の ADO コードと比較すると、5.0〜8.8 倍ですね。(^^;
幾ら追加専用にするとは言っても、テーブルセットモードから
静的カーソルモードに変更しているため、速度がかなり低下しているようです。
少し手を加えて、下記のように変更してみました。
Set RS = New ADODB.Recordset
RS.CursorLocation = adUseServer
Set RS.ActiveConnection = Cn
RS.Properties("Append-Only Rowset").Value = True
RS.Open "TABLE1", , adOpenKeyset, adLockOptimistic, adCmdTable
この場合、元の ADO コードに比べ、30% 程度の時間短縮となりました。
ちなみに、DAO 版の方を追加専用に変更してみたところ、12% ほど高速化しました。
元々含まれているデータ件数にもよりますけどね。
Set RS = Db.OpenRecordset("TABLE1", dbOpenTable, dbAppendOnly)
≫BIOPRINさん
> インデックスはつけることで高速化が実現するものだと思っていました！
それは検索の場合ですね。
データの更新/登録/削除時には、データに対する「目次」たるインデックスの
評価と更新が発生するため、その分、処理速度が幾らか低下します。
ただ、『インデックスを付ける事による、登録時の実行速度低下』よりも、
『インデックスを外す事による、検索時の実行速度低下』の方が、
デメリットが大きくなるため、通常はインデックスをつけるべきです。
なお、インデックスの状態を変更できないような場合には、
データの追加は「空の仮テーブル」に登録するようにして、あとで
本テーブルの方に INSERT INTO で流し込む…という手もあります。
> 10倍も違うのですか・・・。
えぇと、その辺の値は、あまりアテにしないでください。m(_ _)m
DAO の結果も ADO の結果も、当然、いくらかのばらつきが出るわけですが、
時間がかかる処理だった事もあり、2〜3回程度しか試せておらず、その中での
結果を比較したら、たまたま 10倍近い値になってしまったというだけです。
その後さらに 5 回ほど追試してみましたが、平均を取らずに比較した場合、
3.2 〜 10.2倍、という範囲でした。(という事は、4〜5倍前後かな?)
# いずれにしても、DAO の方が高速に処理できるだろうとは思います。
すいません。。。
質問のレベルが低くて申し訳ありません。
昨日から悪戦苦闘しているのですが、DAOオブジェクトの宣言というのでしょうか。それがうまくいきません。。。
'========== DAO 版 ==========
' 参照設定： Microsoft DAO 3.6 Object Library
Dim db As DAO.Database
Dim RS As DAO.Recordset
Dim fT1 As DAO.Field
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)
'Set dbs = ws.OpenDatabase("t.mdb")
Set RS = ws.OpenDatabase("t.mdb")
Set RS = db.OpenRecordset("TEL", dbOpenTable)
Set fT1 = RS.Fields("T1")
RS.AddNew
fT1.Value = sT0
RS.Update
RS.Close
Set RS = Nothing
Set fTEL = Nothing
低レベルですいません。。。。
よろしくお願いします。
それと私は普段perlな人間です。
perlでは my $hensu1; と宣言(VBでは dim hensu as variantかな)するとレキシカルスコープといい、ルーチン終了後に変数は開放されます(変数の局所化)。
なので、上のコードはループ処理内に書いていますが、これは正しいことですか？VBではもっと別の場所（ループ呼び出し前のサブルーチン内でデータベースに接続しておくとか）でdimなどしておいて、そのハンドルというかそれに向かってsetするような使い方でいいのでしょうか。
追記：
過去ログや以下を参考にしたのですが、混ざってしまっているようで、混乱しています。
http://www.accessclub.jp/dao/01.html
http://homepage2.nifty.com/inform/vbdb/dao_paramquery.htm
http://www.accessclub.jp/bbs6/0005/das996.html
以下の部分を入れたり消したり、元にして考えたりしています
Dim ws As DAO.Workspace
Set ws = DBEngine.Workspaces(0)
'Set dbs = ws.OpenDatabase("t.mdb")
Set RS = ws.OpenDatabase("t.mdb")
とりあえず↓は何？
> Set RS = ws.OpenDatabase("t.mdb")
アルファベットだと同じに見えますか？じゃあ
カタカナで書きましょうか？
データベースを開く命令でレコードセットを
作ろうとしてるの？？？
>perlでは〜ルーチン終了後に変数は開放されます
perl は知りませんが、
[VB6.0]ではプロシージャ(関数)レベルです。ヘルプで
『変数の適用範囲』をキーワード検索してみてください。
同じタイトルのページに
>Dim で宣言されたローカル変数は、そのプロシージャが
>実行されている間だけ存在します。
と記述されています。
>なので、上のコードはループ処理内に書いていますが、これは正しいことですか？
正しくないです。
…というかループの外で変数を宣言して開放されるかどうかテスト
すれば分かることでは？…保持されるなら同じオブジェクトを
何回も何回も作成(呼び出し)するより
>そのハンドルというかそれに向かってsetするような使い方でいいのでしょうか。
のほうが良さそう…というのは簡単に予想が付くのでは？
で、これを『参照変数に参照アドレスを格納する』というような表現をします。
特にデータベースとの接続を確立する…とかいうのは時間が掛かる
場合が多いです。複数の処理を実行する場合は、１つの接続を
開いたままで処理を実行し、最後に接続を閉じます。
よくわからないけど、過去ログとかみていると、それが必要らしいのでまねして入れてみた感じです。
データベースをワークスペースに展開しているのではないかな？というところですが、おおきくまちがっていますか？？
すいません。教えてください。
特攻隊長まるるう さん、すいません。
そうですね。試してみればわかることでした。
また、調査不足でした。すいません。
>特にデータベースとの接続を確立する…とかいうのは時間が掛かる
>場合が多いです。複数の処理を実行する場合は、１つの接続を
>開いたままで処理を実行し、最後に接続を閉じます。
今回はシングルユーザなのであまり気にすることもない状態なのですが、非常に長い時間、データベースを操作する状態にしっぱなしというのは、良くないのかな、と思ったので。それはDBMSが勝手にやるので気にする必要はないのでしょうか。
# 開放→解放かな。
>> 特にデータベースとの接続を確立する…とかいうのは時間が掛かる
>> 場合が多いです。
ADO や ODBC あるいは OLEDB レベルでは、コネクションプーリングという機構が
あるため、それを利用する事で、(2回目以降の)接続時間の問題を解決できます。
# COM+/MTS という選択肢もありますね。
> 非常に長い時間、データベースを操作する状態にしっぱなしというのは、良くないのかな、と思ったので。
接続に関しては、2つの側面があります。
1つは、まるるうさんが書かれているように、DBへの接続の作成のためにコストがかかるという事。
もう1つは、BIOPRINさんが書かれたように、その維持にコストがかかるという事です。
そしてどちらを重要視するかは、そのアプリケーションごとに考えなければいけません。
一般的なアプリケーションでは、ユーザーが画面を閲覧・操作する時間が長く、
DBを読み書きしている時間は、アプリ全体の総起動時間から見れば僅かなので、
サーバーリソースに負荷を与えないためにも、最近の開発手法としては、
「接続は毎回切断し、必要な時だけ再接続する」方法が多くなってきています。
再接続にかかる時間も、先述のプーリングという機構によって解決されますので。
ですが今回のアプリケーションのように、データを一括処理するような場合には
それは当てはまりません。まして、ループ処理内で切断/再接続というのは無駄です。
今回のようなアプリケーションでは、ループの処理前に接続して、処理終了までは
その接続を保持し続けるというスタイルを用いるべきです。
再接続にかかる時間はゼロではありませんので、もしも、データを 1件登録するたびに、
切断と再接続を繰り返すとしたら、再接続の時間が僅か100ミリ秒だったとしても
長すぎる時間といえるでしょう。何しろ、1万件のデータを挿入するともなれば、
トータルで 15分以上の時間になるのですからね。
> Dim ws As DAO.Workspace
> Set ws = DBEngine.Workspaces(0)
> 'Set dbs = ws.OpenDatabase("t.mdb")
> Set RS = ws.OpenDatabase("t.mdb")
DAO でデータベースを開く場合には、
Dim DB As DAO.Database
Set DB = ws.OpenDatabase(……)
ですね。サンプルによっては、Workspace の取得を省略している場合もありますが、
その場合には 既定のワークスペースが用いられる事になります。
> 今回はシングルユーザなので
その場合は、データベースを排他モードで開く事を検討してみてください。
ページロックやテーブルロックにかかるコストを削る事ができます。
># 開放→解放かな。
あうっ ...( ＿)＿
弁さんが書いてくれたので接続に関しては省略。
>よくわからないけど、過去ログとかみていると、それが必要らしいのでまねして入れてみた感じです。
掲示板の書き込みはその場で書いてるから間違ったコードもたくさんあります。
保守もできません。サンプルコードを管理人さんが公開しているようなサイトの
情報を優先して参考にしてください。
http://homepage2.nifty.com/inform/vbdb/dao_paramquery.htm
> Set RS = ws.OpenDatabase("t.mdb")
のような使い方はしてませんよね？多くの情報を仕入れるのは有効な
ことですが、改造するのはコードの意味を完全に理解してからです。
なんとなくでは”動かないのが当然です”…そのデバッグ作業を回答者
にやらせるのは非常識だと思いません？
サンプルコードを実行するときは１つのサイトの情報のみ、できるだけ
サンプルコードそのままの環境を再現して実行してください。…当然のことですが。
>一般的なアプリケーションでは、ユーザーが画面を閲覧・操作する時間が長く、
>DBを読み書きしている時間は、アプリ全体の総起動時間から見れば僅かなので、
>サーバーリソースに負荷を与えないためにも、最近の開発手法としては、
>「接続は毎回切断し、必要な時だけ再接続する」方法が多くなってきています。
例えば[VB.NET]では非接続型のオブジェクトの導入でデータベースに接続
するオブジェクトの内部に自分で接続して自分で閉じる機能があったり…
なにも難しいことは言って無いんです。パン屋に行ってあんぱんとメロンぱん
買うのに、あんぱん買ってレジ行って店の外に出る、メロンぱん買ってレジ行って
店の外に出るのか？ってことです。…しませんよね？
ただし、使わないのに繋ぎっぱなしは良くないです。パンを買わないのにパン屋
に居座るのは良くありません。でも、パンを買うなら店に入って目的の事をする間は
店から出なくてもいいんじゃない？って事です。
>そしてどちらを重要視するかは、そのアプリケーションごとに考えなければいけません。
プログラム中でどこがパン屋になるのか？考えなければいけません。
起動時に接続を開いて、アプリケーション終了時まで
開きっぱなしという設計もあり得ます。…特殊ですが。
まず、現状の報告をさせていただきます。
10,000件ごとの実行速度は以下の通りです。
1->188.42
2->174.25
今のコードは以下の通りです(抜粋)
Public DB As DAO.Database
Public RS As DAO.Recordset
Public fT1 As DAO.Field
Public ws As DAO.Workspace
Option Explicit
Private Sub cmdSTART_Click()
Set ws = DBEngine.Workspaces(0)
Set DB = ws.OpenDatabase("t.mdb")
Set RS = DB.OpenRecordset("T", dbOpenTable, dbAppendOnly)
Set fT1 = RS.Fields("T")
'ループ
For i = 0 To 9999
txtIN = Format(i, "0000")
DoEvents
GetInfo (OUT & txtIN)
Next i
RS.Close
Set RS = Nothing
Set fT1 = Nothing
MsgBox ("完了しました")
End Sub
Private Sub GetInfo(str As String)
sT0 = vbNullString
'いろいろな処理
If sT0 = vbNullString Then Exit Sub
RS.AddNew
fT1.Value = sT0
RS.Update
End Sub
う〜ん・・・。
速度アップがほとんど見られない状態です。
上のコードで直したほうが良いところなどあったら教えてください。
魔界の仮面弁士 さん、ありがとうございます。
いろいろな比較の結果など頂き、とても参考になります。
>『インデックスを外す事による、検索時の実行速度低下』
今回はまず、データをデータベースに入れたいのでとりあえずインデックスは
ナシで行ったほうがいいですね。念のため確認しましたが今使っているMDBには
インデックスはありませんでした。
>(という事は、4〜5倍前後かな?)
少しでも全然OKです。
もともと3年８ヶ月かかると試算が出たのですがそのときは目玉が飛び出るかと思いました。。。
なんとか過去ログや、以前質問させていただいたアドバイスを元に高速化した結果、
約２ケ月まで計算終了までの期間を短くすることができました。
それでも、15時間くらい走らせたところでどんどん遅くなってきてしまったので、
今回ご相談させていただいたわけです。
>ループ処理内で切断/再接続というのは無駄です。
ループ外で呼び出しをするように変更しました。しかし、Rs.Closeを間違った
呼び出し方をしたのでエラーが出てしまい、しばらく苦戦していました。
>1万件のデータを挿入するともなれば、トータルで 15分以上の時間になるのですからね。
そうですね。今回は多くのデータを処理しなければならないので小さな時間でも短縮できるように改善していきたいです。
>データベースを排他モードで開く事を検討してみてください。
はい。とりあえず動作するようになったので、今回はこのアドバイスを実行できるように
勉強させていただいています。できたら報告いたします。
特攻隊長まるるうさんありがとうございます。
>管理人さんが公開しているようなサイトの情報を優先して参考にしてください。
はい。参考にさせていただきます。
>改造するのはコードの意味を完全に理解してからです。
>なんとなくでは”動かないのが当然です”…そのデバッグ作業を回答者
>にやらせるのは非常識だと思いません？
そうですか・・・。私はとりあえず動作して、そこから一つ一つを理解して、
全体を把握していくように勉強させていただいていました。
それが常識なのかもしれませんが、勉強方法についても勉強させていただきました。
ありがとうございます。
>プログラム中でどこがパン屋になるのか？考えなければいけません。
はい。今回はループ前に接続し、ループ後に解除しています。
ループ内ではデータを吟味し、必要ならデータを挿入し、そうでないなら戻っています。
| ツイート |  |