SQL UPDATE WHERE IN (List) или UPDATE каждый отдельно?

В последнее время я делаю все возможное, чтобы найти лучший способ запуска определенных запросов в SQL, который потенциально может быть выполнен несколькими различными способами. Среди моих исследований я встретил довольно много ненависти к концепции WHERE IN из-за присущей неэффективности в том, как она работает.

например: WHERE Col IN (val1, val2, val3)

В моем текущем проекте я делаю UPDATE на большом наборе данных, и мне интересно, какая из следующих функций более эффективна: (или существует ли лучший вариант)

UPDATE table1 SET somecolumn = 'someVal' WHERE ID IN (id1, id2, id3 ....);

В приведенном выше списке идентификатор может содержать до 1,5 тыс. идентификаторов.

Цитирование по всему идентификатору в коде и выполнение для каждого из следующих операторов:

UPDATE table1 SET somecolumn = 'someVal' WHERE ID = 'theID';

Мне кажется, что более логично, что первый будет работать лучше/быстрее, потому что меньше запросов для запуска. Тем не менее, я не на 100% знаком с входом и выходом из SQL и как работает очередь запросов.

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

Общая информация в случае, если это помогает, я использую Microsoft SQL Server 2014, а основным языком разработки является С#.

Любая помощь очень ценится.

EDIT:

Вариант 3:

UPDATE table1 SET somecolumn = 'someVal' WHERE ID IN (SELECT ID FROM @definedTable);

В приведенном выше примере @definedTable представляет собой SQL 'User Defined Table Type', где внутренние данные передаются в хранимую процедуру как (в С#) тип SqlDbType.Structured

Люди спрашивают, как приходит идентификатор: ID находятся в List<string> в коде и используются для других вещей в коде, а затем отправляются в хранимую процедуру. В настоящее время идентификатор входит в хранимую процедуру как "Пользовательский тип таблицы" с одним столбцом (ID).

Я думал, что иметь их в таблице может быть лучше, чем код конкатенировать массивную строку и просто плюнуть в SP как переменную, которая выглядит как id1, id2, id3, id4 и т.д.

Ответ 1

Я использую ваш третий вариант, и он отлично работает.

Моя хранимая процедура имеет параметр таблицы. См. Также Использовать табличные параметры.

В процедуре есть одно утверждение, нет циклов, как вы сказали:

UPDATE table1 SET somecolumn = 'someVal' WHERE ID IN (SELECT ID FROM @definedTable);

Лучше вызывать процедуру один раз, чем 1500 раз. Лучше иметь одну транзакцию, чем 1500 транзакций.

Если число строк в @definedTable идет выше, скажем, 10K, я бы подумал о его разбиении пакетами в 10K.

Ваш первый вариант в порядке для нескольких значений в предложении IN, но когда вы дойдете до действительно высоких чисел (60K +), вы увидите что-то вроде этого, как показано в this ответить:

Msg 8623, уровень 16, состояние 1, строка 1 Процессор запросов закончился внутренние ресурсы и не могли составить план запроса. Это редкость события и ожидаются только для чрезвычайно сложных запросов или запросов, которые ссылаются на очень большое количество таблиц или разделов. Пожалуйста, упростите запрос. Если вы считаете, что получили это сообщение по ошибке, обратитесь в Службу поддержки клиентов за дополнительной информацией.

Ответ 2

Ваш первый или третий вариант - лучший способ. Для любого из них вам нужен индекс на table1(id).

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

Ответ 3

Вы должны НЕ использовать цикл и отправлять весь новый оператор SQL для каждого идентификатора. В этом случае SQL-движок должен перекомпилировать инструкцию SQL и составить план выполнения и т.д. Каждый раз.

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

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

EDIT:

Pseudocode Perl, как описано ниже:

#!/usr/bin/perl
use DBI;
$dbh = DBI->connect('dbi:Oracle:MY_DB', 'scott', 'tiger', { RaiseError => 1, PrintError =>1, AutoCommit => 0 });
$sth = $dbh->prepare ("UPDATE table1 SET somecolumn = ? WHERE id = ?");
foreach $tuple (@updatetuples) {
    $sth->execute($$tuple[1], $$tuple[0]);
}
$dbh->commit;
$sth->finish;
$dbh->disconnect;
exit (0);