Q&A
Access実行環境のOSは64ビットでしょうか？ OSもAccessも32ビット同士ならトラブルは少なそうなのですが。ODBC経由で参照しているPostgreSQLドライバーのバージョンはわかりますか？
Accessで、レコード数が約120万件あるPostgreSQLのテーブルから
リンクテーブルを経由して別のaccess上のテーブルへ全件Insertする処理があります。
そのInsertが全件正常に行われる場合と、
中途半端に40万件程度Insertされる場合があり、困っています。
処理中のAccessのメモリを監視すると、
正常時：700MBまでメモリ使用量が上がる
異常時：400MBまで使用量が上がったところで一旦30MB前後まで落ち、
再び300MBまで使用量が上がる（エラーは発生しません）
正常時、異常時共にOS全体のメモリ使用量は50%程度です。
極力プログラムは修正しない形で対応したいと考えています。
原因または対策を知っていましたら教えてください。
###該当のソースコード
VBA
1DoCmd.RunSQL "INSERT INTO 格納先テーブル名 IN 格納先ファイルパス SELECT * FROM リンクテーブル名
###動作環境
サーバ
PostgreSQL 9.5
WindowsServer2012 R2
メモリ4GB
クライアント
Windows10
メモリ6GB
Access 2013(32ビット版)
回答4件
0
自己解決
根本的な原因は分からずじまいですが、PostgreSQLのODBCの設定を以下の通り変更することで
改善の傾向が見られました。
オプション(高度な設定)→「データソース」ボタン→「Declare～Fetchを使用する」にチェックを付ける
ただし、この設定をしても端末によっては改善しないため、
最終的には件数分割でINSERTするようプログラムを変更しかないかなと思っています。
回答をいただいた皆様ありがとうございました。
投稿2018/02/15 07:14
総合スコア6
ふと思いましたが、クエリーのプロパティでODBCタイムアウト値が設定されてはいないですか？
0
安定してないというのは難しいですね…他のやり方では
・CurrentDb.Executeで同じSQLを実行する
（Docmd.RunSQLでもそうですがdbFailOnError使うといいかと）
・select into(テーブル作成クエリ)で実行してみる
くらいですかね。根本的な解決にはならないかもしれませんが一応。
投稿2018/01/12 02:34
総合スコア3828
回答ありがとうございます。
・CurrentDb.Executeで同じSQLを実行する
・select into(テーブル作成クエリ)で実行してみる
いずれも試してみましたが、現象は変わりませんでした。
そうでしたか…お力になれずすみません。
やはり「一度に全てをinsertする」というのが難しいのだと思います。
多少プログラムの変更になりますが確かpostgreはlimitとoffsetが使えるので
1000件ずつとかはある程度楽に実装できますよ。
プログラムを修正するしか解決方法が無くなった場合は、
limitやoffsetを使用して実装したいと思います。
0
MS-Accessのデータベースには、そんな無茶なことは出来ないと
経験上でわかります。
少ないコーディングでちゃんと動いてほしいのであれば、
SQLServer他まっとうなRDBMSで組み直してほしいです。
どうしてもMS-Accessでないと行けないのだとしたら、
Recordsetオブジェクトとしてオープンして、
リンクテーブル側から1行読み込んだら他方に1行書き込む、
を繰り返すループ処理を作りつつ、
この先は勘に頼る部分なのですが適切に「DoEvents」を挟まないと
途中で処理が暴走するケースが有りました。
例えば100行処理するごとにとか、500行処理するごとにとかで、
細かくDoEventsを入れると処理時間が遅くなるので注意です。
DoEventsを入れて、
ファイル自己拡張などシステム側が何か処理できるタイミングを設けることが必要です。
それと試していないのですが、
排他ロック上でやったらまた変わるかもしれません。
処理中はほかからのアクセスを拒否するような。
投稿2018/01/12 00:50
退会済みユーザー
総合スコア0
回答ありがとうございます。
おっしゃる通り、一括でinsertするのではなく一定の行数ごとにループでinsertしていけば
解消できるかなとは考えています。
なぜ「極力プログラムは修正しない形で対応したい」と書いたかというと、
OracleからPostgreSQLにサーバーのデータベースを移行してから現象が出るようになったため、
PostgreSQL側の設定に何かしら問題があるのでは、と思ったためです。
だとすると、psqlODBCドライバーの改訂履歴を調べてみるのが良さそうな気がします。
https://odbc.postgresql.org/docs/release.html
で見ると「psqlODBC 09.05.0100 Release」のあと同じ09.05系は「psqlODBC 09.05.0400 Release」まで進んでいるので、
一旦入れ替えて試すってのをやってみてはいかがでしょうか。
念のため確認ですが、ODBCの設定は32bit版でやってますよね？
%windir%\SysWOW64\odbcad32.exe
ODBCの設定は32bit版で行っています。
ドライバーを最新にして試してみたいと思います。
最新のドライバーで試してみましたが、結果は変わりませんでした。
0
MDBのサイズは2Gまでですが、そのデータをinsertした後のサイズはどの程度ですか？
また、mdbは最適化しないとサイズは増える一方ですけど、対応済みでしょうか？
追記
よく見ると制御用のaccessからデータ用のMDBへinsertしているのですね。
毎回別なMDBを使用しているなら大丈夫だと思いますが、繰り返し使用しているなら最適化は必要です。
またその出力先のMDBは出力中に参照されるようなことはないでしょうか？
accessは共有に対しては非力で、よく破損しますので。
投稿2018/01/11 09:49
編集2018/01/12 01:13総合スコア25173
insertした後のサイズは約500MBなので、サイズの制約には引っかからないと考えています。
プログラムでの最適化は行っていません。
回答ありがとうございます。
＞またその出力先のMDBは出力中に参照されるようなことはないでしょうか？
無いとは言い切れませんが、今回の現象は絶対に参照されない状況下でも発生しています。
出力先のMDBは毎回同じですが、最適化後も現象が出てしまいます。
mdbが破損しかかっている場合に、動作が安定しないことがよくあります。
最適化しても修復しきれないケースがあるので、出力先のMDBの内容をすべて（テーブルは構造のみ）インポートした、新規のMDBを作成してみて下さい。
該当のテーブル構造のみの新規MDBを作成し実行してみましたが、現象は変わりませんでした。
では、リンクテーブルではなくパススルークエリーを作成し行ってみて下さい。
パススルークエリーとリンクテーブルではMSACCESSの振る舞いが変わってくるので、改善されるかもしれません。
リンクテーブルの代わりにパススルークエリーを使ってみましたが、結果は同じでした。
（メモリ消費量の動きも同じでした）
後は件数分割しないで行うこととしては、処理が変更になってしまいますが、テーブルに出力するのではなく、一旦CSVで出力して、MDBにインポートすることぐらいでしょうか。
あなたの回答
tips
プレビュー
