В процессе развития проекта периодически появляется необходимость обмена данными между серверами баз данных. Предположим, у нас есть источник данных в виде 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'Способ номер 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. Данные для вставки в таблицу специально упрощены.
Комментарии (36)
amaksr
19.07.2016 20:10Интересный способ связывать MS SQL и Postgres, не знал про него.
Но в остальном как-то маловато для статьи на Хабре.
QuickJoey
20.07.2016 09:49с одной стороны, да, ничего особенного, с другой, когда стоит задача передать много данных, и вроде бы механизм для этого есть, а пользоваться им невозможно, захотелось рассказать как это решено.
MinamotoSoft
19.07.2016 20:56Метод правильный — выводы ошибочные.
Основная разница между 1 и 2-2.5 вариантами — колличество соединений.
В первом случае у вас перед вставкой каждой строки открывается соединение, после вставки — закрывается. Вот в этом и кроется «тормоз» так как сама процедура установления соединения достаточно медленная.
Ну а последний вариант — это уже не совсем «SQL». Это больше похоже на «блочную работу» по типу как у MySqlDump. И этот метод станет не таким эффективным если размер «блока» будет мегабайт 100-1000. (из-за большого времени на физический транспорт) А вот если сформировать «блок» в виде текста, заархивировать, передать мультипоточным протоколом, а принимающая сторона распакует и выполнит на «принимающем» сервере — это будет куда более эффективный метод.
Главный «враг» во всем этом это Ваши 16мс задержки между хостами.QuickJoey
20.07.2016 09:57начну с конца. про главного врага и да и нет. я специально писал про удалённый сервер, но в реальности ситуация с локальным точно такая же. мне кажется, главный враг — это ожидание подтверждения транзакции со стороны mssq. про количество соединений, если честно не догадался проверить, вы утверждаете, что в одной транзакции mssql каждый раз открывает новое соединение?
блоки, которые сейчас используются в бою, это примерно 30Мб раз в час, на сотню узлов PostgeSQL, и примерно 150Мб два раза в сутки, на ту же сотню хостов.
способ с архивацией — передачей — распаковкой хорош, но хотелось обойтись без ещё одной детальки, которая может сломаться.
vdem
19.07.2016 20:56+1Не знаю как насчет удаленной БД, но и на локальной вставка большого количества записей по одной будет занимать немалое время. А вариант с одним запросом INSERT, вставляющим тысячу строк за один раз — я полагаю, ни для кого ни секрет.
QuickJoey
20.07.2016 09:58главное не в INSERT с большим количеством строк за раз, а, всё-таки, удалённое исполнение этого запроса.
vdem
20.07.2016 10:52Все же не соглашусь :) Удаленное выполнение запроса в Вашем примере увеличивает эффективность всего в 10 раз, в то время как вставка многих записей одним INSERT увеличивает эффективность в 80 раз (ориентировался по приведенным оценкам времени выполнения). Так что пунктом 2 следовало бы описать вставку многих записей одним запросом, а пунктом 2.5 — удаленное выполнение.
SabMakc
19.07.2016 21:44+1Не пробовали вариант с INSERT… SELECT…?
Должно быстро отработать и нет необходимости формирования запроса «вручную».MinamotoSoft
19.07.2016 23:15Эта штука точно сработает внутри одного сервера. Как она сработает со «связанными» — предсказать сложно. Надо проверять ;)
QuickJoey
20.07.2016 10:52попробовал, результат такой же, как и в первом случае 2 минуты 49 секунд на 1000 строк.
insert into RemotePG...RemoteTable (RecordID, RecordName) select top 1000 RecordID, RecordName from LocalTable with(nolock)
avloss
20.07.2016 00:40мне кажется даже если это сработает — то в реальной жизни такой поход упрется в максимальную длину запроса, и это случится на живом сервере в самый не подходящий момент. К тому страдает читаемость кода и логов. Спасибо за статью!
QuickJoey
20.07.2016 10:15это не если, а вполне работает, выше ответил. удалённые сервера, это тупенькие машинки, 2Гб оперативки, на целеронах, используются как POS терминалы в розничной сети.
DarkOrion
20.07.2016 07:26Насколько оперативно новые записи из MSSQL должны появляться в Postgres? Если раз в день/часс, то почему бы не попробовать написать ETL и грузить с помощью SSIS?
QuickJoey
20.07.2016 10:04есть несколько блоков данных, условно медленные и быстрые, быстрые — раз в час, медленные два раза в сутки. проблема в том, что хостов, на которых эти данные нужны, больше сотни. таблиц, которые таким образом синхронизируются примерно два десятка.
а чем SSIS пакет будет принципиально отличаться? когда-то давно пробовал SSIS с единственным mysql, скорость была похожа, как если бы я просто исполнял запросы из консоли.DarkOrion
20.07.2016 10:20Ну формально SSIS как и раз и создан под задачи, которые вы описываете — мигрировать данные между системами. Он по дефолту заточен под большие объемы, использует пакетные операции и т.п. Конкретный адаптер\драйвер для системы (в вашем случае — Postgres) обычно тоже можно затюнить под конкретную задачу\профиль данных (размер блока и т.п.).
Соответственно пишите ETL (если вы не трансформируете данные, то это вообще можно мастером сделать), настраиваете два джоба (часовой и полусуточный) и готово.
Конкретных бенчмарков к сожалению не могу предоставить. Попробуйте на одной таблице, в теории должно быть как минимум не хуже вашего решения.QuickJoey
20.07.2016 10:50у меня сложилось некоторое предубеждение против SSIS, уж больно он капризный. но вы меня убедили, возьму себя в руки и попробую на какой-нибудь большой таблице. отпишусь по результатам.
QuickJoey
20.07.2016 11:50попробовал, цедит в час по чайной ложке: за 15 минут около 9к. записей. при этом в свойствах data transformation вроде бы прописано, что 1000 записей за раз, в 100 потоков. плюс, SSIS не видит источников данных из 64-битного драйвера, что неудобно, потому что у меня все источники заведены там.
semI-PACK
20.07.2016 19:37Тоже столкнулся с такой задачей. Данные на линкед сервер шли очень медленно. Таблица с 65 млн записей скопировалась бы по расчетам только через 300 суток непрерывной записи. Решение нашли тоже через SSIS. В access базу пишутся через ssis пакет по 1 млн данные (если больше аксесс тупить начинает), затем другой ssis выбирает из него данные в другой сервер (но уже напрямую).
DarkOrion
21.07.2016 12:43Очень странно. У нас SSIS перегружает Oracle > MS SQL по 4 млн.записей в минуту, причем это ограничено в основном скоростью работы view на стороне Oracle (из физических таблиц — 5+ млн.записей в минуту). Таблицы не очень широкие, атрибутов по 10-20.
QuickJoey
21.07.2016 17:56общее с комментарием выше – таргет сервер mssql, видимо дело в этом.
DarkOrion
22.07.2016 01:18А какие версии у Postgres/MSSQL? Подниму у себя, погоняю. Непонятно, где затык. Скорость вашего метода 2.5 имхо в том, что данные одной строкой передаются, по сути файлом. Можно попробовать вставить ssisом из csv.
QuickJoey
22.07.2016 09:54версии более-менее всё равно, 9.5 Postgres, и любой выше 2000 у MSSQL. главное в скорости, что MSSQL не следит за подтверждением транзакции на удалённом сервере. отправил, там что-то исполнилось, здесь пришло «запрос исполнился».
csv вряд ли даст какой-нибудь прирост, но появится лишний шаг (или два), которые тоже могу сломаться. и проблема, мне кажется, не в том откуда выбирать, выбирает быстро, вставляет медленно.
Danik-ik
20.07.2016 10:45В mssql есть прекрасный и быстрый инструмент для импорта- экспорта. За давностью лет могу ошибиться в названиях, но если в enterprise manager зайти в раздел packages — то это самое оно.
Быстро
Любые odbc источники
Можно планировать запуск
Можно строить сложные последовательности обработки
И НЕ НАДО ДУМАТЬ ОБ ЭКРАНИРОВАНИИ ДАННЫХ!!!
Это был mssql 2000. Вряд ли сегодня стало много хуже.Danik-ik
20.07.2016 11:40кстати, мы использовали это для систематической синхронизации двух баз данных управленческого учёта в процессе постепенного перехода на новое ПО: старой, несвязной, с двоящимися записями на карат aka db2 и новой на MSSQL. Абсолютно разные структуры таблиц, проверки, устранение дублирования, обеспечение связности данных… При этом визуальное наглядное представление и автоматическая работа. На тот момент кроме MS никто, кажется, такого полноценного инструмента не имел.
Целиковый запрос м.б. и побыстрее, но экранирование данных игнорировать нельзя.QuickJoey
20.07.2016 11:55ответил выше
такого красиво оформленного инструмента может быть и нет, хотя складывается ощущение, что инструмент в основном заточен под продукты MS. в Postges есть copy, весьма шустрая, но тоже с нюансами.
radya100
20.07.2016 17:02Talend — возможно лучшее решение, чем SSIS для данной задачи/ Есть бесплатный www.talend.com/download
QuickJoey
21.07.2016 17:58там куча инструментов, и по названию так сразу не понятно, чем они занимаются. а качать по 700Мб, чтобы в этом разобраться…
может быть вы напишите что именно пробовали и как настраивали? хотя, ощущение, что краткого описания не получится, там полноценная статья.
easyman
Хотел спросить, что будет, когда превысите максимальную длину запроса.
Отвечаю сам: Нет лимита. ( There has been no hard-wired upper limit for several releases now. )
BalinTomsk
— Нет лимита
То на что вы указали — лимит на сам сервер PostgreSQL, если скрипт будете выполнять непосредтвенно на серверe.
На стороне MSSQL есть лимит на переменную VARCHAR(max), а еше лимиты на фреймворк использумый для передачи данных.
TimsTims
А еще в MSSQL нельзя просто так используя INSERT INTO вставлять более 1000 записей за раз. Вместо этого приходится использовать конструкцию BULK INSERT, либо другие хитрые INSERT into table SELECT from
QuickJoey
в реальной жизни данные вставляются блоками по 500 записей. в таком случае, в случае обрыва, можно продолжить с того места, где закончил. а не пытаться запихнуть 1млн. записей разом.