実現したいこと
見積のシステムを作る際、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,[見積日付]));
試したこと・調べたこと
上記の詳細・結果
・インデックスが効果的に使えていない
各主キーのインデックスは「はい(重複なし)」になっている
SQL serverにテーブルがあるためaccessでインデックスの変更ができない
デザインビューを開くとこのプロパティはリンクテーブルに保存できないと表示されている
・パススルークエリーで実行させる
作成し実行させると別エラーが発生する
[Microsoft][ODBC SQL Server Driver][SQL server]isnullには引数が2個必要(#174)。[Microsoft][ODBC SQL Server Driver][SQL server]')'付近に不適切な構文があります。(#102)
→SQL serverにクエリがないため接続できないと思われる
・データベースツールの「データベースの最適化」
変化なし
・データベースツールの「パフォーマンスの最適化」
改善点なしと表示
補足
見積(主 見積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 2019(.accdb)
0 コメント