Настройка производительности SQL-запросов

UPDATE nas_backup
SET fiber_serviceability_class = '0', 
last_updated_ts = CURRENT_TIMESTAMP 
WHERE location_id IN ( 
SELECT location_id 
FROM ( 
WITH distinct_locs AS ( 
    SELECT location_id, boundary_type 
    FROM ( 
        SELECT location_id, boundary_type 
        FROM nc
        WHERE technology_type = 'Fibre' 
    ) 
    GROUP BY location_id, boundary_type 
    HAVING COUNT( * ) = 1
)
SELECT nas.location_id
FROM distinct_locs, nas_backup nas
WHERE distinct_locs.location_id = nas.location_id
AND distinct_locs.boundary_type = 'FSA'
GROUP BY nas.location_id
)
);

Может ли кто-нибудь предложить способ оптимизации запроса. Сейчас это занимает более 5 минут.

Таблица nc содержит 16 миллионов записей, а таблица nas_backup — 2 миллиона записей.


sql sql-tuning
person Wamglind Carmasaic    schedule 12.06.2014    source источник
comment
Вы не упомянули никаких индексов, поэтому их добавление было бы хорошим началом.   -  person Anthony Grist    schedule 12.06.2014
comment
Имеются индексы для соответствующих столбцов. Я просто хочу предоставить более оптимизированную версию запроса.   -  person Wamglind Carmasaic    schedule 12.06.2014
comment
Если вы можете предоставить фактический план выполнения, мы сможем определить, где находятся узкие места?   -  person sarin    schedule 12.06.2014
comment
Просто чтобы убедиться, что я понимаю вашу цель: вы хотите обновить все записи в nas_backup, где тип технологии местоположения — «Волокно», а тип границы — «FSA». Есть что еще проверить?   -  person Pred    schedule 12.06.2014

Ответы (2)


arrow_upward
0
arrow_downward

EXISTS может вам немного помочь. Попробуйте:

UPDATE nas_backup
SET fiber_serviceability_class = '0', 
last_updated_ts = CURRENT_TIMESTAMP 
-- Remove the IN condition and check only that at least one row exists
WHERE EXISTS ( 
SELECT location_id 
FROM ( 
    WITH distinct_locs AS ( 
        SELECT location_id, boundary_type 
        FROM ( 
            SELECT location_id, boundary_type 
            FROM nc
            WHERE technology_type = 'Fibre'
        ) 
        GROUP BY location_id, boundary_type 
        HAVING COUNT( * ) = 1
    )
    SELECT
        nas.location_id
    FROM
        distinct_locs
    WHERE
        distinct_locs.boundary_type = 'FSA'
        -- This is the new line
        AND distinct_locs.location_id = nas_backup.location_id
    GROUP BY
        nas.location_id
    )
);

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

В следующий раз, пожалуйста, сообщите нам производителя и версию используемой СУБД.

person Pred    schedule 12.06.2014
comment
Спасибо Пред. Но мне интересно, почему вы включили новую строку в конце. Это условие уже включено в исходный запрос. - person Wamglind Carmasaic; 12.06.2014
comment
эта строка должна ссылаться на таблицу, которую вы обновляете. Вот почему я включил (и именно поэтому я использовал не псевдоним nas, а имя таблицы. Это условие связывает подзапрос с исходной таблицей. - person Pred; 12.06.2014
comment
Я только что отредактировал ответ. Я удалил «присоединение» из последнего выбора. - person Pred; 12.06.2014

arrow_upward
0
arrow_downward

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

Все фильтры, которые у вас есть, применяются к части местоположения, поэтому перенесите ее в подзапрос, чтобы уменьшить набор результатов:

SELECT location_id, boundary_type 
FROM nc 
WHERE technology_type = 'Fibre' AND nc.boundary_type='FSA'
GROUP BY location_id, boundary_type 
HAVING COUNT(*) = 1

Затем используйте синтаксис JOIN вместо неявного соединения:

UPDATE nas fiber_serviceability_class = '0', last_updated_ts = CURRENT_TIMESTAMP 
FROM nas_backup nas
    JOIN (SELECT location_id, boundary_type 
            FROM nc 
            WHERE technology_type = 'Fibre' AND nc.boundary_type='FSA'
            GROUP BY location_id, boundary_type 
            HAVING COUNT(*) = 1) loc ON loc.location_id=nas.location_id

Я не знаю, есть ли особая причина для подзапроса с предложением HAVING. Не зная вашей структуры и данных, это всего лишь предположение, но, возможно, вам это не нужно. Это очень простой запрос, который переводится как «Обновить каждый nas_backup, где тип технологии — Fiber, а тип границы — FSA одновременно».

UPDATE nas fiber_serviceability_class = '0', last_updated_ts = CURRENT_TIMESTAMP 
FROM nas_backup nas
    JOIN nc ON nas.location_id=nc.location_id
WHERE nc.technology_type='Fibre'
    AND nc.boundary_type='FSA'
person Martin K.    schedule 12.06.2014