SQL Server Reducing script execution time

bq3bfh9z 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(159)

We have two Fact tables as below

Fact1
| DeviceKey | DateKey | TimeKey | CustomersIn |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 20230225 | 235612 | 2 |
| 1 | 20230226 | 94123 | 1 |
| 1 | 20230227 | 183408 | 3 |
| 1 | 20230228 | 175926 | 2 |
| 1 | 20230301 | 112319 | 1 |
| 2 | 20230225 | 224623 | 3 |
| 2 | 20230226 | 163452 | 2 |
| 3 | 20230225 | 45603 | 1 |
| 3 | 20230227 | 223451 | 2 |

Fact2

DeviceKey	EventDatekey	EventTimeKey	ErrorKey
1	20230225	81625	2
1	20230227	135409	2
1	20230301	124143	12
2	20230225	192324	2
2	20230226	180732	2
2	20230228	143919	12
2	20230301	71452	2
3	20230227	213451	2
3	20230228	212451	2

Display the EventDateKey and EventTimeKey of Fact 2 table, which is greater than the maximum Datekey and TimeKey for each device in Fact 1 table. If two days or 48 hours have passed from the maximum DateKey and TimeKey for each device in Fact 1 table. Right now, date and time: 2023/03/01 11:58

Result
| DeviceKey | EventDatekey | EventTimeKey | ErrorKey | maxdatetimeKey |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 2 | 20230226 | 180732 | 2 | 20230226163452 |
| 2 | 20230228 | 143919 | 12 | 20230226163452 |
| 2 | 20230301 | 71452 | 2 | 20230226163452 |
| 3 | 20230228 | 212451 | 2 | 20230227223451 |

I used this script

SELECT f2.*, s.maxdatetimekey
FROM Fact2 f2
INNER JOIN (
  SELECT DeviceKey, MAX(CONCAT(DateKey, FORMAT(TimeKey, '0#####'))) as maxdatetimeKey
  FROM FactTelemetry f1
  GROUP BY DeviceKey
) s ON s.DeviceKey = f2.DeviceKey AND CONCAT(EventDateKey, FORMAT(EventTimeKey, '0#####')) > maxdatetimeKey;
GO

But this is a simple example of our data and the amount of our data is very large, which makes the execution of this script take a long time, what do you suggest to solve this problem?

sql-server

来源：https://stackoverflow.com/questions/75642572/reducing-script-execution-time

1条答案

按热度按时间

wwtsj6pe1#

The first thing I would try would be to change your join and maxdatetimekey:

SELECT f2.*, s.maxdatetimekey
    FROM Fact2 f2
    INNER JOIN (
      SELECT DeviceKey, MAX(DateKey * 1000000 + TimeKey) as MaxDateTimeKey
--, MAX(CONCAT(DateKey, FORMAT(TimeKey, '0#####'))) as maxdatetimeKey

      FROM FactTelemetry f1
      GROUP BY DeviceKey
    ) s ON s.DeviceKey = f2.DeviceKey AND 
    EventDateKey * 1000000 + EventTimeKey > = maxdatetimeKey
    --CONCAT(EventDateKey, FORMAT(EventTimeKey, '0#####')) > maxdatetimeKey;
    ;

See if that helps.

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Reducing script execution time

1条答案

相关问题

热门标签

最新问答