sql-serverssasbusiness-intelligencessas-tabular

Reducing script execution time

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?

Solution

  • 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(CAST(DateKey as bigint) * 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 
    CAST(EventDateKey as bigint) * 1000000 + EventTimeKey > = maxdatetimeKey
    --CONCAT(EventDateKey, FORMAT(EventTimeKey, '0#####')) > maxdatetimeKey;
    ;

    See if that helps.