#title Service Broker를 이용하여 응답속도 높이기
[[TableOfContents]]

==== 개요 ====
비동기 메시지 처리는 응용 프로그램의 응답속도를 높이는데 사용될 수 있다. 즉, 모든 처리가 끝나고 클라이언트에 제어권을 넘기는 방식이 아닌 필요한 부분까지만을 서버에서 제어권을 가지고 있고 나머지는 Service Broker에 맞겨 비동기 처리를 하는 것이다. 

==== 시나리오 ====
attachment:sssb05.jpg
{{{
CREATE TABLE dbo.Cust
	(
	CustID varchar(20) NOT NULL,
	PassWD varchar(20) NOT NULL,
	CurrentConnYN bit NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Cust ADD CONSTRAINT
	PK_Cust PRIMARY KEY CLUSTERED 
	(
	CustID
	) 
GO
CREATE TABLE dbo.Connect_History
	(
	CustID varchar(20) NOT NULL,
	DisConnectDT datetime NOT NULL,
	ConnectDT datetime NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Connect_History ADD CONSTRAINT
	PK_Connect_History PRIMARY KEY CLUSTERED 
	(
	CustID,
	DisConnectDT,
	ConnectDT
	) 
GO
ALTER TABLE dbo.Connect_History ADD CONSTRAINT
	FK_Connect_History_Cust FOREIGN KEY
	(
	CustID
	) REFERENCES dbo.Cust
	(
	CustID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
}}}

'''문제점:'''
Connect_History 테이블은 클라이언트에게 서비스를 제공하는데는 전혀 관련이 없는 내부용으로 쓰이는 이력관리 테이블이다. 하지만 실제로 로그인을 할 때에는 Connect_History테이블에 Insert를 해야 하고, 로그아웃을 할 때는 Update를 해야 한다. 그러므로 트랜잭션이 길어져 SP호출시 응답시간이 길어지게 된다. 

==== 일반적으로 T-SQL을 이용한 SP는 소스 ====
{{{
insert Cust values('yasicom', '1234', 0)
go

create proc usp_connect
	@cust_id varchar(20)
,	@pass_wd varchar(20)
,	@conn_yn bit = 1 --1:접속, 0:접속종료
as
begin
	declare @rs bit;

	--현재 고객의 ID, PW를 대조하고, 현재 접속중인지 파악
	--if CurrentConnYN = 1 then 접속 else 접속종료
	--select 해서 ID, PW가 존재하면 다음에 업데이트 치는 그런거 하지 말기를…
	--update도 조건에 맞는 행이 있어야 업데이트하므로 논리적으로는 update만 해도 아무 문제 없다. 

	update Cust
	set
		@rs = CurrentConnYN = case when @conn_yn = 1 then 1 else 0 end
	from Cust
	where CustID = @cust_id
	and PassWD = @pass_wd
	and CurrentConnYN = case when @conn_yn = 1 then 0 else 1 end;

	--접속이력에Insert, update
	if @rs is null 
		return isnull(@rs, 0);
	else if @rs = 1
	begin
		insert Connect_History(CustID, DisConnectDT, ConnectDT)
		values(@cust_id, '99991231', getdate());
	end else 
	begin
		update Connect_History
		set DisConnectDT = getdate()
		where CustID = @cust_id
		and DisConnectDT = '99991231'
		and @conn_yn = 0
	end

	return isnull(@rs, 0);

end
go

--2회실행시키면첫번째는1을리턴하고두번째는0을리턴할것이다. 
declare @rs1 int
exec @rs1 = usp_connect 'yasicom', '1234', 1
select @rs1

--접속종료
declare @rs2 int
exec @rs2 = usp_connect 'yasicom', '1234', 0
select @rs2
}}}

아마도 이런 시나리오 일 것이다. 그러나 앞서서 말한 것처럼 이력을 관리하기 위해서는 그 만큼의 리소스가 낭비된다. 또한 트랜잭션의 수와 비례하게 이력관리 테이블이 차지하는 저장 공간도 늘어난다. 공간이야 어쩔 수 없지만 성능까지 어쩔 도리가 없었다. 그러나 SQL Server 2005에 새롭게 선보인 Service Broker를 이용하면 이력관리 테이블에 메시지만을 던져 놓고 처리를 하던지 말던지 제어권을 클라이언트에게 넘주므로 응답속도를 향상시킬 수 있다. 물론 대규모에서..이제 실제로 Service Broker를 이용해서 비동기 처리를 해보도록 하겠다. 코딩량이 많으므로 예외처리는 생략한다. 

{{{
create message type conn_history_msg
validation = none;

create contract conn_history_contract
(conn_history_msg sent by initiator);
go

create proc usp_sb_conn
as
return 0;
go

create queue sendqueue;
create queue receivequeue
with activation
(
	status = off
,	procedure_name = usp_sb_conn
,	max_queue_readers = 100
,	execute as self
);

create service send_service
  on queue sendqueue (conn_history_contract);

create service receive_service
  on queue receivequeue (conn_history_contract);
go


alter proc usp_connect
	@cust_id varchar(20)
,	@pass_wd varchar(20)
,	@conn_yn bit = 1 --1:접속, 0:접속종료
as
begin
	declare @rs bit;

	update Cust
	set
		@rs = CurrentConnYN = case when @conn_yn = 1 then 1 else 0 end
	from Cust
	where CustID = @cust_id
	and PassWD = @pass_wd
	and CurrentConnYN = case when @conn_yn = 1 then 0 else 1 end;

	--여기부터 수정(이전소스는 그대로)
	--접속 이력에 메시지 던짐
	/*
	if @rs is null 
	begin
		return isnull(@rs, 0);
	end
	else if @rs = 1
	begin
		insert Connect_History(CustID, DisConnectDT, ConnectDT)
		values(@cust_id, '99991231', getdate());
	end else
	begin
		update Connect_History
		set DisConnectDT = getdate()
		where CustID = @cust_id
		and DisConnectDT = '99991231'
		and @conn_yn = 0
	end
	*/
	declare 
		@handle uniqueidentifier
	,	@msg nvarchar(max);

	set @msg = N'
	<root>
		<rs>' + isnull(convert(varchar, @rs), '') + '</rs>
		<custid>' + @cust_id + '</custid>
		<currdt>' + convert(varchar, getdate(), 121) + '</currdt>
		<connyn>' + convert(varchar, @conn_yn) + '</connyn>
	</root>
	';

	begin dialog conversation @handle
	from service send_service
	to service 'receive_service'
	on contract conn_history_contract
	with encryption = off;

	send on conversation @handle
	message type conn_history_msg(@msg); 


	return isnull(@rs, 0);

end
go

alter proc usp_sb_conn
as
begin
	declare 
		@msg xml
	,	@handle uniqueidentifier
	,	@rs bit
	,	@cust varchar(20)
	,	@currdt varchar(30)
	,	@connyn bit;

	while(1=1)
	begin
		receive top(1) 
			@msg = convert(xml, convert(nvarchar(max), message_body))
		,	@handle = conversation_handle
		from receivequeue

		if @@rowcount = 0
		begin
			end conversation @handle;
			break;
			
		end

		select
			@rs = x.item.value('rs[1]', 'bit')
		,	@cust = x.item.value('custid[1]', 'varchar(20)')
		,	@currdt = x.item.value('currdt[1]', 'varchar(30)') 
		,	@connyn = x.item.value('connyn[1]', 'bit') 
		from @msg.nodes('/root') as x(item)

		if @rs is null 
		begin
			return isnull(@rs, 0);
		end
		else if @rs = 1
		begin
			insert Connect_History(CustID, DisConnectDT, ConnectDT)
			values(@cust, '99991231', @currdt);
		end else
		begin
			update Connect_History
			set DisConnectDT = @currdt
			where CustID = @cust
			and DisConnectDT = '99991231'
			and @connyn = 0
		end
	end

	return isnull(@rs, 0)
end
go
}}}

이제 실제로 실행해 보자.
{{{
--Connect_History을비운다.
--접속중인지도모르니..
truncate table Connect_History

--접속해본다.
--Connect_History 테이블에는이력이반영되지않았으나접속성공/실패여부가리턴되었다. 
--즉, 메시지를보내고끝낸것이다. 
declare @rs int
exec @rs = usp_connect 'yasicom', '1234', 0
exec @rs = usp_connect 'yasicom', '1234', 1
select @rs

--큐조회
select top(100) convert(xml, convert(nvarchar(max), message_body))
from receivequeue with (nolock);
go
}}}
attachment:sssb06.jpg

{{{
--큐status=on으로변경
alter queue receivequeue
with activation
(
	status = on --off에서 on으로변경
,	procedure_name = usp_sb_conn
,	max_queue_readers = 100
,	execute as self
);
go

--큐조회
--조금있으면 Servic Broker가 usp_sb_conn을 호출하여 접속이력에 대한 처리를 할 것입니다. 
--SQL Server는 Server의 자원상황을 보고 알아서 처리한다고 합니다. 어쨌든 최대한 빨리..
select top(100) convert(xml, convert(nvarchar(max), message_body))
from receivequeue with (nolock);

--Connect_History조회
select * from Connect_History;
go
}}}
attachment:sssb07.jpg

==== Service Broker 적용 분야 ====
 * 긴 트랜잭션의 분할 수행
 * 서비스에 영향을 끼치지 않는 부분을 처리하거나 (이력, 로그성 데이터 처리)
 * 대규모 분산 환경

----
GOOD~  ... 만 넣으니 쫌... 하는군
이름과 번호를 왜 넣어야 하는겨.. ㅎㅎ -- 엠켁 2012-05-11 13:54:18
----
ㅋㅋ
익명으로 해놨더니 누가 자꾸 나쁜짓을 해서리.. -- 이재학 2012-05-11 14:55:59