Оптимизация медленных запросов SQL


Есть приложение с очень медленных запросов (перечислены ниже), и мне нужно оптимизировать, чтобы бежать быстрее. Правда в том, что я не знаю, с чего начать. Любой помочь? Спасибо!

SELECT DISTINCT v.order_ANC,
                v.order_ANC2,
                f.codi_arxiu,
                f.nom_arxiu,
                f.codi_grup,
                f.codi_fons_refe,
                f.codi_fons,
                f.nom_fons,
                f.any_ini,
                f.any_fi,
                f.cronologia,
                SUM (num_uni_imatges) AS num_uni_imatges,
                SUM (unitats_text) AS unitats_text,
                SUM (unitats_notext) AS unitats_notext
  FROM (  SELECT f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia,
                 SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
                 COUNT (u.codi_unitat) AS unitats_text,
                 0 AS unitats_notext
            FROM ianc_fons f
            JOIN ianc_unicat u ON ( f.codi_arxiu = u.codi_arxiu AND f.codi_fons = u.codi_fons )
           WHERE     1 = 1
                 AND u.anc_unicat_id IN
                        (SELECT DISTINCT u.anc_unicat_id
                           FROM ianc_unicat u
                          WHERE     1 = 1
                                AND u.codi_arxiu = '1'
                                AND u.codi_grup = 'ANC'
                                AND u.codi_fons = 1)
        GROUP BY f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia
        UNION
          SELECT f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia,
                 SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
                 0 AS unitats_text,
                 COUNT (u.nt1_unitat) AS unitats_notext
            FROM ianc_fons f
            JOIN ianci_unicats u ON (     f.codi_arxiu = u.nt1_codi_arxiu
                                      AND f.codi_fons = u.nt1_codi_fons )
           WHERE     1 = 1
                 AND u.nt1_id IN
                        (SELECT DISTINCT u.nt1_id
                           FROM ianci_unicats u
                          WHERE     1 = 1
                                AND u.nt1_codi_arxiu = '1'
                                AND u.codi_grup = 'ANC'
                                AND u.nt1_codi_fons = 1)
        GROUP BY f.codi_arxiu,
                 f.nom_arxiu,
                 f.codi_grup,
                 f.codi_fons_refe,
                 f.codi_fons,
                 f.nom_fons,
                 f.any_ini,
                 f.any_fi,
                 f.cronologia
       ) AS f
LEFT JOIN v_ianc_arxius_order v ON ( f.codi_arxiu=v.codi_arxiu )
GROUP BY v.order_ANC,v.order_ANC2,f.codi_arxiu, f.nom_arxiu, f.codi_grup, f.codi_fons_refe,
         f.codi_fons, f.nom_fons, f.any_ini,  f.any_fi, f.cronologia
ORDER BY v.order_ANC,v.order_ANC2,f.nom_arxiu, f.nom_fons


1175
0
задан 7 июня 2011 в 09:06 Источник Поделиться
Комментарии
2 ответа

Я подозреваю, что одним узким местом является группировка в подзапросы. Для каждой строки в ianc_fons таблице строки дублируются - один для каждого присоединиться к Чили таблица ianc_unicat. А потом они опять группируются.

Я бы попробовать другой подход и двигаться суммирование в составной подзапрос :

SELECT f.codi_arxiu,
f.nom_arxiu,
f.codi_grup,
f.codi_fons_refe,
f.codi_fons,
f.nom_fons,
f.any_ini,
f.any_fi,
f.cronologia,
child.num_uni_imatges,
child.unitats_text
FROM ianc_fons f
LEFT JOIN
( SELECT codi_arxiu, codi_fons,
SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
COUNT (u.codi_unitat) AS unitats_text
FROM ianc_unicat u
WHERE u.anc_unicat_id IN
(SELECT u.anc_unicat_id
FROM ianc_unicat u
WHERE 1 = 1
AND u.codi_arxiu = '1'
AND u.codi_grup = 'ANC'
AND u.codi_fons = 1)
GROUP BY codi_arxiu, codi_fons
) AS child ON f.codi_arxiu = child.codi_arxiu AND f.codi_fons = child.codi_fons

Обратите внимание, я убрал в отличие от выберите из ianc_unicat. Как это используется в фильтрации, различающиеся не надо. Использовать distinct-это всегда накладные расходы, поскольку обработчик запросов должен для сортировки записей, а затем сравнить их всех по одному, чтобы удалить дубликаты. Избежать, насколько это возможно.

Каждой стороне союза является выбор из той же таблицы, а затем суммированием различных дочерних записей.

Как суммирование в отдельную присоединился подзапроса, вы можете избавиться от Союза, и просто присоединиться к другой таблице в той же манере. Выберите теперь нужно подвести итоги из обоих соединяемых подзапросы.

SELECT f.codi_arxiu,
f.nom_arxiu,
f.codi_grup,
f.codi_fons_refe,
f.codi_fons,
f.nom_fons,
f.any_ini,
f.any_fi,
f.cronologia,
COALESCE(child.num_uni_imatges, 0) + COALESCE(child2.num_uni_imatges, 0) AS num_uni_imatges,
child.unitats_text,
child2.unitats_notext

Обратите внимание на использование КОАЛЕСЦИРУЮТ. Как вы оставили присоединения данные итоги сейчас, то есть шанс, что значения могут быть null. Null-это не то же, что и 0. Нуль + 4 не равно 4, то результат будет нулевой. Поэтому объединяться нужно сделать нулевые значения 0.

Теперь вам больше не понадобится внешняя выбора, как это было раньше сумма строк в рамках Союза. Вы можете избавиться от всех вместе. Строки объединили в для внешнего запроса может быть зарегистрирован в нашей основной запрос. Мы уже не лишние группировку здесь. Это дает нам запрос, аналогичный приведенному ниже :

SELECT f.codi_arxiu,
f.nom_arxiu,
f.codi_grup,
f.codi_fons_refe,
f.codi_fons,
f.nom_fons,
f.any_ini,
f.any_fi,
f.cronologia,
COALESCE(child.num_uni_imatges, 0) + COALESCE(child2.num_uni_imatges, 0) AS num_uni_imatges,
child.unitats_text,
child2.unitats_notext
FROM ianc_fons f
LEFT JOIN
( SELECT codi_arxiu, codi_fons,
SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
COUNT (u.codi_unitat) AS unitats_text
FROM ianc_unicat u
WHERE u.anc_unicat_id IN
(SELECT u.anc_unicat_id
FROM ianc_unicat u
WHERE 1 = 1
AND u.codi_arxiu = '1'
AND u.codi_grup = 'ANC'
AND u.codi_fons = 1)
GROUP BY codi_arxiu, codi_fons
) AS child ON f.codi_arxiu = child.codi_arxiu AND f.codi_fons = child.codi_fons

LEFT JOIN
( SELECT u.nt1_codi_arxiu, u.codi_fons,
SUM (CASE WHEN u.num_imatges > 0 THEN 1 ELSE 0 END) AS num_uni_imatges,
COUNT (u.nt1_unitat) AS unitats_notext
FROM ianci_unicats u
WHERE 1 = 1
AND u.nt1_id IN
(SELECT u.nt1_id
FROM ianci_unicats u
WHERE 1 = 1
AND u.nt1_codi_arxiu = '1'
AND u.codi_grup = 'ANC'
AND u.nt1_codi_fons = 1)
) AS child2 ON f.codi_arxiu = child2.nt1_codi_arxiu AND f.codi_fons = child2.nt1_codi_fons

LEFT JOIN v_ianc_arxius_order v ON ( f.codi_arxiu=v.codi_arxiu )
ORDER BY v.order_ANC,v.order_ANC2,f.nom_arxiu, f.nom_fons

2
ответ дан 7 июня 2011 в 12:06 Источник Поделиться

Оптимизации SQL-оператор, как правило, включает два этапа: понимание того, как СУБД была выбрана для выполнения запроса в индексном выражении подстановки и порядок соединения (так называемый план запроса), и выяснить способ, чтобы написать запрос, который получает тот же эффект, но то, что СУБД не находит лучшего плана запроса для.

Заставляя СУБД, чтобы выбрать лучший план запроса может быть также просто, как и принуждение другого выбора индекса (переопределение автоматической, но неоптимальный выбор индекса), добавление отсутствующего индекса или переопределение оптимизации выбора таблицы объединить заказ. Или вы могли бы найти совершенно другой способ выражения той же логической запроса, например, с помощью соединения, а не где столбец В (выбрать ...). Переопределение оптимизатора, как правило, требует специального синтаксиса, специфичные для SQL товара (SQL-сервер, в данном случае), которые должны быть разъяснены в где-то инструкции.

В вашем случае, ваш первый шаг должен быть, наверное, чтобы лучше вникнуть в то, почему этот запрос работает медленно. Вы можете сделать это в ограниченном объеме за счет исключения некоторых пунктов из запроса и увидев ее сразу быстрее; возможно, удалением заказа на пункте или удаление присоединиться положения.

Если это возможно, то более мощный вариант-использовать любые средства анализа представлены в СУБД, или доступны в качестве третьих лиц инструменты. Я не знаю конкретно в SQL Server, но я уверен, что есть инструмент, который позволит вам показать план запроса для любого заданного запроса, и в идеале дать некоторое представление, почему оптимизатор выбрал план запросов и другие возможности.

0
ответ дан 7 июня 2011 в 10:06 Источник Поделиться