アクセスのリンク接続で処理が重くなる

Question

実現したいこと

見積のシステムを作る際、AccessからSQL Serverに接続をしているのですが、1つのクエリがサーバへのSQL処理速度が遅くなりました。
処理が終わるまで20秒ほどかかります。
すぐに処理が終了するようにしたいです。

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

処理速度が遅いクエリは1つだけで、それ以外のクエリは問題なく起動します。
該当のクエリである総合計クエリは、IIfとIsNullを使い別クエリにある加工費や材料費などの合計を計算し、見積明細備考というテーブルにある見積明細備考にあるID1つにつき加工費や材料費の集計結果を出力するレコードがあります。

この処理を行う影響で重くなっていると思われるのですが制作している見積システムは、見積1つにつき各費用の合計を見れるようにする必要があり消すことができないです。
(例)
ID 加工費合計 材料費合計　初回費用合計　運賃合計
001 10 20 30 40
002 11 21 31 41

初心者で大変申し訳わけないのですが、処理をすぐに終わらせられる方法をご教授のほどお願い致します。

該当のソースコード

総合計クエリのSQL

SELECT 見積明細備考.見積明細備考ID, 見積明細.見積ID, 見積明細.見積明細ID, 見積明細備考.型番, 商品.商品名, 見積明細備考.納入価格単位, 見積明細備考.単位, 見積明細備考.数量, IIf((IsNull((SELECT Sum(Q_加工費.価格) FROM Q_加工費 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番))),0,(SELECT Sum(Q_加工費.価格) FROM Q_加工費 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番)) AS 加工費, IIf((IsNull((SELECT Sum(Q_材料費.価格) FROM Q_材料費 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番))),0,(SELECT Sum(Q_材料費.価格) FROM Q_材料費 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番)) AS 材料費, IIf((IsNull((SELECT Sum(Q_初回費用.価格) FROM Q_初回費用 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番))),0,(SELECT Sum(Q_初回費用.価格) FROM Q_初回費用 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番)) AS 初回費用合計, 見積明細備考.生産ロット, IIf((IsNull([初回費用合計]) Or [初回費用合計]=0 Or [生産ロット]=0),0,[初回費用合計]/[生産ロット]) AS 初回費用, IIf(IsNull((SELECT Q_運賃.数値 FROM Q_運賃 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番)),0,(SELECT Q_運賃.数値 FROM Q_運賃 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番)) AS 1枚あたり運賃, [加工費]+[材料費]+[初回費用]+[1枚あたり運賃] AS 原価合計, 見積明細備考.粗利, [原価合計]/(1-[粗利]) AS 総合計, Int([総合計]) AS 総合計2, [単位]+"入" AS 単位2, [総合計2]*[数量] AS 納入価格2, 見積明細.表版, 見積明細.見積日付, 取引先.得意先名, Year(DateAdd("m",-3,[見積日付])) AS 年度
FROM (取引先 INNER JOIN 見積明細 ON 取引先.得意先ID = 見積明細.得意先ID) INNER JOIN (商品 INNER JOIN 見積明細備考 ON 商品.型番 = 見積明細備考.型番) ON 見積明細.見積明細ID = 見積明細備考.見積明細ID
GROUP BY 見積明細備考.見積明細備考ID, 見積明細.見積ID, 見積明細.見積明細ID, 見積明細備考.型番, 商品.商品名, 見積明細備考.納入価格単位, 見積明細備考.単位, 見積明細備考.数量, 見積明細備考.生産ロット, 見積明細備考.粗利, [単位]+"入", 見積明細.表版, 見積明細.見積日付, 取引先.得意先名, Year(DateAdd("m",-3,[見積日付]));

試したこと・調べたこと

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

上記の詳細・結果

・インデックスが効果的に使えていない
各主キーのインデックスは「はい(重複なし)」になっている
SQL serverにテーブルがあるためaccessでインデックスの変更ができない
デザインビューを開くとこのプロパティはリンクテーブルに保存できないと表示されている

・パススルークエリーで実行させる
作成し実行させると別エラーが発生する
ODBC-- 呼び出しが失敗しました。
[Microsoft][ODBC SQL Server Driver][SQL server]isnullには引数が2個必要(#174)。[Microsoft][ODBC SQL Server Driver][SQL server]')'付近に不適切な構文があります。(#102)
→SQL serverにクエリがないため接続できないと思われる
SQLビューは総合計クエリと同じコード

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

・データベースツールの「パフォーマンスの最適化」
改善点なしと表示

補足

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

加工費,材料費,初回費用,運賃(主キーは名前+ID 　加工費IDなど)
各テーブルに見積ID、見積明細IDがある
見積明細ID、見積明細備考にある型番から該当の費用を参照し合計を総合計クエリに表示させている

取引先　商品
取引先には得意先ID,商品には型番を主キーにし参照することで得意先名と商品名を表示させるようにしている

原因解決のため参考にしたサイト
https://www.depthbomb.net/?p=219

SQL Serverへのリンク
下記のサイトを参考に連携
https://enoxproduction.com/access-link-table/

Microsoft Access 2021(.accdb)

Answer 1

・インデックスが効果的に使えていない

リンクテーブルにインデックスはありません。
疑似インデックスを作成する事は出来ますが、今回の解決にはならないでしょう。

・パススルークエリーで実行させる

クエリーの内容を見ましたが、パススルークエリーにはなっていません。
パススルークエリーは接続先のDBMSの環境で動作する内容で記述する必要があります。
※今回であれば、SQL Serverに発行して求める結果になるSQLを、パススルークエリーとします。
クエリー内で参照しているクエリーはWITH式で置き換えるようにすると良いでしょう。

複数のリンクテーブルを参照するクエリーが遅い場合の解決方法は、主にパススルークエリーを採用する事です。

Comments

[syuu07]

ご回答ありがとうございます。
パススルークエリーを使うことを決め総合計クエリを編集していたのですが、WITH式で置き換えることができません。
調べた限りWITH式は一時的なテーブルを作成することで、サブクエリに記載されたコードを取り出すことができるということがわかりました。
同じ式を何度も取り出すときに使う処理であることを理解し今回の場合下記のコードにあるWHEREの条件式が該当します。

IIf((IsNull((SELECT Sum(Q_加工費.価格) FROM Q_加工費 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番)))

総合計クエリにある見積明細IDと見積明細テーブルにある見積明細IDが同じであり、かつ総合計クエリにある型番と見積明細備考テーブルにある型番が同じである各費用を取り出すコードになっておりましてWITH句にするにはSELECT......とする必要があり作ることができません。
この式をWITH式にすることはできるのでしょうか？

[sazi]

概ね以下の内容になると思います。
with Q_加工費 as (
-- ここには、Q_加工費の中身を展開する
select ～
)
select
IIf((IsNull((
SELECT Sum(Q_加工費.価格) FROM Q_加工費
WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番
)))

ですが、パススルークエリーで動作するようにするのは手始めで、本題のパフォーマンスを改善する為にSQLチューニングを施す必要があるかもしれない事は忘れずに。

[syuu07]

ご返答ありがとうございます。
まず、加工費の合計だけを抽出するために下記のようにSQLビューに式を入力しました。

with Q_加工費 as (
select 加工費ID,見積ID,見積明細ID,型番,商品名,得意先ID,得意先名,年度,工程,工程数,単価,価格,人件費,更新日
FROM Q_加工費
)

SELECT 見積明細備考.見積明細備考ID, 見積明細.見積ID, 見積明細.見積明細ID, 見積明細備考.型番, IIf((IsNull((SELECT Sum(Q_加工費.価格) FROM Q_加工費 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番))),0,(SELECT Sum(Q_加工費.価格) FROM Q_加工費 WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番)) AS 加工費
FROM ((見積明細 INNER JOIN 見積明細備考 ON 見積明細.見積明細ID = 見積明細備考.見積明細ID) INNER JOIN 取引先 ON 見積明細.得意先ID = 取引先.得意先ID) INNER JOIN 商品 ON 見積明細備考.型番 = 商品.型番
GROUP BY 見積明細備考.見積明細備考ID, 見積明細.見積ID, 見積明細.見積明細ID, 見積明細備考.型番;

しかし、

sqlステートメントが正しくありません。Delete、Insert、Procedure、SelectまたはUpdateを使用して下さい。

というエラーが発生します。
Selectを使っているはずなのになぜこのようなエラーが発生するのでしょうか？

[sazi]

withの参照が出来ないのかもしれません。
withの位置を以下に変更してみてください。

select
IIf((IsNull((
with Q_加工費 as (
-- ここには、Q_加工費の中身を展開する
select ～
)
SELECT Sum(Q_加工費.価格) FROM Q_加工費
WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番
)))

[syuu07]

ご回答ありがとうございます。返信が遅くなり申し訳ございません。
with句の場所を変更し以下の通り実行しました。

select
IIf((IsNull((with Q_加工費 as (select 加工費ID,見積ID,見積明細ID,型番,商品名,得意先ID,得意先名,年度,工程,工程数,単価,価格,人件費,更新日 FROM Q_加工費)
SELECT Sum(Q_加工費.価格) FROM Q_加工費
WHERE 見積明細ID=見積明細.見積明細ID and 型番=見積明細備考.型番
)))

ですが、次のようなエラーが発生してしまいます。

[Microsoft][ODBC SQL Server Driver][SQL server]　キーワード 'with' 付近に不適切な構文があります。(#156)[Microsoft][ODBC SQL Server Driver][SQL server]このステートメントが共通テーブル式、xmlnamespaces 句、または変更追跡コンテキストの句の場合は、前のステートメントをセミコロンで終了してください。(#319)[Microsoft][ODBC SQL Server Driver][SQL server]')'付近に不適切な構文があります。(#102)

with句の後にセミコロンを追加しても同じエラーが発生しました。
エラー内容を調べてもSQLが間違っているくらいしかわからず回避することができません。
どのように変更すればエラーを回避できるでしょうか？

[sazi]

with式で定義したものを式内で呼び出ししていますけど(Q_加工費)、そういった場合は再帰用途のみですので、今回は違いますよね。
先にも述べましたが、先ずはODBC経由ではなく、SSMSなどのDBツールを用いて、SQLServer上で動作するSQLを作成してから、それをパススルークエリーとして下さい。※実行計画をこの段階で確認し、レスポンスに問題無いか確認して下さい。
Q_加工費のように内容が分からないものなどの影響があるかもしれず、判断は出来かねますので。

[syuu07]

ご回答ありがとうございます。
実行に問題がないことを確認した上で、コードを作成したいと思います。

[sazi]

推奨する訳ではありませんが、性能を改善する箇所のパススルークエリーを作成するのに手間取る場合には、リンクテーブルそのものを単純なパススルークエリーに変更する方法もあります。
内容としては、リンクテーブルをリンクテーブルと同じ名前の(select * from [リンクテーブル対象のテーブル])というパススルークエリーに置き換えるといったものです。
リンクテーブルを照会にしか使用していないなら置き換え可能だと思いますが、更新を行っているような場合には別な問題（競合等）が生じる可能性があります。

過去に、同手段でパフォーマンス改善したことはありますが、同じ結果になるかどうかは不明です。

[syuu07]

ご回答ありがとうございます。
with句を使わず、既にあるクエリをそのままパススルークエリーにするということでしょうか？

[sazi]

> with句を使わず、既にあるクエリをそのままパススルークエリーにするということでしょうか？
既存のクエリーをパススルークエリーにするのではなく、リンクテーブルを単純なパススルークエリーへの置き換えです。
あくまで、既存機能に影響が無ければですが。

[syuu07]

ご回答ありがとうございます。
何度か試した結果、無事with句を使い動くことができました。
ここまで答えてくださってありがとうございます。

[sazi]

解決したようで、何よりです。
後学の為に事例として教えて下さい。
２０秒がどの程度に短縮されたのでしょうか？

[syuu07]

データはほとんど入れていないのですが、2~3秒程度で実行できるようになりました。
with句は合計に必要なクエリが4つあったのでそれらを呼び出す際に使用させていただきました。

[sazi]

> データはほとんど入れていない
データが無いところで確認しても、実際のデータが入ると時間が掛かる事があります。
その場合はSQLチューニングやSQLServer側のインデックスの追加が必要になります。

[syuu07]

ご回答ありがとうございます。
連絡が遅くなり申し訳ございません。
SQLServer側をインデックスにする方法を調べ試しに一つの列を非クラスター化インデックスし実行してみたのですが、実行結果がClustered Index ScanからIndex Seakにならずindex化できません。
原因を調べてもよくわからず、どうすればindex化できるでしょうか？

コード
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
select * from 加工費
where 見積ID = '001771';

加工費
加工費ID(主キー int)　見積ID(varchar(20) NULL ×)

参考にしたサイト
https://anderson02.com/sqlserver/sqlserver-index/sqlserver-index-2-08/

[sazi]

変更されようとしているのは、実際にデータを投入したら、希望するパフォーマンスではなかったという事ですか？
それは、SQLのチューニングですので、実行計画を元に調整する事になります。
パススルークエリー自体は成功したのなら、カテゴリはSqlServerで別質問とされた方が良いです。
※そもそも、SQLを変更すればインデックスは不要かもしれませんし、回答するにはテーブル構造が情報として必要です。

[syuu07]

ご回答ありがとうございます。
わかりました、別質問にて作成いたします。