Сделайте SQL-запрос общим и сделайте его быстрым

Question

Найдя правильный SQL-запрос для моих целей, я понял, что мой запрос медленный.

WITH temp_table (t_col_1, t_col_2, t_col_3) AS
(
    SELECT col_1 AS t_col_1, col_2 AS t_col_2, col_3 AS t_col_3
    FROM actual_table
    WHERE ID = 100 AND PID = 1245
)
SELECT t_col_1, t_col_2, t_col_3 
FROM temp_table AS t1 
WHERE t1.t_col_2 BETWEEN 1 AND 12541
  AND t1.t_col_1 = (SELECT max(t2.t_col_1)
                    FROM temp_table AS t2
                    WHERE t2.t_col_1 < 15147
                      AND t2.t_col_2 = t1.t_col_2) 
ORDER BY t1.t_col_2

Причина, по которой я использую запрос в этой форме, заключается в следующем:

1. Запрос SQL генерируется и используется в Matlab для извлечения данных.
2. В зависимости от идентификатора, столбцы col_1 и col_2 могут быть взаимозаменяемыми, поэтому t_col_1 = col_2 и t_col_2 = col_1. В этом случае скрипт Matlab заменяет col_1 AS t_col_2 и col_2 AS t_col_1.

Есть ли элегантный способ ускорить запрос?

Заранее спасибо.

Answer 1

Ответ полностью зависит от вашего оптимизатора запросов и статистики базы данных, которая, в свою очередь, будет отличаться в зависимости от вашего выбора базы данных.

1. Получить QEP - план выполнения запроса
2. Посмотрите, где план медленный
3. Оптимизируйте запрос и / или добавьте статистику базы данных и / или добавьте необходимые индексы

Вы можете попытаться настроить запрос и, возможно, вам повезет, но правильный подход - понять план запроса.

Например, у вас нет возможности узнать, является ли значение 'max' медленным, или может ли current_table содержать миллиард строк без индекса по ID и PID.

Answer 2

Ваша проблема в том, что запрос относительно максимального значения t_col_1 выполняется для каждой строки, так как ваш основной запрос проверяет WHERE критерии. Вместо этого вы можете генерировать max(t2.t_col_1) значение из подзапроса, который выполняется один раз и затем использует эту переменную в ваших критериях, например так:

SELECT PID, t1.t_col_1, t1.t_col_2, t1.t_col_3
FROM
    (SELECT PID, t_col_1, t_col_2, t_col_3, max(t2.t_col_1) AS t_col_1_max
    FROM temp_table
    GROUP BY PID, t_col_1, t_col_2, t_col_3) 
    as t1
WHERE 
    (t1.t_col_2 BETWEEN 1 AND 12541)
    AND t1.t_col_1 < 15147
    AND t1.t_col_1 = t1.t_col_1_max
ORDER BY t1.t_col_2

Ваш код для создания временной таблицы выглядит хорошо.