Access 更新クエリが重くラグが発生してしまう

実現したいこと

数百件あるデータがあるテーブルがあり一斉に更新クエリを使った際、20秒ほどラグが発生してしまいます。
更新クエリを使わずに実行したときラグが起きなかったので原因は更新クエリだと考えました。
実現したいこととしましては、極力ラグを抑えたいです。

発生している問題・分からないこと

主となる見積明細テーブルの年度が変更した際、別のテーブルにある年度が一斉に更新する更新クエリを作りました。
しかし、数百件あるデータを一斉に更新したため更新が終わるまで20秒ほどラグが発生してしまいます。
どうすればラグを抑えることができるでしょうか？
初心者で申し訳ございませんが、ご教授の方よろしくお願いします。

該当のソースコード

更新クエリのソースコード

UPDATE 加工費 INNER JOIN Q_見積明細 ON 加工費.見積明細ID = Q_見積明細.見積明細ID SET 加工費.年度 = [Q_見積明細]!年度;

加工費の部分が別の表だと材料費などに変わります。
このコードを元に作成したコードを4つのテーブルで更新させ、それらを一斉に実行させた状態でラグが起きます。

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

・パススルークエリ
→見積明細テーブルを編集したいため更新することが不可能なパススルークエリを使うことができない。
更新先である加工費や材料費のテーブルも同じ

・リレーションシップ
→見積明細テーブルのクエリであるQ＿見積明細に年度があり、下記のコードにて作成しクエリにしか年度コードがないためリレーションシップにすることもできない

年度: Year(DateAdd("m",-3,[見積日付]))

・インデックス　
→リンクテーブルの各年度テーブルにインデックスを設置
変化なし

インデックスを作る際参考にしたサイト
https://anderson02.com/sqlserver/sqlserver-index/sqlserver-index-2-08/

・データベースツールの「データベースの最適化」
変化なし

補足

・Accessのデータ自体はSQLサーバーである　SQL Server 2022 express　にて保存。Accessとリンク済み

Microsoft Access 2021(.accdb)

接続先(別のPC側)
・リモートデスクトップ接続にて操作している
・SQL Server Management Studio 19
・SQL Server 2022 express
SQL Server 認証にて接続
・Windows Server 2022 Standard
・名前付きインスタンス

見積(主　見積ID)ー見積明細(見積明細ID)ー見積明細備考(見積明細備考ID)

加工費,材料費,初回費用,運賃(主キーは名前+ID 　加工費IDなど)
各テーブルに見積ID、見積明細IDがある

SUPER_TRADER

2024/03/18 08:17

年度を更新するクエリってそう何度も使うものではないのでは？それこそ1年に一度くらいではないのでしょうか？

logres_Fan

2024/03/18 12:00

年度ってUpdateするものですっけ？Insertしていくものじゃないの？

syuu07

2024/03/19 04:00

コメントありがとうございます。今回は年度を例にしましたがほかの項目でも使いたいので投稿させてもらいました。

行動規範の内容に同意します

回答1件

ベストアンサー

ラグというのが、「複数の更新クエリーを実行し、それぞれのクエリーが前のクエリーの結果を参照している」という事に対してのものなら、
クエリーの実行を、非同期型のDoCmdではなく、同期型のExecuteを使用して下さい。

そうではなく、「一つの更新クエリーについて」の話なら、クエリー内での参照元がクエリーである場合、参照元のクエリーが完結していない状態で結果が表示されることはあります。
そうなる理由として、例えばクエリーやテーブルを開いた際に、早く開くように、表示されていない部分は遅延する動きと同等な動作をしているのだと推測されます。
SQLチューニングによる高速化により改善されたように見えたとしても完全ではなく、結局ネットワークの負荷などが生じると発生したりします。
※そもそも、リンクテーブルを参照し結合した場合に有効なインデックスはリンクテーブルそのものに設定する疑似インデックスです。
SQLServerでのインデックスの効果を得られるのは、パススルークエリーです。

回避策としては、
A.更新クエリー経由ではなく、直接DBを更新する
【MS Access】パススルークエリからストアドを実行したりUPDATE文を実行する方法

B.参照元のクエリーをテーブルに落とし込み、データを物理的に確定させ、その結果を参照するようにする。
※クエリー内で参照しているクエリーを展開して、一つのクエリーにしても改善しなかったと記憶しています。
※ネストが無いようにSQLを書き換える事が可能なら改善するかもしれませんが、集計があったりすると処理的に書き換え出来なかったりします。

A案推奨です。

投稿2024/03/18 14:43

編集2024/03/19 02:18