Михаил Смирнов: апреля 2007

Здесь я привожу копию моей статьи "Технологии Push и Pull при работе с linked servers в Microsoft SQL Server", опубликованной в рассылке MS SQL Server - дело тонкое #354 от 22.04.2007 г (http://www.sql.ru/articles/mssql/2007/051803PushAndPullinMicrosoftSQLServerLinkedServers.shtml).

Технологии Push и Pull при работе с linked servers в Microsoft SQL Server

Для обеспечения взаимодействия нескольких серверов Microsoft SQL Server наиболее часто используется технология linked-серверов. При этом, типичной является задача обмена данными между linked-серверами. В данной статье я проведу краткий сравнительный анализ технологий push и pull для решения задачи передачи новых данных.
Провести такой анализ проще всего на примере. Пусть у нас есть два сервера – сервер-источник (SourceServer) и целевой сервер (DestServer). В качестве примера рассмотрим задачу, когда нам необходимо передать таблицу Customers c сервера-источника на целевой сервер.

Технология Push
Технология Push характеризуется “заталкиванием” данных с исходного сервера на целевой сервер. Т.е. SQL-запрос для передачи таблицы Customers будет выполняться на исходном сервере и выглядеть так:


insert into DestServer.Northwind.dbo.Customers
select *
from Customers

Для того, чтобы понять, как такой запрос будет обработан SQL Server’ом, необходимо воспользоваться SQL Server Profiler.
На исходном сервере запрос выполняется в неизменном виде –


insert into DestServer.Northwind.dbo.Customers
select top *
from Customers
go

Однако, на целевом сервере все совсем не так. На нем выполняются следующие запросы -


set implicit_transactions on 
go

declare @P1 int
set @P1=1
declare @P2 bigint
set @P2=8400823175122854
exec sp_getschemalock @P1 output, @P2 output, 
N'"Northwind"."dbo"."Customers"'
select @P1, @P2
go

declare @P1 int
set @P1=180150000
declare @P2 int
set @P2=2
declare @P3 int
set @P3=4
declare @P4 int
set @P4=-1
exec sp_cursoropen @P1 output, 
N'select * from "Northwind"."dbo"."Customers"', 
@P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
go

exec sp_cursor 180150000, 4, 0, 
N'Northwind.dbo.Customers', 
@CustomerID = N'ALFKI', 
@CompanyName = N'Alfreds Futterkiste', 
@ContactName = N'Maria Anders',
@ContactTitle = N'Sales Representative', 
@Address = N'Obere Str. 57', 
@City = N'Berlin', 
@Region = NULL, 
@PostalCode = N'12209', 
@Country = N'Germany', 
@Phone = N'030-0074321', 
@Fax = N'030-0076545'
go

exec sp_cursor 180150000, 4, 0, 
N'Northwind.dbo.Customers', 
@CustomerID = N'ANATR', 
@CompanyName = N'Ana Trujillo Emparedados y helados', 
@ContactName = N'Ana Trujillo', 
@ContactTitle = N'Owner', 
@Address = N'Avda. de la Constitucion 2222', 
@City = N'Mexico D.F.', 
@Region = NULL, 
@PostalCode = N'05021', 
@Country = N'Mexico', 
@Phone = N'(5) 555-4729', 
@Fax = N'(5) 555-3745'
go

exec sp_cursor 180150000, 4, 0, 
N'Northwind.dbo.Customers', 
@CustomerID = N'ANTON', 
@CompanyName = N'Antonio Moreno Taqueria', 
@ContactName = N'Antonio Moreno', 
@ContactTitle = N'Owner',
@Address = N'Mataderos  2312', 
@City = N'Mexico D.F.', 
@Region = NULL, 
@PostalCode = N'05023', 
@Country = N'Mexico', 
@Phone = N'(5) 555-3932', 
@Fax = NULL
go

exec sp_cursorclose 180150000
go

IF @@TRANCOUNT > 0 COMMIT TRAN
Go

set implicit_transactions off 
go

Видно, что при вставке на удаленный сервер, SQL Server выполняет следующие операции:

Открывает распределенную транзакцию, включая режим неявных транзакций.
Открывает курсор и вставляет записи последовательно одну за другой, а не все сразу, как в случае обычной вставки, без использования linked-серверов.

Последовательную вставку каждой записи также можно заметить, если на целевом сервере создать триггер на INSERT – триггер будет срабатывать столько раз, сколько записей существует во вставляемом наборе.
Для примера – следующий триггер на таблице Customers:


CREATE TRIGGER dbo.I_Customers
   ON  dbo.Customers
   AFTER INSERT
AS 
BEGIN
 declare @InsertedCount int, @TotalRows int
 select @InsertedCount = count (*) from inserted
 select @TotalRows = count (*) from Customers

 insert into CallsCount (InsertedCount, TotalRows)
 values (@InsertedCount, @TotalRows)
END

В этом триггере CallsCount - это вспомогательная таблица, которая поможет подсчитать сколько раз триггер был запущен. Она содержит два поля – InsertedCount – кол-во записей в таблице inserted и TotalRows – общее кол-во записей в таблице Customers на момент срабатывания триггера.
После выполнения предыдущего запроса на вставку выборка из таблицы CallsCount дает следующий результат:

InstertedCount	TotalRows
1	1
1	2
1	3

Здесь видно, что триггер был вызван 3 раза. При этом каждый раз в таблице inserted была одна запись, а кол-во записей в таблице Customers увеличивалось постепенно. Это еще раз доказывает то, что записи вставляются последовательно, одна за одной.
Можно провести интересный эксперимент, изменив триггер следующим образом:


ALTER TRIGGER dbo.I_Customers 
   ON  dbo.Customers
   AFTER INSERT
AS 
BEGIN
 declare @InsertedCount int, @TotalRows int
 select @InsertedCount = count (*) from inserted
 select @TotalRows = count (*) from Customers

 insert into CallsCount (InsertedCount, TotalRows)
 values (@InsertedCount, @TotalRows)

 if @TotalRows = 3
  rollback tran
END

Т.е. таким образом можно как бы попытаться откатить вставку третьей строки. Однако, после запуска запроса на вставку, при наличии такого триггера, обе таблицы – Customers и CallsCount будут пустыми – в них не будет не только третьей записи, но и вообще ни одной.
Причина этого видна в перехваченной последовательности запросов - SQL Server объявляет распределенную транзакцию. Вызывая rollback tran в триггере мы откатываем не только данный триггер, но и всю транзакцию вставки. Это, вообще говоря, логично, так как при этом работа триггера выглядит так же как и при вставке не из удаленного сервера, с той лишь разницей, что вызывается он несколько раз. Именно для обработки таких ситуаций SQL Server и объявляет распределенную транзакцию. Побочным эффектом такой транзакции является длительная блокировка таблицы Customers на целевом сервере на все время вставки.
Все эти эффекты не имеют важного значения, если между серверами передаются небольшие объемы данных. Однако, последовательная вставка курсором большого кол-ва записей может занимать довольно длительное время, добавляя ко всему прочему блокировку таблиц на все это время. В качестве примера можно попробовать передать большой справочник, состоящий примерно из 130 000 строк.


insert into DestServer.Northwind.dbo.Dictionary
select *
from Dictionary

На тестовом сервере это привело к 130 000 запросам на вставку и заняло примерно 370 секунд. В дальнейшем это значение будет сопоставлено с результатами работы технологии Pull.

Технология Pull
При использовании технологии pull запрос на вставку данных выполняется на целевом сервере. При этом происходит «втягивание» данных от сервера-источника.
Такой запрос, выполняющийся на целевом сервере, выглядит так:


insert into Customers
select *
from SourceServer.Northwind.dbo.Customers

Если воспользоваться SQL Server Profiler, то на целевом сервере он будет выполняться в неизменном виде -


insert into Customers
select *
from SourceServer.Northwind.dbo.Customers
go

На сервере-источнике при этом будет выполнен следующий запрос -


set implicit_transactions on 
go

declare @P1 int
set @P1=1
declare @P2 bigint
set @P2=8381016049028902
exec sp_getschemalock @P1 output, @P2 output, 
N'"Northwind"."dbo"."Customers"'
select @P1, @P2
go

declare @P1 int
set @P1=2
exec sp_prepexec @P1 output, NULL, 
N'SELECT Col1028,Col1027,Col1026,Col1025,Col1024,
Col1023,Col1022,Col1021,Col1020,Col1019,Col1018 FROM 
(SELECT Tbl1001."CustomerID" Col1018,
        Tbl1001."CompanyName" Col1019,
        Tbl1001."ContactName" Col1020, 
        Tbl1001."ContactTitle" Col1021,
        Tbl1001."Address" Col1022, 
        Tbl1001."City" Col1023,
        Tbl1001."Region" Col1024,
        Tbl1001."PostalCode" Col1025,
        Tbl1001."Country" Col1026,
        Tbl1001."Phone" Col1027,
        Tbl1001."Fax" Col1028 
 FROM "Northwind"."dbo"."Customers" Tbl1001) Qry1029'
select @P1
go

exec sp_unprepare 2
go

exec sp_releaseschemalock 1
go

IF @@TRANCOUNT > 0 ROLLBACK TRAN
Go

set implicit_transactions off 
go

Здесь видно, что при такой технологии курсор не используется. Вместо этого применяется пакетная вставка записей – всех за один раз. Это же доказывает нам и использование триггера. После выполнения запроса таблица CallsCount содержит всего одну запись -

InstertedCount	TotalRows
3	3

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


insert into Dictionary
select *
from SourceServer.Northwind.dbo.Dictionary

Если попытаться замерить время, которое сервер тратит на вставку записей таким образом на примере большого справочника из 130 000 строк, то следующий запрос на том же сервере выполняется 30 секунд, что в 12 раз быстрее, чем при использовании технологии Push.

Сравнительные данные
В следующей таблице приведены сравнительные данные этих двух технологий

	Push	Pull
Запрос	Выполняется на сервере-источнике. Пример: insert into DestServer.Northwind.dbo.Customers select * from Customers	Выполняется на сервере-приемнике. Пример: insert into Customers select * from SourceServer.Northwind.dbo.Customers
Вставка	Курсором, по одной записи.	Пакетно. Все записи за один запрос.
Триггер	Срабатывает для каждой вставляемой записи.	Срабатывает один раз для всех вставляемых записей.
Скорость	Низкая, так как записи вставляются одна за одной.	Высокая (в десятки раз быстрее), так как все записи вставляются за один запрос.

Заключение
Из всего вышесказанного следует, что технология pull обеспечивает более высокую производительность, чем технология push и является более предпочтительной.
Я с радостью рассмотрю любые комментарии и вопросы по данной тематике.
Мои координаты доступны на сайте www.msmirnov.ru
Михаил Смирнов
Руководитель проектов.

воскресенье, 22 апреля 2007 г. - www.msmirnov.ru

Технологии Push и Pull при работе с linked servers в Microsoft SQL Server

воскресенье, 22 апреля 2007 г. - www.msmirnov.ru