Блог

Какой был бы наиболее эффективный способ перебрать 25 000 записей и, основываясь на некоторой заранее написанной логике vb, которая никогда не изменится (уверен на 99%), обновить столбец результатов в таблице до значения 1, 2 или 3?

Производительность и надежность здесь наиболее важны. Это, скорее всего, будет вызываться через клиент-серверное приложение в сети, но было бы неплохо иметь возможность вызывать его из веб-приложения. Я думаю о 3 разных способах сделать это с помощью T-SQL, C #.

a. Напишите объект, который выполняет хранимую процедуру, получает 25 000 записей, используйте коллекцию foreach для просмотра каждой записи и, основываясь на некоторой логике c #, вызывайте объект в каждой записи, которая выполняет хранимую процедуру, для обновления этой строки. Это привело бы к вызову объекта 25 000 раз (и процедура, я предполагаю, просто повторно использовала бы план выполнения)

или

b. Напишите хранимую процедуру, которая получает 25 000 записей, используйте запрещенный курсор для просмотра каждой записи и, основываясь на некоторой логике T-SQL, обновите эту строку в этой хранимой процедуре.

или

ОБНОВЛЕНО: МОЕ РЕШЕНИЕ ЗАКЛЮЧАЕТСЯ В СЛЕДУЮЩЕМ, чего бы это ни стоило, я использую сохраненные вычисляемые столбцы и разбиваю цикл на более мелкие инструкции update для обновления столбца (все завернуто в транзакцию). Смотрите статью ниже. Я думаю, что это будет действительно быстро, по сравнению с циклом..

http://technet.microsoft.com/en-us/library/cc917696.aspx

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

Моим первым выбором было бы сделать все это на SQL, если бы я мог, т. Е. обновить xxx set col = 1 где (ваша логика здесь), обновить xxx set col = 2 где (логика) и т.д.

Если вам нужно выполнить логику в клиенте vb, либо в веб-приложении, либо на клиентском сервере, моим выбором было бы использовать datareader для прохождения по записям (извлекая только требуемые столбцы, а не всю строку) и либо выполнить обновление TSQL, либо хранимую процедуру для вызова обновления тех записей, которые необходимо обновить, по одной за раз).

datareader обеспечит вам наилучшую производительность; SP должен работать по крайней мере так же хорошо, если не лучше, чем обновление TSQL, (но, вероятно, ненамного).

РЕДАКТИРОВАТЬ: Избегайте курсоров на стороне сервера (почти) любой ценой…они настоящие свиньи.

Решение этой проблемы без ввода c # на самом деле является лучшим вариантом, если ключевым является производительность. Выполняйте свои запросы вне c #. Если это действительно необходимо, используйте DataReaders.

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

C. Используйте DataReader и обновляйте записи с помощью ExecuteNonQuery

Как насчет опции (C) Хранимой процедуры, которая обновляет таблицу, используя логику на основе наборов, а не курсор:

 ...
update x set col = f(x)
from   x
...
  

В зависимости от того, как работают обновления, у вас есть пара вариантов.

1. Создайте вычисляемый столбец, в котором сохраняются результаты. Таким образом, при изменении записи она будет обновляться в одном месте.

2. Вместо выполнения 25 000 запросов на обновление просто используйте sqlbulk load.

3. (и это мое предпочтение). Попросите ваше приложение отправить параметры на SQL Server для того, что нужно обновить. В этом случае я бы предпочел использовать статический курсор, поскольку это было бы немного быстрее, если бы одна запись не обязательно влияла на следующую.

Вы можете либо:

1. Используйте 3 отдельных обновления, предложенных @Andrew

2. Извлеките записи во временную таблицу и просматривайте их партиями по 1000 записей за раз в цикле WHILE для инструкции UPDATE (таким образом, 25 циклов / обновлений)

3. Или, если вы используете SQL Server 2008 (или новее) и алгоритм определения изменения сложный, вы можете собрать 25 000 строк в коллекцию на .Сетевая сторона и потоковая передача изменений обратно в Proc, который имеет табличный параметр и выполняет одно обновление. Вы можете найти пример этого на:
   http://www.sqlservercentral.com/articles/SQL Server 2008/66554/

В каждом случае вы хотите избежать 25 000 вызовов ОБНОВЛЕНИЯ.

У меня похожая ситуация. На самом деле, у меня > 10.000.000 записей. Бизнес-логика была довольно сложной, и существовал старый код, написанный исключительно на SQL. Менеджеры сказали мне, что со старым кодом на 1 000 000 записей уходит более 15 часов. С моим решением мне потребовалось буквально всего 5 минут! Я сделал это в цикле, который состоит из 3 шагов в итерации, и каждая итерация занимала одну партию записей:

1. Массовая загрузка счетов. Я не помню размер пакета, я думаю, что это было около нескольких тысяч.
2. Выполнение бизнес-логики для загруженных записей
3. Массовая вставка. Поскольку это было массово, это не могло быть обновлением. Таким образом, это было скопировано во временную таблицу с почти такой же структурой, что и исходная таблица, а затем обновлено по ключу в исходной таблице. Временная таблица опустошалась / удалялась при каждой массовой вставке. Это намного быстрее, чем стандартное обновление.