Ускорение запроса на разделение в древней версии SQL Server

Question

Настройка

У меня проблемы с производительностью и концептуальные проблемы с получением правильного запроса на SQL Server 7, работающем на двухъядерной машине с оперативной памятью 2 ГГц + 2 ГБ - нет шансов получить это, как вы могли ожидать:-/.

Ситуация

Я работаю с устаревшей базой данных, и мне нужно добывать данные, чтобы получить различные идеи. У меня есть all_stats таблица, которая содержит все статистические данные для вещи в определенном контексте. Эти контексты сгруппированы с помощью group_contexts Таблица. Упрощенная схема:

+--------------------------------------------------------------------+
| thingies                                                           |
+--------------------------------------------------------------------|
| id          | INT PRIMARY KEY IDENTITY(1,1)                        |
+--------------------------------------------------------------------+

+--------------------------------------------------------------------+
| all_stats                                                          |
+--------------------------------------------------------------------+
| id          | INT PRIMARY KEY IDENTITY(1,1)                        |
| context_id  | INT FOREIGN KEY REFERENCES contexts(id)              |
| value       | FLOAT NULL                                           |
| some_date   | DATETIME NOT NULL                                    |
| thingy_id   | INT NOT NULL FOREIGN KEY REFERENCES thingies(id)     |
+--------------------------------------------------------------------+

+--------------------------------------------------------------------+
| group_contexts                                                     |
+--------------------------------------------------------------------|
| id          | INT PRIMARY KEY IDENTITY(1,1)                        |
| group_id    | INT NOT NULL FOREIGN KEY REFERENCES groups(group_id) |
| context_id  | INT NOT NULL FOREIGN KEY REFERENCES contexts(id)     |
+--------------------------------------------------------------------+

+--------------------------------------------------------------------+
| contexts                                                           |
+--------------------------------------------------------------------+
| id          | INT PRIMARY KEY IDENTITY(1,1)                        |
+--------------------------------------------------------------------+

+--------------------------------------------------------------------+
| groups                                                             |
+--------------------------------------------------------------------+
| group_id    | INT PRIMARY KEY IDENTITY(1,1)                        |
+--------------------------------------------------------------------+

Эта проблема

Задача для заданного набора вещей найти и объединить 3 самых последнихall_stats.some_date) характеристики вещи для всех групп, для которых у нее есть статистика. Я знаю, это звучит легко, но я не могу понять, как сделать это правильно в SQL - я не совсем вундеркинд.

Мое плохое решение (нет, это действительно плохо...)

Мое решение сейчас состоит в том, чтобы заполнить временную таблицу со всеми необходимыми данными и UNION ALLВ данных мне нужно:

-- Before I'm building this SQL I retrieve the relevant groups
-- for being able to build the `UNION ALL`s at the bottom.
-- I also retrieve the thingies that are relevant in this context
-- beforehand and include their ids as a comma separated list -
-- I said it would be awfull ...

-- Creating the temp table holding all stats data rows
-- for a thingy in a specific group
CREATE TABLE #stats
(id INT PRIMARY KEY IDENTITY(1,1),
 group_id INT NOT NULL,
 thingy_id INT NOT NULL,
 value FLOAT NOT NULL,
 some_date DATETIME NOT NULL)

-- Filling the temp table
INSERT INTO #stats(group_id,thingy_id,value,some_date)
SELECT filtered.group_id, filtered.thingy_id, filtered.some_date, filtered.value
FROM
   (SELECT joined.group_id,joined.thingy_id,joined.value,joined.some_date
    FROM
       (SELECT groups.group_id,data.value,data.thingy_id,data.some_date
        FROM
            -- Getting the groups associated with the contexts
            -- of all the stats available
           (SELECT DISTINCT context.group_id
            FROM all_stats AS stat
            INNER JOIN group_contexts AS groupcontext
                ON groupcontext.context_id = stat.context_id
        ) AS groups
        INNER JOIN
            -- Joining the available groups with the actual
            -- stat data of the group for a thingy
           (SELECT context.group_id,stat.value,stat.some_date,stat.thingy_id
            FROM all_stats AS stat
            INNER JOIN group_contexts AS groupcontext
                ON groupcontext.context_id = stat.context_id
            WHERE stat.value IS NOT NULL
              AND stat.value >= 0) AS data
        ON data.group_id = groups.group_id) AS joined
    ) AS filtered
-- I already have the thingies beforehand but if it would be possible
-- to include/query for them in another way that'd be OK by me
WHERE filtered.thingy_id in (/* somewhere around 10000 thingies are available */)

-- Now I'm building the `UNION ALL`s for each thingy as well as
-- the group the stat of the thingy belongs to

-- thingy 42 {
-- Getting the average of the most recent 3 stat items
-- for a thingy with id 42 in group 982
SELECT x.group_id,x.thingy_id,AVG(x.value)
FROM
   (SELECT TOP 3 s.group_id,s.thingy_id,s.value,s.some_date
    FROM #stats AS s
    WHERE s.group_id = 982
      AND s.thingy_id = 42
    ORDER BY s.some_date DESC) AS x
GROUP BY x.group_id,x.thingy_id
HAVING COUNT(*) >= 3

UNION ALL

-- Getting the average of the most recent 3 stat items
-- for a thingy with id 42 in group 314159
SELECT x.group_id,x.thingy_id,AVG(x.value)
FROM
   (SELECT TOP 3 s.group_id,s.thingy_id,s.value,s.some_date
    FROM #stats AS s
    WHERE s.group_id = 314159
      AND s.thingy_id = 42
    ORDER BY s.some_date DESC) AS x
GROUP BY x.group_id,x.thingy_id
HAVING COUNT(*) >= 3
-- }

UNION ALL

-- thingy 21 {
-- Getting the average of the most recent 3 stat items
-- for a thingy with id 21 in group 982
/* you get the idea */

Это работает - медленно, но работает - для небольших наборов данных (например, 100 штук с 10 прикрепленными статистиками каждый), но проблемная область, с которой он должен работать, состоит из 10000+ штук с потенциально сотнями статистических данных на штуку. В качестве примечания: сгенерированный SQL-запрос смехотворно велик: довольно маленький запрос включает, например, 350 штук, которые имеют данные в 3 контекстных группах, и он насчитывает более 250 000 отформатированных строк SQL - выполнение за 5 минут.

Так что, если у кого-то есть идея, как решить эту проблему, я очень, очень признателен за вашу помощь:-).

Answer 1

В вашей древней версии SQL Server вам нужно использовать скалярный подзапрос в старом стиле, чтобы получить последние три строки для всех штук в одном запросе:-)

SELECT x.group_id,x.thingy_id,AVG(x.value)
FROM
 (
   SELECT s.group_id,s.thingy_id,s.value
   FROM #stats AS s
   where (select count(*) from #stats as s2
          where s.group_id = s2.group_id
            and s.thingy_id = s2.thingy_id 
            and s.some_date <= s2.some_date
         ) <= 3
 ) AS x
GROUP BY x.group_id,x.thingy_id
HAVING COUNT(*) >= 3

Чтобы повысить производительность, вам нужно добавить кластерный индекс, вероятно, (group_id,thingy_id,some_date desc,value) к #stats Таблица.

Если group_id,thingy_id,some_date уникален, вы должны удалить ненужное ID столбец, в противном случае order by group_id,thingy_id,some_date desc в течение Insert/Select в #stats и использовать ID вместо some_date для нахождения последних трех строк.