Jet Database Engineプログラムしか作れない私(情けない)
今更で、キャリアアップのためpostgreSQLを勉強中
こいつのpostgreSQL版が作りたい。
完成すれば社内の全PCから発注データ参照/発注業務ができる。
きっと簡単だろう!
odbcでリンク張って
vbaは、jet用を少しイジるレベルでは?
2週間くらいで「odbcリンク版」完成!
しかし・・・・
が~ん、使い物にならない!
update文1行が約2分!?
遅・す・ぎ・る!
私は、2行の「DoCmd.RunSQL update文」を多用します。
全データのFlagを初期化(=False)、その後、該当データのみを(=True)
1 2 | DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=False" DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=True where ORDERNO='" & TargetMaker & "'" |
数年分貯まった発注明細、約29,000行
このデータへupdateを実行し処理速度を比較
1 | "UPDATE public_tblorder SET Fprint=False" |
内部jet tableなら、1秒以下


odbcリンク接続経由で、postgre TABLEへupdate実行
えぇー、嘘だろ?
1分46秒!!
超々、ちょう遅い
絶望的に遅い!
jetの100倍以上遅い!
postgresODBC ver.10が絶望的に遅いので、ver.16へアップグレード!
テストし直すと
あれ、1分55秒にスピードダウン!?
前回テストからレコードが数百増えたのが原因?
とにかく、私のvbaプログラムは使い物になりません!
postgreSQL shellから実行で、2.5秒
pgAdminから実行で、3.3秒
処理時間、ほぼ2分のクライアントPC上のAccessを、
数秒にスピードアップする方法
google様、教えて
パススルークエリで、約5秒
もっと早い方法が無いか、さらに検索
お告げ2:ADO(ActiveX Data Objects)からSQL実行
ADO接続+con.Execute(sql)でselect文実行スクリプト発見!
ならばupdate文も実行できるのでは?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | Dim con As New ADODB.Connection con.ConnectionString = "Provider=MSDASQL;" & _ " DSN=toPosgre;" & _ " DATABASE=test;" & _ " SERVER=888.888.888.888;" & _ " PORT=8888;" & _ " UID=taro;" & _ " PWD=urashima" & _ " SSLmode=disable;" con.Open Dim sql As String sql = "UPDATE tblorder SET Fprint=False" Dim rst As ADODB.Recordset Set rst = con.Execute(sql) Set rst = Nothing con.Close |
update文も実行できた!
ADOで、約2秒
すごい、Shellと同じ処理速度!
このADO接続で、vbaを書き直します。
アマチュアとプロの間、高いハードルが有りますね
1 2 | DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=False" DoCmd.RunSQL "UPDATE public_tblorder SET Fprint=True where ORDERNO='" & TargetMaker & "'" |
これをpostgre用に書き換えるとこんな感じかな
まずFunctionプロシージャ
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | Public Function SQLexec(mySQL As String ) Dim con As New ADODB.Connection con.ConnectionString = "Provider=MSDASQL;" & _ " DSN=toPostgre;" & _ " DATABASE=test;" & _ " SERVER=888.888.888.888;" & _ " PORT=8888;" & _ " UID=urasima;" & _ " PWD=taro;" & _ " SSLmode=disable;" con.Open Dim rst As ADODB.Recordset Set rst = con.Execute(mySQL) Set rst = Nothing con.Close Set con = Nothing End Function |
呼び出すvba
1 2 3 4 5 6 7 | Dim mySQL As String mySQL = "UPDATE tblorder SET Fprint=False" Call SQLexec(mySQL) mySQL = "UPDATE tblorder SET Fprint=True where ORDERNO='" & TargetMaker & "'" Call SQLexec(mySQL) |
スクリプトが、2行から20行超に!
これで実行速度2分が2秒にスピードアップしました。
やっぱADO.Execute(mySQL)が、
一番早い!