Linked Server MSSQL. Оптимизация производительности в 30 раз

Моя цель - предложение широкого ассортимента товаров и услуг на постоянно высоком качестве обслуживания по самым выгодным ценам.

Исходные данные:

  1. Два SQL Server'а, которые находятся в прямой доступности между собой, на одном из которых настроен Linked Server.

  2. SQL запрос вида:

insert into LocalDatabaseName.dbo.TableName (column1, column2, ..., columnN)
select column1, column2, ..., columnN
from LinkedServerName.RemoteDatabaseName.dbo.TableName

Задача: максимально быстро скопировать записи с одного сервера на другой

Столкнулся с тем, что подобный запрос выполняется на 40k (40000) записей больше минуты. С ростом количества подобных запросов или количества записей, производительность сильно падает и оптимизировать запрос средствами SQL никак нельзя. С использованием приложения ImportExportDataSql мне удалось ускорить этот запрос до 2 секунд, не используя Linked Server.

Приложение ImportExportDataSql создавал для себя и постоянно его дорабатывал на протяжении нескольких лет. Основные требования при создании приложения - портативность, работа под всеми версиями Windows без установки сторонних библиотек (кроме NET Framework 3.5), простой интерфейс и высокая производительность.

ImportExportDataSql - универсальный конвертер данных, как альтернатива "bcp"

Главная форма ImportExportDataSql
Главная форма ImportExportDataSql

При работе с данными очень часто требуется загружать файлы из разных файлов в БД (чаще всего CSV и Excel) и обратно (из БД в CSV). До этого пользовался утилитой bcp, но всегда не хватало графического интерфейса. Кроме этого у "bcp", есть недостатки, описанные в моей предыдущей статье.

В ImportExportDataSql кроме графического интерфейса, реализована возможность работы через командную строку. Пример командной строки:

Пример работы ImportExportDataSql из командной строки:
ImportExportDataSql.exe -ConnectionName="Имя соединения с БД" -TaskName="Имя Задачи 1" -TaskName="Имя задачи 2" [-Log="C:\FolderName\LogFileName.log"]

Параметры командной строки:

-ConnectionName - Имя соединения с БД, которое должно быть сохранено на форме "Соединение с БД" по кнопке "Сохранить настройку соединения с БД"

Сохранить настройку соединения с БД
Сохранить настройку соединения с БД

-TaskName - Имя задачи из пользовательского списка задач

-Log - имя лог файла. Необязательный параметр. По-умолчанию, используется лог файл в папке Logs\UserName\ImportExportDataSql.log

Список решаемых задач в ImportExportDataSql

  1. Сохранить из БД в файл - если файлы хранятся в БД и их нужно сохранить на диск

  2. Сохранить из БД в файл (утилитой bcp) - если файлы хранятся в БД и их нужно сохранить на диск с помощью утилиты bcp (создается bat файл)

  3. Сохранить из файла в БД - если нужно загрузить файлы с диска в таблицу БД с полем типа varbinary

  4. Сохранить из БД в скрипт SQL - сохраняет результат SELECT запроса в SQL файл

  5. Из БД в скрипт SQL (только INSERT)

  6. Из БД в скрипт SQL (только UPDATE)

  7. Статический скрипт SQL

  8. Сохранить из Excel в скрипт SQL

  9. Сохранить из БД в CSV

  10. Сохранить из CSV в SQL

  11. Сохранить из CSV в БД

  12. Сохранить конфигурацию БД в SQL - выгружает структуру БД в SQL файл

  13. Сохранить из БД в БД - сохраняет результат SELECT запроса на другой или текущий сервер

Все типы обработки, которые заканчиваются словом SQL могут объединяться в один файл, если имя файла одинаковое в нескольких задачах. Это очень удобно для копирования данных из одной БД в другую (например, при переносе данных с прода на тест или наоборот).

Сохранить из БД в БД

Сохранить из БД в БД
Сохранить из БД в БД

Использование данного способа позволило оптимизировать запрос (приведенный в начале статьи) копирования данных через Linked Server, сократив время выполнения с 1 минуты до 2 секунд. Алгоритм копирования данных из одной БД в другую выполнен стандартными классами языка C# из пространства имен System.Data.SqlClient: SqlConnection, SqlDataReader, SqlCommand и SqlBulkCopy.

Чтобы не возникало ошибки нехватки памяти OutOfMemoryException, чтение и запись данных выполняется блоками (частями). Блок ограничивается максимальным количеством записей, который определяется пользователем. Параметры, которые задает пользователь:

  1. SQL запрос - выполняется на БД источнике, с которой нужно копировать информацию

  2. Настройки выгрузки в БД назначения:

    Имя соединения - выбирается из списка соединений, которые пользователь сохраняет на форме "Соединение с БД", отображаемая при запуске приложения. Точка (.) в параметре "Имя соединения" означает, что используется текущее соединение с БД.

    Имя таблицы - в которую нужно копировать записи. Таблицу можно выбирать из списка, либо указать вручную (может содержать не только имя схемы и имя таблицы, но и имя БД)

    Номер последней обрабатываемой строки - служит для ограничения количества копируемых строк, и применяется для отладки. Например, если запрос возвращает 100 записей, а "Номер последней обрабатываемой строки" = 10, то будет скопировано только 10 первых строк из результата запроса.

    Количество строк в блоке - количество строк сохраняемых одной транзакцией

Способом "Сохранить из БД в БД" я также пользуюсь, когда необходимо скопировать результат запроса с большим количеством записей. Ограничение количества записей, в этом случае, дает преимущество, перед обычным запросом копирования записей (insert into ... select ...), так как снижается нагрузка на диск, не сильно растет журнал транзакций и не используется база tempdb (если "Количество строк в блоке" оптимальное).

Преимущества и применение ImportExportDataSql

Приложение ImportExportDataSql постоянно помогает мне в работе. С помощью него удобно переносить данные из одной БД в другую.

В коде встроено множество проверок, чтобы достаточно быстро можно было понимать на какой строке возникла ошибка при импорте CSV файла или Excel.

Можно загружать большие CSV файлы (больше 1Гб) и добавлять свои поля, которых нет в CSV. Отсекать ненужные поля из CSV, не загружая их в БД.

Скрипты при выгрузке в SQL формат дополнены различными проверками, чтобы при выполнении скрипта на другой базе все ошибки отображались в одной таблице, а не списком ошибок на панеле "Messages" в SQL Server Management Studio.

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

Заключение

Используя язык C# и класс SqlBulkCopy можно существенно сократить время выполнения запроса, в котором используется Linked Server.

Ссылки

Скачать ImportExportDataSql

Статья с подробным описанием ImportExportDataSql

Статья "Быстрое чтение CSV в C#", в которой рассказывается о недостатках "bcp"

Сообщество VK, для желающих пообщаться с автором

Источник: https://habr.com/ru/post/562416/

Интересные статьи

Интересные статьи

В середине июле этого года мы рассказывали о том, что была представлена бета-версия Proxmox Backup Server (PBS). В день холостяков, 11.11.2020 в 11:11, Proxmox Server Solutions GmbH опу...
В данной статье мы продолжаем рассказывать про работу с Windows Server Core 2019. В прошлых постах мы рассказали как готовим клиентские виртуальные машины на примере нашего нового тарифа VDS Ultr...
По умолчанию конфигурация PostgreSQL не настроена для рабочей нагрузки. Значения по умолчанию установлены для обеспечения работоспособности PostgreSQL везде с наименьшим количеством ресурсов. Им...
Пользователи ClickHouse знают, что его главное преимущество — высокая скорость обработки аналитических запросов. Но как мы можем выдвигать такие утверждения? Это должно подтверждаться тестами про...
Дано: старое http node.js приложение и возросшая нагрузка на него. Стандартные решения проблемы: докинуть серверов, все переписать с 0, оптимизировать уже написанное. Давайте попробуем пойти пу...