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