いつもお世話になります。
VisualStudio2005とSQLserver2008を利用しています。
現在、クライアント毎に宅配便の利用履歴を表示する機能を作成しています。
メインとなる宅配便マスタに複数のマスタを結合して表示しているのですが、処理が非常に遅くて困っています。
主要な原因は結合の多用だと考えているのですが、どう削減していいものかわかりません。
以下は実際のSQL文です。かなり長いので結合部分だけ抜き出しています。
Dim strSelect As String = ""
strSelect &= " select CM.クライアント名 as クライアント,"
strSelect &= " CO13.汎用名称 as ターミナル,"
strSelect &= " CO2.汎用名称 as 内容,"
strSelect &= " CO6.汎用名称 as 便種,"
strSelect &= " CO7.汎用名称 as 運送業者,"
strSelect &= " AM1.エリア as 仕入エリア,"
strSelect &= " AM2.エリア as 請求エリア,"
For i As Integer = 1 To 8
strSelect &= " CO89_" & i & ".汎用名称 as サイズ" & i & ", "
strSelect &= " CAST(FLOOR(TTm" & i & ".単価) as int) as 仕入単価" & i & ", "
strSelect &= " CAST(FLOOR(TT" & i & ".単価) as int) as 請求単価" & i & ", "
strSelect &= " TM.個数" & i & ", "
strSelect &= " CAST(FLOOR(TTm" & i & ".単価 * TM.個数" & i & ") as int) as 仕入金額" & i & ", "
strSelect &= " CAST(FLOOR(TT" & i & ".単価 * TM.個数" & i & ") as int) as 請求金額" & i & ", "
Next
strSelect &= " SM.社員名 as 担当"
strSelect &= " from 宅配便マスタ as TM "
strSelect &= " inner join (select * from クライアントマスタ union select * from 関連会社マスタ) as CM on TM.ClientNO = CM.ClientNO "
strSelect &= " left outer join (select * from コードマスタ where 識別コード=00002) as CO2 on TM.内容NO = CO2.汎用コード"
strSelect &= " left outer join (select * from コードマスタ where 識別コード=00006) as CO6 on TM.便種 = CO6.汎用コード"
strSelect &= " left outer join (select * from コードマスタ where 識別コード=00007) as CO7 on TM.運送業者 = CO7.汎用コード"
strSelect &= " left outer join (select * from コードマスタ where 識別コード=00013) as CO13 on TM.ターミナル = CO13.汎用コード"
For i As Integer = 1 To 8
strSelect &= " left outer join (select * from コードマスタ where 識別コード=00008 or 識別コード=00009)"
strSelect &= " as CO89_" & i & " on TM.サイズ" & i & " = CO89_" & i & ".汎用コード"
Next
For i As Integer = 1 To 8
strSelect &= " left outer join (select * from 宅配便タリフ where ClientNO = '" & FlexI & "') as TTm" & i & ""
strSelect &= " on TM.仕入エリア = TTm" & i & ".エリア and TM.便種 = TTm" & i & ".便種 "
strSelect &= " and TM.運送業者 = TTm" & i & ".運送業者 and TM.サイズ" & i & " = TTm" & i & ".サイズ"
strSelect &= " and TM.出荷日 >= TTm" & i & ".適用開始 and TM.出荷日 <= TTm" & i & ".適用終了"
Next
For i As Integer = 1 To 8
strSelect &= " left outer join 宅配便タリフ as TT" & i & " on TM.ClientNO = TT" & i & ".ClientNO and TM.請求エリア = TT" & i & ".エリア"
strSelect &= " and TM.便種 = TT" & i & ".便種 and TM.運送業者 = TT" & i & ".運送業者 and TM.サイズ" & i & " = TT" & i & ".サイズ"
strSelect &= " and TM.出荷日 >= TT" & i & ".適用開始 and TM.出荷日 <= TT" & i & ".適用終了"
Next
strSelect &= " left outer join エリアマスタ as AM1 on TM.仕入エリア = AM1.コード"
strSelect &= " left outer join エリアマスタ as AM2 on TM.請求エリア = AM2.コード"
strSelect &= " inner join 社員マスタ as SM on TM.社員ID = SM.社員ID"
strSelect &= " left outer join 都道府県マスタ as TDM on TM.都道府県 = TDM.コード "
strSelect &= " where " & strWhere
strSelect &= " order by TM.ClientNO, TM.出荷NO, TM.出荷日"
Return strSelect
明らかに無駄と思える箇所としては、佐川急便が一つの伝票で8種類までの重量区分を一度に処理できる形態を取っているため、
サイズ1~8、仕入単価1~8、請求単価1~8を求めるために同じマスタを8つずつ結合する羽目に陥っているところが挙げられますが……。
よい方法がありましたら、是非お知恵を拝借したいと思います。
お手数をおかけしますが、よろしくお願いします。
No.2ベストアンサー
- 回答日時:
strSelect &= " inner join (select * from クライアントマスタ union select * from 関連会社マスタ) as CM on TM.ClientNO = CM.ClientNO "
これはunionしているから仕方ないですけど、
他のjoinの大半について。
strSelect &= " left outer join (select * from コードマスタ where 識別コード=00002) as CO2 on TM.内容NO = CO2.汎用コード"
などはなんでleft outer join (select * from ・・・)なんですか?
left outer join コードマスタ as CO2 on TM.内容NO = CO2.汎用コード and 識別コード=00002
としていないから副問い合わせをした結果と結合するので、
インデックスは使えないし、作業用にメモリかディスクは食っているし負荷とレスポンスの大敵と
なっていますけど。
なお、
inner join (select * from クライアントマスタ union select * from 関連会社マスタ) as CM on TM.ClientNO = CM.ClientNO
も、
inner join クライアントマスタ as CM1 on TM.ClientNO = CM1.ClientNO
inner join 関連会社マスタ as CM2 on TM.ClientNO = CM2.ClientNO
として、冒頭のSelect句の、
select CM.クライアント名 as クライアント,
を
select case when CM1.クライアント名 is not null then CM1.クライアント名
else CM2.クライアント名 end as クライアント,
とし、末尾のwhere句に
and (CM1.クライアント名 is not null or CM2.クライアント名 is not null)
としたほうが、私が思っているデータ量なら、はやいと思います。
(select * from クライアントマスタ union select * from 関連会社マスタ
が数十件程度しか取得しないのならこちらは無視してください。)
Siegrune様、お礼が大変遅くなりました。
ご指摘をいただいた後、改めて見返してみて自身のコードの酷さに頭を抱えてしまいました。
onの後にandを入れること自体に気づいていなかったならともかく、
一部のコードでは使っているという無茶苦茶な有様。
全体的に整理して、だいぶすっきりさせることができました。
本当にありがとうございました。
クライアントマスタと関連会社マスタに関しては50件程度なのでひとまず現状のままとし、
今後の取引先の増加に備えてSQL文をコメントで残しておきます。
No.1
- 回答日時:
正直、SQLが複雑すぎます。
こちらで展開してみましたが投稿文字数の制限に引っかかってしまいました。まず、( SELECT ~ WHERE ) とLEFT JOINの組み合わせが多すぎます。
これでは( SELECT ~ WHERE )で絞り込んだデータと元データとのネステッドループジョインが多発してしまいます。
本当にLEFT JOINの必要があるのでしょうか?また、そもそも、マスタにないデータの入力を許すようなシステムに問題はないでしょうか(参照整合性制約の利用も考えられます)。
この部分をINNER JOINのON条件にまとめることが出来れば、複合索引を作成しパフォーマンスが向上できると思います。
(select * from クライアントマスタ union select * from 関連会社マスタ)の部分も「union」を「union all」にはできないのでしょうか。
nora1962様、御礼が大変遅くなりました。
ご回答ありがとうございます。
left joinの必要性に関しては、各項目を出荷伝票取込時、出荷報告書取込時、請求書取込時の3段階に分けて取り込んでいるのですが、すべての項目が埋まる前に一覧を表示することが可能となるように用いています。
union allについては知識不足でした。
早速取り入れさせていただきました。
ありがようございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL うまくいきません教えてくださいお願いしますSQLです。クエリ構文です。 1 2023/07/07 12:39
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
- Visual Basic(VBA) ExcelからAccessのテーブルに書き込む時に時間がかかる 1 2022/10/14 20:38
- PHP アコーディオンPHPが上手くいかない 3 2022/07/15 16:29
- Visual Basic(VBA) EXCEL VBAにて動的にCheckBOXを複数作成し、同BOXにイベントを追加したい 1 2023/03/16 07:05
- Visual Basic(VBA) サブフォルダ(データ)にある複数の.xlsxファイルのSheet3のA2セルの値で01から左側をB2 2 2022/08/14 15:46
- Visual Basic(VBA) エクセル VBA 難しいです 1 2023/02/21 15:39
- Visual Basic(VBA) ①ExcelVBAでカレンダーを作り、別のユザーフォームで日付を入力したいのですがエラーになります。 1 2023/02/17 18:39
- PostgreSQL SQLで検索結果の記事を表示したい 1 2022/04/28 21:03
- Excel(エクセル) VBA フォルダ見える化のコードについて 2 2023/06/19 15:04
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
SQL文で、合計が0のレコードを...
-
EXISTSを使ったDELETE文
-
Accessの実行時エラーについて
-
ACCESS ツリービューの作り方
-
ACCESS 商品毎の最新の単価を設...
-
異なるデータベース間のテーブ...
-
前月の取得について
-
差し込み後、元データを変更し...
-
止まなーい雨はない でもお前に...
-
エクセルVBAコードで教えて下さ...
-
フィルターかけた後、重複を除...
-
SELECT 文 GROUP での1件目を...
-
外部参照してるキーを主キーに...
-
エクセルで最後の文字だけ置き...
-
for whichの使い方
-
datetime型でNULL値を入れたい。
-
SQL Date型の列から年月だけを...
-
カーソル0件の時にエラーを発生...
-
列のヘッダーを含めるのをデフ...
-
SQLによる"あいうえお"順でソー...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SQL文で、合計が0のレコードを...
-
EXISTSを使ったDELETE文
-
Accessの実行時エラーについて
-
ACCESS ツリービューの作り方
-
日付により変動する「単価」を...
-
前月の取得について
-
ACCESS 商品毎の最新の単価を設...
-
アクセス★非課税、課税の合計金...
-
AS400にてサブファイルレコード...
-
アクセス フィールド名に変数...
-
ユニオンクエリ?レポートにて...
-
ACCESSでクエリ作成時複数のフ...
-
Access2013で商品に複数の単価...
-
アクセスにて月末日付を取得し...
-
異なるデータベース間のテーブ...
-
AccessVBA データのエクスポート
-
フィールドの数値を四捨五入の...
-
FROM句にサブクエリ使えませんか
-
最大値を含むレコードの抽出
-
無駄に見える結合の回数を減ら...
おすすめ情報