生産進捗管理(全統合)の時間のかかるクエリをストアドに移植

jms-funato commented 3 years ago

概要

SV04,SV05を仮想サーバにリプレース以降、Accessの動作が全体的に遅くなった原因はネットワークのスループットが原因と思われるまずは、特に遅い処理(20分→60分)をSQLServerのストアドプロシージャに置き換える

対象MDB

\\sv04\system\新進捗システム\確認用\生産進捗管理(全統合).accdb

対象処理

Form_F_メニューステータス.更新_年間()

修正オブジェクト

ローカルテーブルからリンクテーブルへ変更 T_G-X_混成号機の例外に号機末尾にHを追加 WT_生産計画GX補用 T_G-X補用本機混成変換用 WT_実績反映用 WT_実績反映用2

移行時作業

ローカルテーブルのレコードを変換したリンクテーブルにコピー

エラー

ストアド実行後、F_BSキャッチアップが表示されなくなる=>WT_進捗入力年間計画が空

jms-funato commented 3 years ago

動作テストの前にデータバックアップしようとしたら、処理テーブルのひとつSEISAN.T_進捗入力のコピーに異様に時間がかかる
特に制約等はないが、使われていないと思われる拡張プロパティが大量に設定してあり、そのチェック・読み込みに時間がかかっている？
拡張プロパティはAccess2010からアップサイジングウィザード経由で作成した場合に引き継がれた可能性が高い不要なら削除する

jms-funato commented 3 years ago

SEISAN.T_進捗入力の遅かった原因

default値制約が存在し、それを削除することでその他テーブルと変わらない速度でアクセスできるようになった
拡張プロパティ自体が速度低下の原因ではなかった
処理のストアド化は継続するが、一旦は現状の状態でどれだけ速度改善するか確認していただく

jms-funato commented 3 years ago

不要な制約値の削除とインデックス断片化解消スクリプトのスケジュール実行で、サーバ移行以前の処理時間(60分)に戻った

jms-funato commented 3 years ago

インデックスメンテタスク[sp_RebuildFragmentedIndex]がエラーで落ちており、インデックス断片化が解消されないままだった

タスクエラー

テーブル[プリプレグ管理スポット]でページロックエラーが発生していた

jms-funato commented 3 years ago

処理時間

ストアド版2分クエリ版44分

問題点

Q_硬化Gp更新対象：機種<>787 プリプレグ管理マスタが部品番号だけでは一意に特定できず実行の度に硬化Gpが変わってしまう可能性がある →当該クエリのみAccessクエリで実行し、ストアド内の前後の処理を2つの関数に分割して実行分割版処理時間：18分
T_対象機種に対応する[区分]が存在するのに、存在しない場合にセットされる対象区分=その他が存在する　C-2
Q_787号機別硬化Gp更新対象：機種=787 部品番号・号機・MODELをもとに硬化Gpを更新するがAccessRecordSet版と結果が一致しない

Q_硬化Gp更新等一部のクエリで使用されるマスタテーブルが正規化されておらず、当該クエリだけでクエリ版の処理時間の半分を占めている

修正案1

Access・SQLServer間のデータ転送がボトルネックになっている可能性が高いため、メインテーブルをローカルのWTで一旦処理して、最終結果のみSQLServerに返すようにしてみる

jms-funato commented 2 years ago

出力結果となるリンクテーブル[T_進捗入力年間計画]を処理のみローカルテーブルとして処理した場合

[①各機種データ取得_年間]処理時間オリジナルクエリ版…43:27 ローカルテーブル使用版…16:20 SQLはAccessのDBエンジンで実行されていて、テーブル名が違うだけなのに、硬化Gpで差異が発生してしまう→Accessクエリだと同じ部品番号でも実行するたびに結果が変わってしまう？→元のT_進捗入力年間計画テーブルを使う分には結果は変わらなかった(同じ硬化Gpが取得された)

パススルークエリ化する場合

各クエリSQLをSQLServer構文に置き換える必要があるため、ストアド化と変わらないむしろ通信回数はストアドより多くなってしまう

jms-web / FMS