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

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

关注(0)|答案(2)|浏览(150)

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;

sql-server

来源：https://stackoverflow.com/questions/75625084/why-does-my-query-take-so-long-and-how-can-i-optimize-it

2条答案

按热度按时间

t98cgbkg1#

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.

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

wrrgggsh2#

There are a number of efficiencies you can make:

You can use window functions to remove the need for subqueries.
You can combine all of them into a single query scope, no need for joins.
Always use a non-ambiguous date format.

The other answer also has some efficiency issues:

The temp table is not needed, you can do it one query.
Row numbering in opposite directions is inefficient. We can use LEAD instead for the opposing direction, in order to keep the sorting the same.

SELECT
  MIN(h.Value) AS MIN,
  MAX(h.Value) AS MAX,
  AVG(h.Value) AS AVERAGE, 
  STDEV(h.Value) AS STD_DEV, 
  MIN(CASE WHEN h.rn = 1 THEN h.wwUnit END) AS wwUnit,
  MIN(CASE WHEN h.rn = 1 THEN h.DateTime END) AS TIME_AT_MIN,
  MIN(CASE WHEN h.NextValue IS NULL THEN h.DateTime END) AS TIME_AT_MAX,
  MIN(h.Datetime) AS START_RANGE,
  MAX(h.Datetime) AS END_RANGE
FROM (
    SELECT
      h.*,
      ROW_NUMBER() OVER (ORDER BY h.Value) AS rn,
      LEAD(h.Value) OVER (ORDER BY h.Value) AS NextValue
    FROM dbo.History h
    WHERE TagName = 'N_AC001_01_AC_470_01.Data_2' 
        AND h.DateTime >= '2023-02-15 10:11:37' 
        AND h.DateTime <= '2023-03-01 10:11:37'
        AND h.wwRetrievalMode = 'Cyclic'  
        AND h.wwQualityRule = 'optimistic'
) h;

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

我来回答

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

2条答案

相关问题

热门标签

最新问答