This query is taking too long to run. Is there a way to optimize this query? Please help.
WITH Table1 AS (
CREATE TABLE #temp WITH
(
DISTRIBUTION = HASH (DeviceidKey),
CLUSTERED COLUMNSTORE INDEX
) AS
SELECT DISTINCT DeviceidKey
FROM
(SELECT
DeviceidKey
,CONVERT(date, LoadDate) AS LoadDate
FROM
dbo.DeviceSample
WHERE
(LoadDate >= '2017-09-08T00:00:00') AND (LoadDate < '2017-09-15T00:00:00')
GROUP BY
DeviceidKey,CONVERT(date, LoadDate)
HAVING
(COUNT_BIG(ActionName) >= '1')
) ActionFrequency
GROUP BY
DeviceidKey,DATEADD(dd, -DAY(LoadDate) + 1, LoadDate)
HAVING
(COUNT_BIG(LoadDate) >= '2');
SELECT
COUNT_BIG(DeviceidKey) AS CountDeviceidKey
,DATEDIFF(MINUTE, '2017-10-07T00:00:00', LoadDate) / 1440 + 1 AS auto_grain
FROM
dbo.DeviceSample
WHERE
(LoadDate >= '2017-10-07T00:00:00') AND (LoadDate < '2017-10-14T00:00:00') AND ((dbo.DeviceSample.DeviceidKey IN (SELECT DeviceidKey FROM #temp)))
GROUP BY
DATEDIFF(MINUTE, '2017-10-07T00:00:00', LoadDate) / 1440 + 1
ORDER BY
auto_grain;
);
SELECT auto_grain, 100 * SUM(CountDeviceidKey) AS CountDeviceidKey
FROM Table1
GROUP BY auto_grain
ORDER BY auto_grain