Если есть возможность, лучше удалять данные полностью, а потом добавлять новые при помощи
SqlBulkCopy.
Таким образом можно будет большие, я бы даже сказал огромные, объемы данных помещать в базу за короткий промежуток времени. Лучше всего не все сразу, а частями. Например, если
5 000 000 записей, то пачкой
10 000, или по
1 000. Зависит от объема данных и вероятности возникновения ошибок в процессе переноса. Если там простые данные, типа чисел, то можно их одним махом залить в базу. Если пачка будет слишком большой, серверу может памяти не хватить на её обработку.
Быстро удалить данные можно при помощи инструкции
TRUNCATE TABLE [имя таблицы], но не всегда просто, зависит от связей.
Организовать обновление будет сложнее. Лучше всего выбирать пачку данных, обрабатывать и отправлять обратно на сервер. Размер пачки необходимо определять в зависимости от ресурсов сервера и объема данных в пачке.
Запросы при обновлении должны быть максимально простыми. Можно использовать
XML, но тоже очень простой и небольших объемов, а в идеале обойтись без этого. Чем все будет проще, тем быстрее будет работать. Циклы и курсоры в запросах
стараться не использовать.
Проверку наличия данных можно реализовать на стороне приложения, а не базы. Практика показывает, что это работает быстрее. Перед обновлением можно получить только идентификаторы данных (в
DataTable). Далее проверять, есть необходимый идентификатор в
DataTable или нет. Чтобы было проще, можно обновлять все данные, если идентификатор будет найден. А если нет, то добавлять данные. Это позволит не делать
EXISTS на стороне
SQL Server и обновление можно будет выполнить пачкой. Если использовать
XML, то примерно так это может выглядеть:
-- временная таблица
-- в моем примере всего два поля id и value
CREATE TABLE #tmp
(
num int primary key identity,
id bigint,
value nvarchar(max)
);
-- структура xml:
-- <items>
-- <item id="123">значение</item>
-- <item id="456">значение</item>
-- </items>
-- переносим xml в таблицу
INSERT INTO #tmp
SELECT
ISNULL(n.value('@id', 'bigint'), 0), -- идентификатор существующих данных
n.value('.', 'nvarchar(max)') -- значение данных
FROM @xml.nodes('/items/item') AS node(n);
-- обновляем записи в таблице [table], у которых есть идентификатор
UPDATE a SET value = b.value
FROM table AS a
INNER JOIN #tmp AS b ON a.id = b.id
WHERE b.id <> 0;
-- добавляем записи, у которых нет идентификатора
INSERT INTO table (value)
SELECT value FROM #tmp AS b WHERE b.id = 0;
-- удаляем временную таблицу
DROP TABLE #tmp;
Если есть возможность, лучше обойтись без
XML,
JOIN-ов и всяких
ISNULL.
Базу необходимо правильно настроить. Добавить индексы, где будет уместно. Но не переборщить и учитывать, что они (индексы) могут фрагментироваться и сильно замедлять работу базы. Про фрагментацию самой базы, тоже не стоит забывать. Если будете удалять большие объемы данных, то не забывайте выполнять резервное копирование и сжатие базы.
Соединения с базой лучше использовать одноразовые. Т.е. открыл, выполнил запрос, закрыл и так далее. Если использовать одно соединение, то со временем скорость выполнения запросов будет существенно снижаться.
Универсального решения подобных задач нет, но это примерно то, от чего можно отталкиваться.
