teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問SQLクエリのパフォーマンス要因（異なる列の指定による変化）

編集履歴

質問編集履歴

2

ソースコード若干修正（文言のみ）

2019/05/14 09:43

投稿

Ry22434
Ry22434

スコア15

title CHANGED
File without changes
body CHANGED
@@ -37,7 +37,7 @@
37
37
 
38
38
  WITH BASE AS (SELECT [DTP_MEMBER_ID]
39
39
  FROM
40
- [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
40
+ [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
41
41
  WHERE
42
42
  [DTP_COMPANY] = '001' AND
43
43
  MONTH([DTP_TRX_DATE]) = '5' AND
@@ -60,7 +60,7 @@
60
60
  [DTP_AMOUNT],
61
61
  [DTP_POINTS],
62
62
  ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
63
- FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
63
+ FROM [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
64
64
  WHERE [DTP_COMPANY] = '001' AND
65
65
  MONTH([DTP_TRX_DATE]) = '6' AND
66
66
  YEAR([DTP_TRX_DATE]) = '2018' AND
@@ -73,7 +73,7 @@
73
73
 
74
74
  WITH BASE AS (SELECT [DTP_MEMBER_ID]
75
75
  FROM
76
- [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
76
+ [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
77
77
  WHERE
78
78
  [DTP_COMPANY] = '001' AND
79
79
  MONTH([DTP_TRX_DATE]) = '5' AND
@@ -96,7 +96,7 @@
96
96
  [DTP_AMOUNT],
97
97
  [DTP_POINTS_BASIC],
98
98
  ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
99
- FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
99
+ FROM [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
100
100
  WHERE [DTP_COMPANY] = '001' AND
101
101
  MONTH([DTP_TRX_DATE]) = '6' AND
102
102
  YEAR([DTP_TRX_DATE]) = '2018' AND

1

詳細を記載

2019/05/14 09:43

投稿

Ry22434
Ry22434

スコア15

title CHANGED
File without changes
body CHANGED
@@ -1,1 +1,115 @@
1
- SQLに似たような性質（データ型:decimal）の列に条件を置き換えてSQLクエリを回したところ、大幅にパフォーマンスが異なり、困惑しております。
1
+ SQLに似たような性質（データ型:decimal）の列に条件を置き換えてSQLクエリを回したところ、大幅にパフォーマンスが異なり、困惑しております。
2
+ ### 前提・実現したいこと
3
+
4
+ クエリのパフォーマンスの悪化原因を明らかにし、パフォーマンスの大幅な改善を図りたい。
5
+
6
+
7
+
8
+ ### 発生している問題・エラーメッセージ
9
+
10
+ ```
11
+ SQLに似たような性質（データ型:decimal）の列に条件を置き換えてSQLクエリを回したところ、大幅にパフォーマンスが異なり、困惑しております。
12
+
13
+ DTP_POINTS
14
+ Decimal(16,０) null) - NOT ALLLOW NULL
15
+ 処理時間５分
16
+
17
+ DTP_POINTS_BASIC
18
+ Decimal(18,０）not null) - ALLLOW NULL
19
+ 処理時間２時間以上
20
+
21
+ ```
22
+
23
+ ### 該当のソースコード
24
+ ２つあります
25
+ ```SQL
26
+ 違いは指定した列の違いになります。指定列が[DTP_POINTS]と [DTP_POINTS_BASIC] だけが以下のコードで異なります。
27
+
28
+ DTP_POINTS
29
+ Decimal(16,０) null) - NOT ALLLOW NULL
30
+ 処理時間５分
31
+
32
+ DTP_POINTS_BASIC
33
+ Decimal(18,０）not null) - ALLLOW NULL
34
+ 処理時間２時間以上
35
+
36
+ **__処理時間５分__**
37
+
38
+ WITH BASE AS (SELECT [DTP_MEMBER_ID]
39
+ FROM
40
+ [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
41
+ WHERE
42
+ [DTP_COMPANY] = '001' AND
43
+ MONTH([DTP_TRX_DATE]) = '5' AND
44
+ YEAR([DTP_TRX_DATE]) = '2018' AND
45
+ [DTP_TRANSACTION_CODE] = 'POI')
46
+
47
+ SELECT
48
+ (SELECT COUNT(DISTINCT [DTP_MEMBER_ID]) FROM BASE) AS 'UNIQUE_MEMBER_PREVIOUS_MONTH',
49
+ COUNT(DISTINCT BASE2.[DTP_MEMBER_ID]) AS 'UNIQUE_MEMBER_NEXT_MONTH',
50
+ SUM(BASE2.[DTP_AMOUNT]) AS 'AMOUNT_AT_FIRST_TRX',
51
+ SUM(BASE2.[DTP_POINTS]) AS 'POINT_ISSUE_AT_FIRST_TRX',
52
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 500 ELSE BASE2.[DTP_POINTS] END) AS 'BONUS_POINT_2X',
53
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 750 ELSE FLOOR(BASE2.[DTP_POINTS] * 1.5) END) AS 'BONUS_POINT_3X',
54
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1000 ELSE FLOOR(BASE2.[DTP_POINTS] * 2) END) AS 'BONUS_POINT_4X',
55
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1250 ELSE FLOOR(BASE2.[DTP_POINTS] * 2.5) END) AS 'BONUS_POINT_5X'
56
+
57
+ FROM (
58
+ SELECT
59
+ [DTP_MEMBER_ID],
60
+ [DTP_AMOUNT],
61
+ [DTP_POINTS],
62
+ ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
63
+ FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
64
+ WHERE [DTP_COMPANY] = '001' AND
65
+ MONTH([DTP_TRX_DATE]) = '6' AND
66
+ YEAR([DTP_TRX_DATE]) = '2018' AND
67
+ [DTP_TRANSACTION_CODE] = 'POI' AND [DTP_MEMBER_ID] IN
68
+ (SELECT [DTP_MEMBER_ID] FROM BASE)) AS BASE2
69
+ WHERE TRX_COUNT = 2
70
+
71
+
72
+ **__処理時間２時間以上__**
73
+
74
+ WITH BASE AS (SELECT [DTP_MEMBER_ID]
75
+ FROM
76
+ [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
77
+ WHERE
78
+ [DTP_COMPANY] = '001' AND
79
+ MONTH([DTP_TRX_DATE]) = '5' AND
80
+ YEAR([DTP_TRX_DATE]) = '2018' AND
81
+ [DTP_TRANSACTION_CODE] = 'POI')
82
+
83
+ SELECT
84
+ (SELECT COUNT(DISTINCT [DTP_MEMBER_ID]) FROM BASE) AS 'UNIQUE_MEMBER_PREVIOUS_MONTH',
85
+ COUNT(DISTINCT BASE2.[DTP_MEMBER_ID]) AS 'UNIQUE_MEMBER_NEXT_MONTH',
86
+ SUM(BASE2.[DTP_AMOUNT]) AS 'AMOUNT_AT_FIRST_TRX',
87
+ SUM(BASE2.[DTP_POINTS_BASIC]) AS 'POINT_ISSUE_AT_FIRST_TRX',
88
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 500 ELSE BASE2.[DTP_POINTS_BASIC] END) AS 'BONUS_POINT_2X',
89
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 750 ELSE FLOOR(BASE2.[DTP_POINTS_BASIC] * 1.5) END) AS 'BONUS_POINT_3X',
90
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1000 ELSE FLOOR(BASE2.[DTP_POINTS_BASIC] * 2) END) AS 'BONUS_POINT_4X',
91
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1250 ELSE FLOOR(BASE2.[DTP_POINTS_BASIC] * 2.5) END) AS 'BONUS_POINT_5X'
92
+
93
+ FROM (
94
+ SELECT
95
+ [DTP_MEMBER_ID],
96
+ [DTP_AMOUNT],
97
+ [DTP_POINTS_BASIC],
98
+ ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
99
+ FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
100
+ WHERE [DTP_COMPANY] = '001' AND
101
+ MONTH([DTP_TRX_DATE]) = '6' AND
102
+ YEAR([DTP_TRX_DATE]) = '2018' AND
103
+ [DTP_TRANSACTION_CODE] = 'POI' AND [DTP_MEMBER_ID] IN
104
+ (SELECT [DTP_MEMBER_ID] FROM BASE)) AS BASE2
105
+ WHERE TRX_COUNT = 2
106
+
107
+ ```
108
+
109
+ ### 試したこと
110
+
111
+ WHERE条件などで IS NOT NULLなどの条件を記載しましたが、特に変わらず（といいうよりDB内にNULLは存在せず）。
112
+
113
+ ### 補足情報（FW/ツールのバージョンなど）
114
+
115
+ MS SQL Server