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

#sql #sql-server #optimization #query-optimization

#sql #sql-сервер #оптимизация #оптимизация запросов

Вопрос:

Я относительный новичок в SQL, поэтому прошу прощения, если этот вопрос глупый! У меня есть следующий запрос, и он выполняется очень, очень медленно. В прошлый раз, когда я пробовал, это также создало такой большой файл журнала, что он заполнил диск файлом журнала и, таким образом, выдал ошибку перед завершением запроса. Пожалуйста, я бы хотел получить несколько простых советов о том, как это можно оптимизировать, или указания на любые полезные ресурсы, на которые я могу подписаться. Заранее большое спасибо!

 alter table Recalc_tmp1
add   [Fraction1] float,
      [Fraction2] float
go
update Recalc_tmp1
set [Fraction1] = case when b.[Numerator1] <> 0 then cast(b.[Numerator1] as float)/cast(b.[Denominator1] as float) else null end,
    [Fraction2] = case when b.[Numerator2] <> 0 then cast(b.[Numerator2] as float)/cast(b.[Denominator2] as float) else null end
from Recalc_tmp1 a
left join WriteDowns b
on a.ID1 = b.ID1 AND a.ID2 = b.ID2 AND a.ID3 = b.ID3

Я изменил имена некоторых полей, они более логичны в моих реальных данных!
WriteDowns Таблица относительно небольшая, но Recalc_tmp1 таблица огромная (десятки миллионов строк), т.Е. не все записи в Recalc_tmp1 таблице совпадают в WriteDowns таблице.
ID1 индексируется в обеих таблицах

Изначально у меня это было как select.. into запрос на объединение, но при сравнении этого и этого update варианта объединения с использованием планов выполнения select into вариант был в два раза медленнее.

1. Я думаю, вы хотели проверить, не равны ли Знаменатели1 и Знаменатели2 нулю. Кроме того, если ID1 является первичным ключом в обеих таблицах, почему вы также проверяете ID2 и ID3?

2. Зачем использовать левое соединение, если вы используете значения из b для заполнения a и в противном случае оставляете значение null. Измените его на внутреннее соединение, и ваш запрос будет выполняться намного быстрее с ожидаемыми результатами

3. @DavidN — Вы правы, столбцы ID1 на самом деле не являются первичными ключами. Но они индексируются в обеих таблицах.

Ответ №1:

Что-то вроде этого:

 ;with WriteDowns_cte as
(
select ID1, 
    case when [Denominator1] <> 0 then cast([Numerator1] as float)/cast([Denominator1] as float) end [Fraction1],
    case when [Denominator2] <> 0 then cast([Numerator2] as float)/cast([Denominator2] as float) end [Fraction2] 
from WriteDowns
)
update Recalc_tmp1
set [Fraction1] = b.[Fraction1],
    [Fraction2] = b.[Fraction2]
from Recalc_tmp1 a
join WriteDowns_cte b
on a.ID1 = b.ID1

Ответ №2:

Очевидная вещь — сделать ЛЕВОЕ соединение ВНУТРЕННИМ соединением. Если только вам не нужно обновить строки в Recall_tmp1, которые не имеют соответствующего соответствия в WriteDowns?
Это также является причиной столь значительного увеличения файла журнала. Для обновления каждой строки из десятков миллионов приходилось записывать в журнал.
Если вам действительно нужно обновить все строки, вам нужно будет найти способ выполнить несколько инструкций update, каждая из которых обновляет некоторую часть таблицы. Убедитесь, что за раз выполняется только одна транзакция, и убедитесь, что все транзакции зафиксированы перед запуском следующего пакета.