Why does my query take so long and how can I optimize it?

March 3, 2023

I am trying to combine the results of all these separated (sub) queries into one single result. I have really basic knowledge of SQL.

I am able to get a result, but it takes a massive 6 seconds.

Edit:
Query plan:
https://www.brentozar.com/pastetheplan/?id=SJvCySy12

What can I do with my query to make it much much faster?

   WITH t AS (
    SELECT DateTime,Value,wwUnit 
    FROM [Runtime].[dbo].[History]
    WHERE TagName = 'N_AC001_01_AC_470_01.Data_2' 
        AND DateTime >= '2/15/2023 10:11:37 AM' 
        AND DateTime <= '3/1/2023 10:11:37 AM' 
        AND wwRetrievalMode = 'Cyclic'  
        AND wwQualityRule='optimistic'
),
min_max_avg AS (
    SELECT  
        Min(Value) AS MIN,
        Max(Value) AS MAX,
        AVG(Value) AS AVERAGE, 
        STDEV(Value) AS STD_DEV 
    FROM t
),
eng_unit AS (
    SELECT TOP 1 wwUnit 
    FROM t  
),
min_value_time AS (
    SELECT TOP 1 DateTime TIME_AT_MIN
    FROM t 
    WHERE Value = (SELECT MIN(Value) FROM t)
),
max_value_time AS (
    SELECT TOP 1 DateTime AS TIME_AT_MAX
    FROM t 
    WHERE Value = (SELECT MAX(Value) FROM t)
),
first_logged_value AS (
    SELECT TOP 1 Datetime as START_RANGE
    FROM t
),
last_logged_value AS (
    SELECT TOP 1 DateTime AS END_RANGE 
    FROM t 
    ORDER BY DateTime DESC
)
SELECT *
FROM min_max_avg, eng_unit, min_value_time, max_value_time, first_logged_value, last_logged_value;

>Solution :

Try this:

SELECT DateTime,Value,wwUnit,
    ROW_NUMBER() OVER (ORDER BY Value ASC) AS min_value,
    ROW_NUMBER() OVER (ORDER BY Value DESC) AS max_value
INTO #temp
FROM [Runtime].[dbo].[History]
WHERE TagName = 'N_AC001_01_AC_470_01.Data_2' 
    AND DateTime >= '2/15/2023 10:11:37 AM' 
    AND DateTime <= '3/1/2023 10:11:37 AM' 
    AND wwRetrievalMode = 'Cyclic'  
    AND wwQualityRule='optimistic'

SELECT Min(Value) AS MIN,
       Max(Value) AS MAX,
       AVG(Value) AS AVERAGE, 
       STDEV(Value) AS STD_DEV,
       Max(wwUnit) AS wwUnit,
       --
       MIN(CASE WHEN min_value = 1 THEN DateTime END) AS TIME_AT_MIN,
       MAX(CASE WHEN max_value = 1 THEN DateTime END) AS TIME_AT_MAX,
       --
       Min(Datetime) AS first_logged_value,
       Max(Datetime) AS last_logged_value
FROM #temp;

The idea is save the needed data in temporary table. At the same time, mark the rows with min and max values. Then in later query calculated all the needed values without sub-queries.