Автор: @QuickJoey
В процессе развития проекта периодически появляется необходимость обмена данными между серверами баз данных. Предположим, у нас есть источник данных в виде 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=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. Данные для вставки в таблицу специально упрощены.