- PVSM.RU - https://www.pvsm.ru -
В процессе развития проекта периодически появляется необходимость обмена данными между серверами баз данных. Предположим, у нас есть источник данных в виде SQL Server и удалённый PostgreSQL сервер, на котором эти данные должны оказаться. После добавления удалённого сервера в качестве linked server, можно делать запросы вида:
INSERT INTO RemotePG...RemoteTable (RecordID, RecordName) VALUES (1,'Test string');
Проблема в том, что такие запросы выполняются очень долго. Если перед нами стоит задача выгрузить десятки и сотни тысяч записей, то время на выполнение стремится к бесконечности. Рассмотрим два с половиной способа вставить данные в таблицу на linked server и сравним время выполнения.
Чтобы создать linked server, у вас уже должен быть источник данных ODBC. В моём случае имя linked server и источника ODBC совпадают.
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'
Пусть на удалённой машине есть простая таблица:
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 мс.
В случае с удалённым сервером 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 секунд, уже лучше, но попробуем уменьшить это время.
Для этого, прежде, чем исполнять запрос, подготовим данные для вставки в таблицу.
-- отметим время начала
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
Источник [1]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/158895
Ссылки в тексте:
[1] Источник: https://habrahabr.ru/post/305982/?utm_source=habrahabr&utm_medium=rss&utm_campaign=sandbox
Нажмите здесь для печати.