Прискорюємо вставку даних MSSQL на віддалений PostgreSQL в 800 разів

У процесі розвитку проекту періодично з'являється необхідність обміну даними між серверами баз даних. Припустимо, у нас є джерело даних у вигляді SQL Server і віддалений PostgreSQL сервер, на якому ці дані повинні виявитися. Після додавання віддаленого сервера в якості linked server, можна робити запити виду:
INSERT INTO RemotePG...RemoteTable (RecordID, RecordName) VALUES (1,'Test string');

Проблема в тому, що такі запити виконуються дуже довго. Якщо перед нами стоїть завдання вивантажити десятки і сотні тисяч записів, то час на виконання прагне до нескінченності. Розглянемо два з половиною способу вставити дані в таблицю на linked server і порівняємо час виконання.

Створення нового linked server:Щоб створити linked server, у вас вже повинен бути джерело даних ODBC. В моєму випадку ім'я linked server і ODBC джерела збігаються.
USE [master]
GO

-- покладемо ім'я linked server в змінну
declare @ServerName nvarchar(200)
SET @ServerName='RemotePG'

-- додамо віддалений сервер
EXEC master.dbo.sp_addlinkedserver @server = @ServerName, @srvproduct=@ServerName, @provider='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='rpc', @optvalue='true'
EXEC master.dbo.sp_serveroption @server=@ServerName, @optname='rpc out', @optvalue='true'


Спосіб номер 1
Нехай на віддаленій машині є проста таблиця:
CREATE TABLE RemoteTable (RecordID INT, RecordName VARCHAR(200));

Подивимося на час виконання простого запиту:
-- зазначимо час початку
SELECT getdate();
GO

-- оголосимо та ініціалізуємо змінну
DECLARE @INT i;
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 @INT i;
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 @INT i;

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. Дані для вставки в таблицю спеціально спрощені.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.