[.NET, SQL, Microsoft SQL Server, C#] Linked Server MSSQL. Оптимизация производительности в 30 раз
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Исходные данные:
- Два SQL Server'а, которые находятся в прямой доступности между собой, на одном из которых настроен Linked Server.
- 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При работе с данными очень часто требуется загружать файлы из разных файлов в БД (чаще всего 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
- Сохранить из БД в файл - если файлы хранятся в БД и их нужно сохранить на диск
- Сохранить из БД в файл (утилитой bcp) - если файлы хранятся в БД и их нужно сохранить на диск с помощью утилиты bcp (создается bat файл)
- Сохранить из файла в БД - если нужно загрузить файлы с диска в таблицу БД с полем типа varbinary
- Сохранить из БД в скрипт SQL - сохраняет результат SELECT запроса в SQL файл
- Из БД в скрипт SQL (только INSERT)
- Из БД в скрипт SQL (только UPDATE)
- Статический скрипт SQL
- Сохранить из Excel в скрипт SQL
- Сохранить из БД в CSV
- Сохранить из CSV в SQL
- Сохранить из CSV в БД
- Сохранить конфигурацию БД в SQL - выгружает структуру БД в SQL файл
- Сохранить из БД в БД - сохраняет результат SELECT запроса на другой или текущий сервер
Все типы обработки, которые заканчиваются словом SQL могут объединяться в один файл, если имя файла одинаковое в нескольких задачах. Это очень удобно для копирования данных из одной БД в другую (например, при переносе данных с прода на тест или наоборот).Сохранить из БД в БД
Сохранить из БД в БДИспользование данного способа позволило оптимизировать запрос (приведенный в начале статьи) копирования данных через Linked Server, сократив время выполнения с 1 минуты до 2 секунд. Алгоритм копирования данных из одной БД в другую выполнен стандартными классами языка C# из пространства имен System.Data.SqlClient: SqlConnection, SqlDataReader, SqlCommand и SqlBulkCopy.Чтобы не возникало ошибки нехватки памяти OutOfMemoryException, чтение и запись данных выполняется блоками (частями). Блок ограничивается максимальным количеством записей, который определяется пользователем. Параметры, которые задает пользователь:
- SQL запрос - выполняется на БД источнике, с которой нужно копировать информацию
- Настройки выгрузки в БД назначения:Имя соединения - выбирается из списка соединений, которые пользователь сохраняет на форме "Соединение с БД", отображаемая при запуске приложения. Точка (.) в параметре "Имя соединения" означает, что используется текущее соединение с БД.Имя таблицы - в которую нужно копировать записи. Таблицу можно выбирать из списка, либо указать вручную (может содержать не только имя схемы и имя таблицы, но и имя БД)Номер последней обрабатываемой строки - служит для ограничения количества копируемых строк, и применяется для отладки. Например, если запрос возвращает 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, для желающих пообщаться с автором
===========
Источник:
habr.com
===========
Похожие новости:
- [Python, Программирование, Математика, Машинное обучение] Оптимизация при помощи линейного поиска на Python (перевод)
- [Google Chrome, Интерфейсы, Браузеры, Поисковая оптимизация] Google отказался от экспериментов с сокращением URL-адресов в адресной строке
- [Искусственный интеллект, Голосовые интерфейсы] Google опубликовал пособие по Voice Playbook для разработчиков conversational AI
- [Программирование, .NET, Карьера в IT-индустрии] Возможные неопределенности в карьере программиста. Часть 2
- [Настройка Linux, PostgreSQL, Администрирование баз данных] Измеряем расходы на память у Postgres процессов (перевод)
- [IT-инфраструктура, Серверная оптимизация, Тестирование веб-сервисов, Облачные сервисы, IT-компании] Провайдер CDN Fastly пояснил причину масштабного сбоя
- [1С] Лучше 1С может быть только 1С: Базуха
- [Сетевые технологии] Azure Active Directory Gateway теперь на .NET Core 3.1 (перевод)
- [PostgreSQL] Отказоустойчивый кластер PostgreSQL с помощью crm
- [Информационная безопасность, Разработка веб-сайтов, Поисковая оптимизация, Научно-популярное] Самый популярный HTML-редактор в выдаче Google — это афера с поисковой оптимизацией (перевод)
Теги для поиска: #_.net, #_sql, #_microsoft_sql_server, #_c#, #_optimizatsija (оптимизация), #_sql, #_sql_server, #_linked_server, #_.net, #_sql, #_microsoft_sql_server, #_c#
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 04:17
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
Исходные данные:
insert into LocalDatabaseName.dbo.TableName (column1, column2, ..., columnN)
select column1, column2, ..., columnN from LinkedServerName.RemoteDatabaseName.dbo.TableName  Главная форма ImportExportDataSqlПри работе с данными очень часто требуется загружать файлы из разных файлов в БД (чаще всего CSV и Excel) и обратно (из БД в CSV). До этого пользовался утилитой bcp, но всегда не хватало графического интерфейса. Кроме этого у "bcp", есть недостатки, описанные в моей предыдущей статье.В ImportExportDataSql кроме графического интерфейса, реализована возможность работы через командную строку. Пример командной строки:Пример работы ImportExportDataSql из командной строки: ImportExportDataSql.exe -ConnectionName="Имя соединения с БД" -TaskName="Имя Задачи 1" -TaskName="Имя задачи 2" [-Log="C:\FolderName\LogFileName.log"]
 Сохранить настройку соединения с БД-TaskName - Имя задачи из пользовательского списка задач-Log - имя лог файла. Необязательный параметр. По-умолчанию, используется лог файл в папке Logs\UserName\ImportExportDataSql.logСписок решаемых задач в ImportExportDataSql
 Сохранить из БД в БДИспользование данного способа позволило оптимизировать запрос (приведенный в начале статьи) копирования данных через Linked Server, сократив время выполнения с 1 минуты до 2 секунд. Алгоритм копирования данных из одной БД в другую выполнен стандартными классами языка C# из пространства имен System.Data.SqlClient: SqlConnection, SqlDataReader, SqlCommand и SqlBulkCopy.Чтобы не возникало ошибки нехватки памяти OutOfMemoryException, чтение и запись данных выполняется блоками (частями). Блок ограничивается максимальным количеством записей, который определяется пользователем. Параметры, которые задает пользователь:
=========== Источник: habr.com =========== Похожие новости:
|
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 04:17
Часовой пояс: UTC + 5