SQLServerでとあるパラメーターを設定したときに結果が返ってこない（時間がかかりすぎる）

前提

C#でSQLServerを使用し、Windowsアプリケーションを作成しています。
取得したデータはCrystalReportで帳票表示します。

発生している問題・エラーメッセージ

とあるパラメーターをnullでない値にセットすると、
とても遅くなり、以下のタイムアウトエラーが発生します。
ーーーーーーー
System.Data.SqlClient.SqlException
HResult=0x80131904
Message=実行タイムアウトの期限が切れました。操作完了前にタイムアウト期間が過ぎたか、サーバーが応答していません。
Source=.Net SqlClient Data Provider
（場所省略）
内部例外 1:
Win32Exception: 待ち操作がタイムアウトになりました。
ーーーーーーー

以下に試したことなど記載いたしますが、あとは何を見ればよいのか・・・お手上げです。
何が原因と考えられるか、他に何を試すべきかご教授いただければ幸いです。

該当のソースコード

項目名など伏せないといけないものがあるため、すべて変更しました。
~~全文は文字数制限で載せられなかったので、回答欄に載せます。~~
載せられませんでした・・・

SQL
1SELECT
2       t.a group1
3      ,t.anm group1nm
4      ,ymb.b group2
5      ,ymb.bnm group2nm
6      ,CONVERT(int, CONCAT(ymb.yyyy, ymb.m)) group3
7      ,CONCAT(ymb.yyyy, '年', ymb.m, '月') komokunm
8      ,IsNull(c1.AA1, 0) AA1
9      ,IsNull(c1.AA2, 0) AA2
10      ,IsNull(c1.AA3, 0) AA3
11      ,IsNull(c1.AA4, 0) AA4
12      ,IsNull(c1.AA5, 0) AA5
13      ,IsNull(c1.AA6, 0) AA6
14      ,IsNull(c1.AA7, 0) AA7
15      ,IsNull(c1.BB1, 0) BB1
16      ,IsNull(c1.BB2, 0) BB2
17      ,IsNull(c1.BB3, 0) BB3
18      ,IsNull(c1.BB4, 0) BB4
19      ,IsNull(c1.BB5, 0) BB5
20      ,IsNull(c1.BB6, 0) BB6
21      ,IsNull(c1.BB7, 0) BB7
22      ,IsNull(c2.totalAA, 0) zAA
23      ,IsNull(c3.totalAA, 0) kAA
24      ,IsNull(s.AA, 0) AA
25      ,IsNull(m.MM, 0) MM
26      ,IsNull(n.NN1, 0) NN1
27      ,IsNull(n.NN2, 0) NN2
28  FROM
29       (SELECT DATEPART(yyyy, CONVERT(DATETIME, ym + '/01')) yyyy, DATEPART(m, CONVERT(DATETIME, ym + '/01')) m, b, bnm, a FROM W_YM ym, M_B b WHERE ym.flg5 = b.flg5) ymb
30LEFT JOIN
31       (SELECT
32       shorino
33      ,no
34      ,Gcd
35      ,ymd hiduke
36      ,CASE WHEN (CASE WHEN IsNull(shime, 0) = 0 THEN 1 ELSE shime END) <= 28 THEN (CASE WHEN DATEPART(d, ymd) > shime THEN DATEFROMPARTS(DATEPART(yyyy, DATEADD(m, 1, ymd)), DATEPART(m, DATEADD(m, 1, ymd)), (CASE WHEN IsNull(shime, 0) = 0 THEN 1 ELSE shime END)) ELSE DATEFROMPARTS(DATEPART(yyyy, ymd), DATEPART(m, ymd), (CASE WHEN IsNull(shime, 0) = 0 THEN 1 ELSE shime END)) END) ELSE DATEADD(d, -1, DATEFROMPARTS(DATEPART(yyyy, DATEADD(m, 1, ymd)), DATEPART(m, DATEADD(m, 1, ymd)), 1)) END ymd
37      ,Scd
38      ,Sjikan
39      ,kjikan
40      ,sjikan1
41      ,sjikan2
42      ,sjikanM
43      ,sjikanH
44      ,a
45      ,shime
46  FROM
47       T_N_H
48) h
49    ON
50       d.nippono = h.no
51LEFT JOIN
52       M_S s
53    ON
54       d.sagyoincd = s.sagyoincd
55LEFT JOIN
56       (SELECT
57       b
58      ,bnm
59      ,a
60      ,CASE WHEN @f= 1 THEN b ELSE IsNull(f_b, b) END f
61      ,pre_m
62      ,act_m
63      ,AA
64      ,kAA
65      ,NN2AA
66      ,kuAA
67      ,nyuAA
68      ,f_b
69      ,flg1
70      ,flg2
71      ,flg3
72      ,flg4
73      ,flg5
74  FROM
75       M_B
76) c
77    ON
78       h.Gcd = c.b
79LEFT JOIN
80       W_P_S p
81    ON
82       h.shime = p.shime
83 WHERE
84       h.ymd >= p.startymd
85   AND
86       h.ymd <= p.endymd
87   AND
88       (@tsujo = 1 OR (@tsujo = 2 AND c.flg1 = 1) OR (@tsujo = 3 AND c.flg1 = 0))
89GROUP BY  c.f, DATEPART(yyyy, h.ymd), DATEPART(m, h.ymd)
90) c1
91    ON
92       ymb.b = c1.f
93   AND
94       ymb.yyyy = c1.yyyy
95   AND
96       ymb.m = c1.m
97LEFT JOIN
98       M_ABC t
99    ON
100       t.a = ymb.a
101・・・省略・・・
102 WHERE
103       (@a is null OR t.a = @a)
104   AND
105       (@b is null OR ymb.b = @b)
106   AND
107       (IsNull(c1.AA2,0) <> 0 OR IsNull(c1.BB2,0) <> 0
108    OR
109       IsNull(c1.AA3,0) <> 0 OR IsNull(c1.BB3,0) <> 0
110    OR
111       IsNull(c1.AA4,0) <> 0 OR IsNull(c1.BB4,0) <> 0
112    OR
113       IsNull(c1.AA5,0) <> 0 OR IsNull(c1.BB5,0) <> 0
114    OR
115       IsNull(c1.AA6,0) <> 0 OR IsNull(c1.BB6,0) <> 0
116    OR
117       IsNull(c1.AA7,0) <> 0 OR IsNull(c1.BB7,0) <> 0
118    OR
119       IsNull(c2.totalAA, 0) <> 0 OR IsNull(c3.totalAA, 0) <> 0
120    OR
121       IsNull(s.AA, 0) <> 0 OR IsNull(m.MM, 0) <> 0
122    OR
123       IsNull(n.NN1,0) <> 0 OR IsNull(n.NN2,0) <> 0)

この[@a]が[3]、[@b]が[null]のときに発生します。
（[@a]が[null]や[@a]が[3]で[@b]が[100]のときは発生しません。長くても10秒ほどで帳票表示まで完了します。）

パラメーターは以下のように渡しています。
※実際はコンボボックスのValueを最終的にint型かDBNullで渡すメソッドを通してセットしていますが、以下の渡し方でも同じ現象が起こりました。

C#
1            using (SqlCommand cmd = new SqlCommand(strSQL, mCon))
2            {
3                cmd.Parameters.AddWithValue("a", 3);
4                cmd.Parameters.AddWithValue("b", null);
5                cmd.Parameters.AddWithValue("f", 1);
6                cmd.Parameters.AddWithValue("tsujo", 1);
7                using (SqlDataAdapter da = new SqlDataAdapter(cmd)) da.Fill(dt);　←エラー発生個所はここ
8            }

試したこと

タイムアウトの制限が原因かと思い、[CommandTimeout]を長く設定したのですが、
5分ほど待ちましたが処理が終わりませんでした。

こちら(https://baiteen.wordpress.com/2010/11/12/%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%99%E3%82%8B%E3%81%A8sqlserver%E3%81%AE%E3%83%AC%E3%82%B9%E3%83%9D%E3%83%B3%E3%82%B9%E3%81%8C%E9%81%85%E3%81%84/)
を参考に、パラメーターの型をInt型に指定しましたが結果は変わりませんでした。
これに関連してSQL側で直接キャストを試みましたが変わりませんでした。
ーーーーーーーー
(@a is null OR t.a = @a)
↓
(@a is null OR t.a = cast(@a as int))
ーーーーーーーー

SSMSで同じSQLを動作させましたが、
パラメーターでの動かし方が良くわからず、
単なる置き換えですとすぐに結果は返ってきます。（2秒ほど）
該当のパラメータをスカラー変数で指定してみても、特に問題なくすぐ処理は終わりました。

ikedas

2023/07/11 12:49

「色々省いており」とのことですが、当質問に掲載しているSQL文**そのもの**であってもタイムアウトが発生するということでしょうか。そうではないのなら、実際にタイムアウトが発生するSQL文を掲載したほうがいいと思います。

htk_htk

2023/07/11 12:59

コメントありがとうございます。都合により全文まるっきりそのまま掲載することはできないのですが、階層や結合状況、どこでいくつパラメーターを呼んでいるかなどは確かに合わせて記載するべきでした。失礼いたしました。今手元に環境はないのですが、 7つくらいLEFT JOINをしていたり、UNION ALLをしていたりそもそも重いSQLではあるとは思っております。ただ、このパラメーターを[null]にしていれば他のパラメーターを設定していても発生しなかったため、そういった結合状況などは関係ないかと思ってしまいました。すぐには難しいのですが、なるべく再現したSQL文を掲載させていただきます。（これを書いていて思ったのですがまずUNION ALLをする前に１個ずつのSQLで試してみようかと思います。）

ikedas

2023/07/11 13:05

そうですね。UNIONやJOINしているところやWHEREなどの条件をすこしずつ削っていって、どこを削るとタイムアウトが起きなくなるか---逆にいうと、タイムアウトを起こす最小限のSQL文はどんなものか---がわかると、解決に近づくと思います。ちょっと大変ですが。

YAmaGNZ

2023/07/12 00:35

SQLServer側でもそのクエリがどのように実行されているかプロファイラ等で確認してみてはどうでしょうか。

sazi

2023/07/12 07:10

> この[a]が[3]、[b]が[null]のときに発生します。この時のパラメータ値は何ですか？またパラメータ値の指定がある時に、条件となる項目の値がNullの場合は抽出されないのが仕様ですか？どのようなインデックス構成になっているのか気になりますが、実行計画の確認はされているんですよね？

ikedas

2023/07/12 08:06 編集

SQL文をかなりコンパクトにしていただいたので、実行プラン (「実行計画」、「問い合わせ計画」とも) を確認できるのなら、していただくとよいと思います。あまり複雑なSQL文だと実行プランを見るのも大変です。 (なおプロファイラについては、私はよく知らないんですがMSのサイトに「非推奨です」と書いてありました。) saziさんもおっしゃっていますが、実行プランの結果によってはインデックスの構成を変更してみる必要もでてくると思います。

YAmaGNZ

2023/07/12 07:56

プロファイラについてはマイクロソフトのページに以下のように書いてありました。＞SQL トレースと SQL Server プロファイラーは、非推奨です。 Microsoft SQL Server の Trace や＞Replay オブジェクトを含む Microsoft.SqlServer.Management.Trace 名前空間も非推奨とされます。＞＞この機能は、Microsoft SQL Server の将来のバージョンで削除されます。新規の開発作業ではこの機＞能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してく＞ださい。将来削除されるからこの機能を使用したものを作成するのは非推奨といった感じみたいですね。

htk_htk

2023/07/12 08:13

ikadas様、YAmaGNZ様、sazi様コメントありがとうございました。インデックス構成に関しては、プライマリーキーを設定したときに自動で作成されるもの以外何も触っていない状態です。この辺りも不勉強でどこにどう設定すれば良いかという知識が全くなく・・・こちらも今後の課題です。実行計画、プロファイラに関してもありがとうございます。こちらも同様、確認したものの読み解く力がまだ無く。情けない限りではございますが、今後実践交えて勉強していきたい所存です。

ikedas

2023/07/12 08:16

実行計画やプロファイルも取得できるのでしたら、それも差し支えない範囲で見せていただけるといいと思います。本番のSQL文ではなく、こちらに掲載していただいたコンパクトにしたSQL文で、これらのものを取得することは可能でしょうか。

行動規範の内容に同意します

回答1件

自己解決

根本解決になっているかわかりませんが、
色々実験してタイムアウトしないまでになったので、
結果記載いたします。

変更内容は以下です。
ーーーーーーーー
(@a is null OR t.a = @a)
↓
(@a is null OR ymb.a = @a)
ーーーーーーーー

検索するテーブルを、途中でLEFT JOINしているM_ABC tのテーブルから、
一番元になるテーブルへ変更しました。
この辺り、なぜこうなるかはっきりとはわかっていないので、
改めてこういったチューニング等の勉強が必要だなと思い知りました。
コメント頂戴いたしました皆様、ありがとうございました。

投稿2023/07/12 07:23

編集2023/07/12 07:24

htk_htk

総合スコア11