/**********************************************************************************************
-- Title : [2k] 채번 구현 및 채번 분리에 따른 스트레스 테스트
-- Reference : mcpworld.com
-- Key word : 채번
**********************************************************************************************/
use tempdb
go
/*
** 채번 작업을 분리하지 않은 경우
*/
if object_id('OrderDetails_1') is not null
drop table OrderDetails_1
if object_id('Orders_1') is not null
drop table Orders_1
if object_id('I_OrderDetails_1') is not null
drop proc I_OrderDetails_1
if object_id('I_Orders_1') is not null
drop proc I_Orders_1
go
create table Orders_1
( OrdDate smalldatetime not null
, SlipNo int not null
, CustomerID char(5) not null
, Amount money not null
, note varchar(500)
, constraint PK_Orders_1 primary key (OrdDate,SlipNO)
)
go
create table OrderDetails_1
( ODNo int identity primary key
, OrdDate smalldatetime not null
, SlipNo int not null
, ProductID char(5) not null
, Price money not null
, Quanity int not null
)
go
alter table [dbo].[OrderDetails_1]
add constraint [FK_OrderDetails_T_Orders_T] foreign key
( [OrdDate],
[SlipNo]
) references [dbo].[Orders_1]
( [OrdDate],
[SlipNo]
)
go
--주문 테이블에 자료를 삽입하는 저장 프로시저는
--주문 상세 테이블에서 사용할 @SlipNo를 output 매개변수로
--반환 받을 수 있도록 처리.
create proc I_Orders_1
@OrdDate smalldatetime,
@CustomerID char(5),
@Amount money,
@note varchar(500),
@SlipNo int output
as
set nocount on
begin tran
select @SlipNo = isnull(max(SlipNo),0) + 1
from Orders_1 with (XLOCK)
where OrdDate = @OrdDate
insert into Orders_1
values( @OrdDate, @SlipNo, @CustomerID, @Amount, @note)
commit tran
go
create proc I_OrderDetails_1
@OrdDate smalldatetime,
@SlipNo int,
@ProductID char(5),
@Price money,
@Quantity int
as
insert into OrderDetails_1
values( @OrdDate, @SlipNo, @ProductID, @Price, @Quantity )
go
--쿼리 분석기에서 주문 등록
declare @mySlipNo int
begin tran
exec I_Orders_1 '20010601','A01',60000,'', @mySlipNo output
exec I_OrderDetails_1 '20010601', @mySlipNo, 'JK04', 5000, 2
exec I_OrderDetails_1 '20010601', @mySlipNo, 'JJ01', 5000, 4
exec I_OrderDetails_1 '20010601', @mySlipNo, 'JK02', 6000, 5
commit tran
go
select * from Orders_1
select * from orderdetails_1
/*
** 채번 작업을 분리한 경우
*/
if object_id('OrderSlipNosSingleRow') is not null
drop table OrderSlipNosSingleRow
if object_id('OrderDetails_s') is not null
drop table OrderDetails_s
if object_id('Orders_s') is not null
drop table Orders_s
if object_id('Get_OrderSlipNo_s') is not null
drop proc Get_OrderSlipNo_s
if object_id('I_Orders_s') is not null
drop proc I_Orders_s
if object_id('I_OrderDetails_s') is not null
drop proc I_OrderDetails_s
go
create table OrderSlipNosSingleRow
( OrdDate smalldatetime not null primary key
, SlipNo int not null
)
go
create table Orders_s
( OrdDate smalldatetime not null
, SlipNo int not null
, CustomerID char(5) not null
, Amount money not null
, note varchar(500)
, constraint PK_Orders_s primary key (OrdDate,SlipNO)
)
go
create table OrderDetails_s
( ODNo int identity primary key
, OrdDate smalldatetime not null
, SlipNo int not null
, ProductID char(5) not null
, Price money not null
, Quanity int not null
)
go
alter table [dbo].[OrderDetails_s]
add
constraint [FK_OrderDetails_s_Orders_s] foreign key
( [OrdDate],
[SlipNo]
) references [dbo].[Orders_s]
( [OrdDate],
[SlipNo]
)
go
--OrderSlipNosSingleRow 채번 테이블을 이용하는 채번 저장 프로시저
create proc Get_OrderSlipNo_s
@OrdDate smalldatetime,
@SlipNo int output
as
ErrTrap:
update OrderSlipNosSingleRow set @SlipNo = SlipNo = SlipNo + 1 where OrdDate = @OrdDate
if @@ROWCOUNT = 0
begin
set @SlipNo = 1
insert into OrderSlipNosSingleRow values( @OrdDate, @SlipNo)
-- 2727 primary key, UNIQUE 제약 조건 에러 2601 UNIQUE KEY 에러
if @@ERROR = 2627 OR @@ERROR = 2601
goto ErrTrap
end
go
-- 주문 삽입 저장 프로시저
create proc I_Orders_s
@OrdDate smalldatetime,
@SlipNo int,
@CustomerID char(5),
@Amount money,
@note varchar(500)
as
insert into Orders_s
values( @OrdDate, @SlipNo, @CustomerID, @Amount, @note)
go
--주문 상세 삽입 저장 프로시저
create proc I_OrderDetails_s
@OrdDate smalldatetime,
@SlipNo int,
@ProductID char(5),
@Price money,
@Quantity int
as
insert into OrderDetails_s
values( @OrdDate, @SlipNo, @ProductID, @Price, @Quantity )
go
-- 쿼리 분석기에서 주문 등록
declare @mySlipNo int
exec Get_OrderSlipNo_s '20010601', @mySlipNo output
if @
mySlipNo = -1
begin
-- raiserror('채번 작업중 오류가 발생하였습니다. ',16,1)
print '채번 작업중 오류가 발생하였습니다.'
return
end
begin tran
exec I_Orders_s '20010601', @mySlipNo, 'A01', 60000, ''
exec I_OrderDetails_s '20010601', @mySlipNo, 'JK04', 5000, 2
exec I_OrderDetails_s '20010601', @mySlipNo, 'JJ01', 5000, 4
exec I_OrderDetails_s '20010601', @mySlipNo, 'JK02', 6000, 5
commit tran
go
select * from OrderSlipNosSingleRow
select * from OrderDetails_s
select * from Orders_s
delete from OrderSlipNosSingleRow
delete from OrderDetails_s
delete from Orders_s
/*
** 스트레스 테스트에서 호출할 저장 프로시저
*/
if object_id('Bat_I_Orders_1') is not null
drop proc Bat_I_Orders_1
if object_id('Bat_I_Orders_s') is not null
drop proc Bat_I_Orders_s
go
-- 채번 트랜잭션을 분리하지 않은 경우의 일괄 삽입 저장 프로시저
create proc Bat_I_Orders_1
@CustomerID char(5)
as
declare @i int
declare @mySlipNo int
set @i = 1
while @i <= 10
begin
begin tran
exec I_Orders_1 '20010601', @CustomerID, 60000,'', @mySlipNo output
waitfor delay '00:00:01'
exec I_OrderDetails_1 '20010601', @mySlipNo, 'JK04', 5000, 2
exec I_OrderDetails_1 '20010601', @mySlipNo, 'JJ01', 5000, 4
exec I_OrderDetails_1 '20010601', @mySlipNo, 'JK02', 6000, 5
commit tran
set @i = @i + 1
end
go
-- 채번 트랜잭션을 분리한 경우의 일괄 삽입 저장 프로시저
create proc Bat_I_Orders_s
@CustomerID char(5)
as
declare @i int
declare @mySlipNo int
set @i = 1
while @i <= 10
begin
exec Get_OrderSlipNo_s '20010601', @mySlipNo output
if @mySlipNo = -1
begin
raiserror(' 채번 작업중 오류가 발생하였습니다. ',16,1)
return
end
begin tran
exec I_Orders_s '20010601', @mySlipNo,@CustomerID, 60000, ''
waitfor delay '00:00:01'
exec I_OrderDetails_s '20010601', @mySlipNo, 'JK04', 5000, 2
exec I_OrderDetails_s '20010601', @mySlipNo, 'JJ01', 5000, 4
exec I_OrderDetails_s '20010601', @mySlipNo, 'JK02', 6000, 5
commit tran
set @i = @i + 1
end
go
-- 스트레스 테스트를 위해 OSQL을 이용한 배치 파일은
-- 해당 디렉토리 하위 디렉토리에 있습니다.
-- Server Name, userID, Passward를 환경에 맞게 바꾸어 실행하시면 됩니다.