@tarasverq

Как правильно работать с большой базой через C# + MsSQLL?

Доброго времени суток!
Есть база, в которую надо заносить большой объем данных.
В сутки примерно 5 миллионов записей. Перед тем как добавить в базу данные, сначала проверяем что их там нет, иначе редактируем существующую запись.

Сначала использовал визуальный генератор из студии, покидал таблички, нашел примеры кода в интернете.
Было так:

usersTableAdapter usersTA = new usersTableAdapter();
var usersTable = new users.usersDataTable();
usersTA.Fill(usersTable);


Соответственно, вся база висела в оперативке, а потом обновлялась.
После того, как занесли в базу 10 миллионов записей, старт программы на этом этапе стал очень долгим, также забивалась вся оперативка и файл подкачки. Работа программы тоже была достаточно медленной. На обработку 5000 данных уходило около минуты.

После этого я подумал, что стоит переписать все это безобразие в хранимую процедуру. Переписал. В том же визуальном генераторе добавил хранимую процедуру, и выкинул все использования usersTable из кода. Использовал только TableAdapter. Оперативка перестала забиваться, но время обработки только увеличилось до минуты и сорока секунд на 5000 данных.

Текущий код хранимой процедуры:
IF (NOT EXISTS(SELECT * FROM [dbo].[users] WHERE [id] = @id)) 
BEGIN 
    INSERT INTO [dbo].[users]
           (...)
     VALUES
           (...);
    SET	@result = 1;
END 
ELSE 
BEGIN 
    UPDATE [dbo].[users]
   SET ...
 WHERE [id] = @id
	SET	@result = 0;
END


Мой вопрос: как сделать обработку данных максимально быстрой? Какие компоненты лучше использовать? Возложить ли проверку наличия данных на базу, или же сделать это как-то иначе?
  • Вопрос задан
  • 6186 просмотров
Решения вопроса 2
NYMEZIDE
@NYMEZIDE
резюме - ivanfilatov.ru
Как вы передаете записи, которых 5000, в хранимку? по 1 шт ? Тогда время будет действительно увеличиваться.

Вариантов 2:
1. Конвертируйте все данные (все 5000 записей) в XML - передавайте XML в хранимку, которая сделает разбор XML и добавление/редактирование данных.

2. Сделайте загрузку всех записей (INSERT) во временную таблицу в БД. Затем запускаете хранимку, которая сделает логику добавления/обновления данных в основную таблицу, сравнивая две таблицы - основную и временную.
Ответ написан
Если есть возможность, лучше удалять данные полностью, а потом добавлять новые при помощи 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.

Базу необходимо правильно настроить. Добавить индексы, где будет уместно. Но не переборщить и учитывать, что они (индексы) могут фрагментироваться и сильно замедлять работу базы. Про фрагментацию самой базы, тоже не стоит забывать. Если будете удалять большие объемы данных, то не забывайте выполнять резервное копирование и сжатие базы.

Соединения с базой лучше использовать одноразовые. Т.е. открыл, выполнил запрос, закрыл и так далее. Если использовать одно соединение, то со временем скорость выполнения запросов будет существенно снижаться.

Универсального решения подобных задач нет, но это примерно то, от чего можно отталкиваться.
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
HaJIuBauKa
@HaJIuBauKa
Зачем вытягивать все данные в память?
Получите сначала список уникальных ключей записей (либо набор полей по которым записи определяются уникальными) и потом вытягивайте запись по-одной и обрабатывайте.

Это синхронизацию данных вы пытаетесь выполнить?
То есть нужно просто перелить данные по коду ID?
Если так то нужно просто запоминать на каком ID закончилась прошлая загрузка и продолжать с него далее.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
17 июн. 2021, в 23:09
800 руб./в час
17 июн. 2021, в 22:01
35000 руб./за проект
17 июн. 2021, в 20:24
22000 руб./за проект