Ускоряем вставку данных из MSSQL на удалённый PostgreSQL в 800 раз

В процессе развития проекта периодически появляется необходимость обмена данными между серверами баз данных. Предположим, у нас есть источник данных в виде SQL Server и удалённый PostgreSQL сервер, на котором эти данные должны оказаться. После добавления удалённого сервера в качестве linked server, можно делать запросы вида:

INSERT INTO RemotePG...RemoteTable (RecordID, RecordName) VALUES (1,'Test string');

Проблема в том, что такие запросы выполняются очень долго. Если перед нами стоит задача выгрузить десятки и сотни тысяч записей, то время на выполнение стремится к бесконечности. Рассмотрим два с половиной способа вставить данные в таблицу на linked server и сравним время выполнения.

USE [master]
GO

-- положим имя linked server в переменную
declare @ServerName nvarchar(200)
SET @ServerName=N'RemotePG'

-- добавим удалённый сервер
EXEC master.dbo.sp_addlinkedserver @server = @ServerName, @srvproduct=@ServerName, @provider=N'MSDASQL', @datasrc=@ServerName

-- добавим пользователя с удалённого сервера
-- именно под этой учётной записью будут выполнятся запросы на удалённой машине
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@ServerName,@useself=N'False',@locallogin=NULL,@rmtuser=N'remote_user',@rmtpassword='password'

-- разрешим удалённый вызов процедур
EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@ServerName, @optname=N'rpc out', @optvalue=N'true'

Способ номер 1

Пусть на удалённой машине есть простая таблица:

CREATE TABLE RemoteTable (RecordID INT, RecordName VARCHAR(200));

Посмотрим на время выполнения простого запроса:

-- отметим время начала
SELECT getdate();
GO

-- объявим и инициализируем переменную
DECLARE @i INT;
SET @i=0;

-- вставим тысячу строк напрямую в удалённую таблицу
WHILE @i<1000
BEGIN
    INSERT INTO RemotePG...RemoteTable (RecordID, RecordName) VALUES (1,'Test string');
    SET @i=@i+1;
END
GO

-- отметим время окончания
SELECT getdate();
GO

В моём случае это заняло 2 минуты 52 секунды. Примерно 6 записей в секунду. Небыстро. Для справки: удалённая машина находится на канале около 5 Мбит/сек и средним пингом 16 мс.

Способ номер 2

В случае с удалённым сервером SQL Server позволяет использовать конструкцию вида:

EXECUTE ('sql запрос на удалённой машине') AT LinkedServerName;

Чтобы это было возможным, в настройках Linked Server должны быть разрешены удалённые вызовы процедур (RPC = remote procedure call). В этом случае запрос выполняется непосредственно на удалённой машине.
Посмотрим, как это скажется на времени выполнения:

-- отметим время начала
SELECT getdate();
GO

-- объявим и инициализируем переменную
DECLARE @i INT;
SET @i=0;

-- вставим тысячу строк исполняя запрос удалённо
WHILE @i<1000
BEGIN
    EXECUTE ('INSERT INTO RemoteTable (RecordID, RecordName) VALUES (1,''Test string'');') AT RemotePG;
    SET @i=@i+1;
END
GO

-- отметим время окончания
SELECT getdate();
GO

Время выполнения 17.25 секунд, уже лучше, но попробуем уменьшить это время.

Способ номер 2.5

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

-- отметим время начала
SELECT getdate();
GO

-- объявим и инициализируем переменные
DECLARE @sql VARCHAR(max);
DECLARE @i INT;

SET @i=0;

-- напишем начало скрипта
SET @sql='INSERT INTO RemoteTable (RecordID, RecordName) VALUES ';

-- добавим в скрипт данные для вставки
WHILE @i<1000
BEGIN
    SELECT @sql=@sql+'(1,''Test string''),';
    SET @i=@i+1;
END

-- заменим последний символ ',' на ';'
SELECT @sql=SUBSTRING(@sql,1,LEN(@sql)-1)+';'

-- выполним запрос удалённо
EXECUTE (@sql) AT RemotePG;
GO

-- отметим время окончания
SELECT getdate();
GO

Запрос будет представлять из себя длинную строку вида:

INSERT INTO RemoteTable (RecordID, RecordName) VALUES (1,'Test string'),(1,'Test string') ... (1,'Test string');

Такой запрос, в тех же условиях выполнился за 217 миллисекунд. Что примерно в 800 раз быстрее первоначального варианта.

P. S. Данные для вставки в таблицу специально упрощены.

Автор: QuickJoey

Источник