2 시나리오 #
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호출시 응답시간이 길어지게 된다.
3 일반적으로 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
--큐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