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


AliExpress RU&CIS

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

  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, для желающих пообщаться с автором




Комментарии (8):

  1. vagon333
    /#23145820 / +2

    Сравнение с LinkedServer некорректно т.к. LinkedServer предназначен для:
    1. постоянного соединения с удаленной базой
    2. автоматического обмена

    Ваша утилита для ручных операций и, как любое приложение, легко может иметь массу соединений с разными источниками данных, включая несколько SQL серверов.
    Замечательно.

    Согласитесь, мало кто решится работать с базами через приложение неизвестного разработчика.
    Хороший тон — линк на гитхаб, чтоб интересующиеся могли проверить код на безопасность перед использованием.

    Удачи.

  2. TimsTims
    /#23146054 / +2

    Openquery делает тоже самое, доступен из коробки, запрос выполняется на удаленном сервере, возвращается только результат

    Select * from Openquery ([Linkedservername],'select * from remote_table where a=b') as c

    • qw1
      /#23149022

      Выборка быстро работает… Но задача — переливка данных.

      insert into openquery (...) select ...
      работает медленно.

      Я думаю, как и вставка через linked server, оно не использует batch insert, а специализированные утилиты используют.

      • TimsTims
        /#23149436

        Скорее всего у вас есть доступ к обоим серверам. Значит имеется два Линка, с первого на второй, и со второго на первый. Значит:

        1) можно запускать запрос изначально на втором сервере, чтобы забрать данные с первого.

        2) как особое извращение, если нет доступа к джобам второго - запустить с первого openquery , в котором на втором сервере исполнится вложенный openquery с запросом к первому серверу. Некрасиво, но перегрузки нет.

        • qw1
          /#23150492

          По моему опыту, с select нет проблем, что с linked server, что с openquery. А вот update/insert — по 1 стейтменту на каждую строку, 1 сетевой пакет на строку, это не даёт возможность поднять скорость вставки выше нескольких тысяч рекордов в секунду (не путать linked server со ссылкой на другую БД на том же сервере, тут средствами штатного SQL скорости копирования между базами уже нормальные).

        • qw1
          /#23150506

          1) можно запускать запрос изначально на втором сервере, чтобы забрать данные с первого.
          А, вот такой скрипт можно попробовать.

  3. mssqlhelp
    /#23149868

    Если хотите, чтобы копирование данных выполнялось с высокой производительностью всегда, ознакомьтесь с этими сатьями в тему:
    docs.microsoft.com/ru-ru/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)?redirectedfrom=MSDN
    blogs.msmvps.com/gladchenko/loading-performance-guide

  4. somurzakov
    /#23152304

    зачем заморачиваться с линкедсервер для переливки данных, когда есть старый добрый SSIS?

    никакого си шарпа, умения программировать или знания внутренностей sql не надо, любой самый дешевый джун или вчерашний вайтишник может мышкой потыкать и drag&drop-нуть один Data Flow и все. с популяризацией Ажур, то их аналог ADF то же самое делает за пару тыков мышкой

    по скорости один из самых быстрых и дешевых способов и легко поддерживаемых даже людьми далекими от тонкостей администрирования баз данных