質問編集履歴

2

ソースコード若干修正(文言のみ)

2019/05/14 09:43

投稿

Ry22434
Ry22434

スコア15

test CHANGED
File without changes
test CHANGED
@@ -76,7 +76,7 @@
76
76
 
77
77
  FROM
78
78
 
79
- [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
79
+ [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
80
80
 
81
81
  WHERE
82
82
 
@@ -122,7 +122,7 @@
122
122
 
123
123
  ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
124
124
 
125
- FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
125
+ FROM [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
126
126
 
127
127
  WHERE [DTP_COMPANY] = '001' AND
128
128
 
@@ -148,7 +148,7 @@
148
148
 
149
149
  FROM
150
150
 
151
- [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
151
+ [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
152
152
 
153
153
  WHERE
154
154
 
@@ -194,7 +194,7 @@
194
194
 
195
195
  ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
196
196
 
197
- FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
197
+ FROM [DWH_PP].[dbo].[DAILY_TRX_PLMS_2018]
198
198
 
199
199
  WHERE [DTP_COMPANY] = '001' AND
200
200
 

1

詳細を記載

2019/05/14 09:43

投稿

Ry22434
Ry22434

スコア15

test CHANGED
File without changes
test CHANGED
@@ -1 +1,229 @@
1
1
  SQLに似たような性質(データ型:decimal)の列に条件を置き換えてSQLクエリを回したところ、大幅にパフォーマンスが異なり、困惑しております。
2
+
3
+ ### 前提・実現したいこと
4
+
5
+
6
+
7
+ クエリのパフォーマンスの悪化原因を明らかにし、パフォーマンスの大幅な改善を図りたい。
8
+
9
+
10
+
11
+
12
+
13
+
14
+
15
+ ### 発生している問題・エラーメッセージ
16
+
17
+
18
+
19
+ ```
20
+
21
+ SQLに似たような性質(データ型:decimal)の列に条件を置き換えてSQLクエリを回したところ、大幅にパフォーマンスが異なり、困惑しております。
22
+
23
+
24
+
25
+ DTP_POINTS
26
+
27
+ Decimal(16,0) null) - NOT ALLLOW NULL
28
+
29
+ 処理時間5分
30
+
31
+
32
+
33
+ DTP_POINTS_BASIC
34
+
35
+ Decimal(18,0)not null) - ALLLOW NULL
36
+
37
+ 処理時間2時間以上
38
+
39
+
40
+
41
+ ```
42
+
43
+
44
+
45
+ ### 該当のソースコード
46
+
47
+ 2つあります
48
+
49
+ ```SQL
50
+
51
+ 違いは指定した列の違いになります。指定列が[DTP_POINTS]と [DTP_POINTS_BASIC] だけが以下のコードで異なります。
52
+
53
+
54
+
55
+ DTP_POINTS
56
+
57
+ Decimal(16,0) null) - NOT ALLLOW NULL
58
+
59
+ 処理時間5分
60
+
61
+
62
+
63
+ DTP_POINTS_BASIC
64
+
65
+ Decimal(18,0)not null) - ALLLOW NULL
66
+
67
+ 処理時間2時間以上
68
+
69
+
70
+
71
+ **__処理時間5分__**
72
+
73
+
74
+
75
+ WITH BASE AS (SELECT [DTP_MEMBER_ID]
76
+
77
+ FROM
78
+
79
+ [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
80
+
81
+ WHERE
82
+
83
+ [DTP_COMPANY] = '001' AND
84
+
85
+ MONTH([DTP_TRX_DATE]) = '5' AND
86
+
87
+ YEAR([DTP_TRX_DATE]) = '2018' AND
88
+
89
+ [DTP_TRANSACTION_CODE] = 'POI')
90
+
91
+
92
+
93
+ SELECT
94
+
95
+ (SELECT COUNT(DISTINCT [DTP_MEMBER_ID]) FROM BASE) AS 'UNIQUE_MEMBER_PREVIOUS_MONTH',
96
+
97
+ COUNT(DISTINCT BASE2.[DTP_MEMBER_ID]) AS 'UNIQUE_MEMBER_NEXT_MONTH',
98
+
99
+ SUM(BASE2.[DTP_AMOUNT]) AS 'AMOUNT_AT_FIRST_TRX',
100
+
101
+ SUM(BASE2.[DTP_POINTS]) AS 'POINT_ISSUE_AT_FIRST_TRX',
102
+
103
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 500 ELSE BASE2.[DTP_POINTS] END) AS 'BONUS_POINT_2X',
104
+
105
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 750 ELSE FLOOR(BASE2.[DTP_POINTS] * 1.5) END) AS 'BONUS_POINT_3X',
106
+
107
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1000 ELSE FLOOR(BASE2.[DTP_POINTS] * 2) END) AS 'BONUS_POINT_4X',
108
+
109
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1250 ELSE FLOOR(BASE2.[DTP_POINTS] * 2.5) END) AS 'BONUS_POINT_5X'
110
+
111
+
112
+
113
+ FROM (
114
+
115
+ SELECT
116
+
117
+ [DTP_MEMBER_ID],
118
+
119
+ [DTP_AMOUNT],
120
+
121
+ [DTP_POINTS],
122
+
123
+ ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
124
+
125
+ FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
126
+
127
+ WHERE [DTP_COMPANY] = '001' AND
128
+
129
+ MONTH([DTP_TRX_DATE]) = '6' AND
130
+
131
+ YEAR([DTP_TRX_DATE]) = '2018' AND
132
+
133
+ [DTP_TRANSACTION_CODE] = 'POI' AND [DTP_MEMBER_ID] IN
134
+
135
+ (SELECT [DTP_MEMBER_ID] FROM BASE)) AS BASE2
136
+
137
+ WHERE TRX_COUNT = 2
138
+
139
+
140
+
141
+
142
+
143
+ **__処理時間2時間以上__**
144
+
145
+
146
+
147
+ WITH BASE AS (SELECT [DTP_MEMBER_ID]
148
+
149
+ FROM
150
+
151
+ [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
152
+
153
+ WHERE
154
+
155
+ [DTP_COMPANY] = '001' AND
156
+
157
+ MONTH([DTP_TRX_DATE]) = '5' AND
158
+
159
+ YEAR([DTP_TRX_DATE]) = '2018' AND
160
+
161
+ [DTP_TRANSACTION_CODE] = 'POI')
162
+
163
+
164
+
165
+ SELECT
166
+
167
+ (SELECT COUNT(DISTINCT [DTP_MEMBER_ID]) FROM BASE) AS 'UNIQUE_MEMBER_PREVIOUS_MONTH',
168
+
169
+ COUNT(DISTINCT BASE2.[DTP_MEMBER_ID]) AS 'UNIQUE_MEMBER_NEXT_MONTH',
170
+
171
+ SUM(BASE2.[DTP_AMOUNT]) AS 'AMOUNT_AT_FIRST_TRX',
172
+
173
+ SUM(BASE2.[DTP_POINTS_BASIC]) AS 'POINT_ISSUE_AT_FIRST_TRX',
174
+
175
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 500 ELSE BASE2.[DTP_POINTS_BASIC] END) AS 'BONUS_POINT_2X',
176
+
177
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 750 ELSE FLOOR(BASE2.[DTP_POINTS_BASIC] * 1.5) END) AS 'BONUS_POINT_3X',
178
+
179
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1000 ELSE FLOOR(BASE2.[DTP_POINTS_BASIC] * 2) END) AS 'BONUS_POINT_4X',
180
+
181
+ SUM(CASE WHEN BASE2.[DTP_AMOUNT] >= 100000 THEN 1250 ELSE FLOOR(BASE2.[DTP_POINTS_BASIC] * 2.5) END) AS 'BONUS_POINT_5X'
182
+
183
+
184
+
185
+ FROM (
186
+
187
+ SELECT
188
+
189
+ [DTP_MEMBER_ID],
190
+
191
+ [DTP_AMOUNT],
192
+
193
+ [DTP_POINTS_BASIC],
194
+
195
+ ROW_NUMBER() OVER (PARTITION BY [DTP_MEMBER_ID] ORDER BY [DTP_TRX_DATE] ASC) AS 'TRX_COUNT'
196
+
197
+ FROM [DWH_PONTA].[dbo].[DAILY_TRX_PLMS_2018]
198
+
199
+ WHERE [DTP_COMPANY] = '001' AND
200
+
201
+ MONTH([DTP_TRX_DATE]) = '6' AND
202
+
203
+ YEAR([DTP_TRX_DATE]) = '2018' AND
204
+
205
+ [DTP_TRANSACTION_CODE] = 'POI' AND [DTP_MEMBER_ID] IN
206
+
207
+ (SELECT [DTP_MEMBER_ID] FROM BASE)) AS BASE2
208
+
209
+ WHERE TRX_COUNT = 2
210
+
211
+
212
+
213
+ ```
214
+
215
+
216
+
217
+ ### 試したこと
218
+
219
+
220
+
221
+ WHERE条件などで IS NOT NULLなどの条件を記載しましたが、特に変わらず(といいうよりDB内にNULLは存在せず)。
222
+
223
+
224
+
225
+ ### 補足情報(FW/ツールのバージョンなど)
226
+
227
+
228
+
229
+ MS SQL Server