[初めてのAccess+postgre]遅過ぎ対策２分→２秒へスピードアップ方法

Jet Database Engineプログラムしか作れない私（情けない）

今更で、キャリアアップのためpostgreSQLを勉強中

毎日使ってる自作「メーカー発注用Accessプログラム」

こいつのpostgreSQL版が作りたい。
完成すれば社内の全PCから発注データ参照/発注業務ができる。

きっと簡単だろう！

odbcでリンク張って
vbaは、jet用を少しイジるレベルでは？

２週間くらいで「odbcリンク版」完成！
しかし・・・・

が～ん、使い物にならない！
update文１行が約２分！？
遅・す・ぎ・る！

---

私は、２行の「DoCmd.RunSQL update文」を多用します。
全データのFlagを初期化(=False)、その後、該当データのみを(=True)

DoCmd.RunSQL "UPDATE public_tblorder SET  Fprint=False"
DoCmd.RunSQL "UPDATE public_tblorder SET  Fprint=True where ORDERNO='" & TargetMaker & "'"

数年分貯まった発注明細、約29,000行
このデータへupdateを実行し処理速度を比較

"UPDATE public_tblorder SET Fprint=False"

内部jet tableなら、1秒以下

物理的に離れてるpostgreSQLサーバへ接続


odbcリンク接続経由で、postgre TABLEへupdate実行

えぇー、嘘だろ？

１分４６秒！！
超々、ちょう遅い
絶望的に遅い！

jetの100倍以上遅い！

postgresODBC ver.10が絶望的に遅いので、ver.16へアップグレード！

テストし直すと
あれ、１分５５秒にスピードダウン！？
前回テストからレコードが数百増えたのが原因？

とにかく、私のvbaプログラムは使い物になりません！

---

そもそも、postgreサーバ上で実行すると何秒かかるの？

postgreSQL shellから実行で、2.5秒

pgAdminから実行で、3.3秒

処理時間、ほぼ２分のクライアントPC上のAccessを、

数秒にスピードアップする方法
google様、教えて

google様のお告げ１：パススルークエリーをテスト

パススルークエリで、約5秒

もっと早い方法が無いか、さらに検索
お告げ２：ADO(ActiveX Data Objects)からSQL実行

ADO接続＋con.Execute(sql)でselect文実行スクリプト発見！
ならばupdate文も実行できるのでは？

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を書き直します。

アマチュアとプロの間、高いハードルが有りますね

jet用だと２行

DoCmd.RunSQL "UPDATE public_tblorder SET  Fprint=False"
DoCmd.RunSQL "UPDATE public_tblorder SET  Fprint=True where ORDERNO='" & TargetMaker & "'"

これをpostgre用に書き換えるとこんな感じかな

まずFunctionプロシージャ

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

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)

スクリプトが、２行から２０行超に！
これで実行速度２分が２秒にスピードアップしました。

やっぱADO.Execute(mySQL)が、
一番早い！