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

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

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

postgresql

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

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

きっと簡単だろう!

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秒以下

これ以降は、知らない世界!
物理的に離れてるpostgreSQLサーバへ接続
postgresODBC設定
posgreODBCリンク作成
odbcリンク接続経由で、postgre TABLEへupdate実行

えぇー、嘘だろ?

1分46秒!!
超々、ちょう遅い
絶望的に遅い!

jetの100倍以上遅い!

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

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

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


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

postgreSQL shellから実行で、2.5秒

pgAdminから実行で、3.3秒

pgAdminでupdateを実行すると3.3秒

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

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

google様のお告げ1:パススルークエリーをテスト
passThroughQuery

パススルークエリで、約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を書き直します。

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

追記:jet用スクリプトをpostgre用に書き換えました(2分が2秒にスピードアップ)
jet用だと2行

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)が、
一番早い!

コメントを残す

メールアドレスが公開されることはありません。

CAPTCHA