Известно, что бывает довольно сложно правильно написать сложный запрос, чтобы он работал хорошо и быстро. Но, оказывается, очень часто неправильно пишутся и достаточно простые вещи типа update каких-то полей одной таблицы — даже без каких-либо joins.
Вот характерный пример:
update tableA
set fieldA = case when fieldB = 'ONE' then 1 else 0 end
from tableA
where fieldA = 0
(это синтаксис MSSQL, в Oracle нет from)
Спрашивается — что здесь неправильно?
Начнем с того, что команда update — весьма «дорогая» операция. Во-первых, все изменения пишутся в лог. Во-вторых, достаточно часто вместо update выполняется delete/insert, потому что либо на таблице существует триггер, либо меняется длина записи, и нужно переместить запись в другое место. Еще бывают дополнительные неприятности, если изменяемые поля входят в какие-нибудь индексы — происходит перестроение индексов.
Итак, проанализировав это выражение, видим, что выполняются лишние update, а именно, для условий, когда fieldB != ‘ONE’
То есть, переписав запрос
update tableA
set fieldA = 1
from tableA
where fieldA = 0 and fieldB = 'ONE'
мы сокращаем время выполнения этой операции.
Еще есть часто встречающаяся вариация такого update —
update tableA
set fieldA = coalesce((select sum(fieldB) from tableB where tableA.fieldC = tableB.fieldC), 0)
from tableA
То есть, происходит update всех записей таблицы tableA безотносительно наличия записей в tableB. Если практически для каждой записи в tableA есть записи в tableB, то такой запрос имеет право на существование. Но очень часто приходится видеть, что в tableB гораздо меньше записей, чем в tableA.
В таких случаях этот запрос можно (и нужно) переписать (заполнение fieldA = 0 можно выполнить, например, во время операции insert или вообще установить его, как значение по умолчанию — разумеется, я не рассматриваю те случаи, когда такой update действительно нужно сделать для всех записей)
Переписанный запрос может выглядеть, например, так
update tableA
set fieldA = (select sum(fieldB) from tableB where tableA.fieldC = tableB.fieldC)
from tableA
where exists (select 1 from tableB where tableA.fieldC = tableB.fieldC)
Никакой дополнительной работы сервер БД не производит — ему все равно нужно проверить наличие записей в таблице tableB для каждой записи в tableA, зато количество update может заметно сократиться.
Запрос еще можно переписать и вот таким образом:
update tableA
set fieldA = B.sumFieldB
from tableA, (select sum(fieldB) sumFieldB, fieldC from tableB group by fieldC) B
where tableA.fieldC = B.fieldC
© Михаил Герштейн, FLEXTERA BI