C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[3]: SQL文について

(過去ログ 175 を表示中)

[トピック内 6 記事 (1 - 6 表示)]　 << 0 >>

■100904 / inTopicNo.1)

　SQL文について

▼■

□投稿者/ 鏡月 (3回)-(2022/11/21(Mon) 10:52:45)

分類:[データベース全般]　

SQL実行速度が遅いのですが速度が速いやり方をご存知でしたらご教授願います。。

下記サンプルテーブルを使用し数量フィールドの集計を行いたいと考えております。
集計の条件は「依頼No,作業No」毎に集計しますが作業Noにnullが登録されている依頼Noは出力結果の対象外とします。

サンプルテーブル
依頼No,作業No,数量
1000,a,1
1000,a,12
1000,b,2
1000,b,22
1000,c,3
1001,null,4
1001,d,5
1001,d,15
1001,e,6
1001,f,7
1002,g,8
1002,g,1
1002,h,9
1003,i,10
1004,null,11
1004,null,2
1004,j,12

出力結果
1000,a,13
1000,b,24
1000,c,3
1002,g,9
1002,h,9
1003,i,10

作成したSQL
SELECT 依頼No,作業No,SUM(数量) AS 数量
FROM サンプルテーブル
WHERE 作業No NOT IN
(
SELECT 作業No FROM サンプルテーブル WHERE 作業No IS NULL
)
GROUP BY 依頼No,作業No

引用返信 編集キー/

■100905 / inTopicNo.2)

　Re[1]: SQL文について

▲▼■

□投稿者/ furu (185回)-(2022/11/21(Mon) 11:34:26)

2022/11/21(Mon) 11:48:06 編集(投稿者)

■No100904 (鏡月さん) に返信
> 作成したSQL
> SELECT 依頼No,作業No,SUM(数量) AS 数量
> FROM サンプルテーブル
> WHERE 作業No NOT IN
> (
> SELECT 作業No FROM サンプルテーブル WHERE 作業No IS NULL
> )
> GROUP BY 依頼No,作業No
そもそもWHERE間違えていない？

こう？
WHERE 依頼No NOT IN
(
SELECT 依頼No FROM サンプルテーブル WHERE 作業No IS NULL
)

> SQL実行速度が遅いのですが速度が速いやり方をご存知でしたらご教授願います。。
NOT INよりINのが早いかな

SELECT 依頼No,作業No,SUM(数量) AS 数量
FROM サンプルテーブル
WHERE 依頼No IN
(
SELECT 依頼No FROM サンプルテーブル GROUP BY 依頼No HAVING COUNT(*) = COUNT(作業No)
)
GROUP BY 依頼No,作業No

引用返信 編集キー/

■100906 / inTopicNo.3)

　Re[1]: SQL文について

▲▼■

□投稿者/ 魔界の仮面弁士 (3494回)-(2022/11/21(Mon) 11:54:58)

■No100904 (鏡月さん) に返信
> SQL実行速度が遅いのですが
データベース側が該当している場合は、依頼No 列に
<ビットマップ・インデックス> を張ると早くなるかも。

> 下記サンプルテーブルを使用し数量フィールドの集計を行いたいと考えております。
> 集計の条件は「依頼No,作業No」毎に集計しますが作業Noにnullが登録されている依頼Noは出力結果の対象外とします。
サンプルデータのデータ件数次第では、ウィンドウ関数の方が探索パスが少なくて済むかも。

WITH X AS (SELECT 依頼No, 作業No, SUM(数量) OVER (PARTITION BY 依頼No, 作業No) AS 数量
, MIN(CASE WHEN 作業No IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY 依頼No) AS P
FROM サンプルテーブル) SELECT DISTINCT 依頼No, 作業No, 数量 FROM X WHERE P = 1
-- ORDER BY 依頼No, 作業No

引用返信 編集キー/

■100907 / inTopicNo.4)

　Re[1]: SQL文について

▲▼■

□投稿者/ KOZ (356回)-(2022/11/21(Mon) 12:17:06)

■No100904 (鏡月さん) に返信
> 集計の条件は「依頼No,作業No」毎に集計しますが作業Noにnullが登録されている依頼Noは出力結果の対象外とします。

キーの張り方にもよりますが

SELECT 依頼No,作業No,SUM(数量) AS 数量
FROM サンプルテーブル A
WHERE NOT EXISTS (
SELECT 1 FROM サンプルテーブル B
WHERE B.依頼No = A.依頼No AND B.作業No IS NULL
)
GROUP BY 依頼No,作業No

引用返信 編集キー/

■100908 / inTopicNo.5)

　Re[2]: SQL文について

▲▼■

□投稿者/ 鏡月 (4回)-(2022/11/21(Mon) 13:56:28)

申し訳ございません。
おっしゃる通りWHERE句の条件を間違っておりました。

私が作成したSQL：実行時間：2分15秒、1554行

furu様が作成したSQL：実行時間：2分15秒、1554行

魔界の仮面弁士様が作成したSQL：私の書き方が悪いかもしれませんが21分たっても結果が出力されませんでした。

KOZ様が作成したSQL：実行時間：5分06秒、1554行

ボトルネックが違う箇所の恐れがありますので再度一からSQLを確認したいと思います。
色々なご意見をいただきありがとうございます。

解決済み

引用返信 編集キー/

■100909 / inTopicNo.6)

　Re[3]: SQL文について

▲▼■

□投稿者/ 鏡月 (5回)-(2022/11/22(Tue) 13:18:54)

最終確認が出来ましたのでご報告させていただきます。

SQLを見直すついでに色々と試しましたが「NOT IN」、「IN」、「NOT EXISTS」より「MIN(CASE WHEN 作業No IS NULL THEN 0 ELSE 1 END)」を使用したほうが速度の改善につながり実行速度が分単位から数秒単位まで改善されました。

ご回答していただきました「furu 様」、「魔界の仮面弁士様」、「KOZ様」ありがとうございました。

解決済み

引用返信 編集キー/

トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

- Child Tree -