無駄に見える結合の回数を減らしたい

質問

いつもお世話になります。
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

明らかに無駄と思える箇所としては、佐川急便が一つの伝票で８種類までの重量区分を一度に処理できる形態を取っているため、
サイズ1~8、仕入単価1~8、請求単価1~8を求めるために同じマスタを８つずつ結合する羽目に陥っているところが挙げられますが……。

よい方法がありましたら、是非お知恵を拝借したいと思います。
お手数をおかけしますが、よろしくお願いします。

回答No.2

Siegrune · ベストアンサー

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

nora1962

正直、SQLが複雑すぎます。こちらで展開してみましたが投稿文字数の制限に引っかかってしまいました。
まず、( SELECT ～ WHERE ) とLEFT JOINの組み合わせが多すぎます。
これでは( SELECT ～ WHERE )で絞り込んだデータと元データとのネステッドループジョインが多発してしまいます。
本当にLEFT JOINの必要があるのでしょうか？また、そもそも、マスタにないデータの入力を許すようなシステムに問題はないでしょうか（参照整合性制約の利用も考えられます）。
この部分をINNER JOINのON条件にまとめることが出来れば、複合索引を作成しパフォーマンスが向上できると思います。
(select * from クライアントマスタ union select * from 関連会社マスタ)の部分も「union」を「union all」にはできないのでしょうか。

お礼

nora1962様、御礼が大変遅くなりました。
ご回答ありがとうございます。
left joinの必要性に関しては、各項目を出荷伝票取込時、出荷報告書取込時、請求書取込時の３段階に分けて取り込んでいるのですが、すべての項目が埋まる前に一覧を表示することが可能となるように用いています。

union allについては知識不足でした。
早速取り入れさせていただきました。
ありがようございました。