[툴소개]SSMS Tools Pack (freeware)
SSMS 사용하는데 유용한 툴이 있어 공유드립니다.
아래의 사이트에 자세하게 기능에 대해서 자세히 설명되어 있습니다. 그 중에서 제가 맘에 들었던 기능 간단히 소개드립니다. SQL Server 2011 CTP1에서도 사용 가능합니다.
색을 넣어두면 개발서버인 줄 알고 실수하는 경우를 줄일 수 있지 않을까합니다.
아래는 같은 서버에 인스턴스명으로 접속(좌)한 경우와 (local)(우)로 접속했을 때의 차이를 보여줍니다.
출처 : http://www.ssmstoolspack.com/
SQL Server 2012 (Denali) 신규 기능 Sequence 소개
오라클로 DB를 입문했던 저로써는 매우 반가운 소식이였습니다. 그래서 가볍게 테스트 해 본 결과 공유드립니다.
1. 기본 문법
CREATE SEQUENCE [schema_name . ] sequence_name
[ <sequence_property_assignment> [ ,…®|n ] ]
[ ; ]
<sequence_property_assignment>::=
{
[ AS { built_in_integer_type | user-defined_integer_type } ]
| START WITH <constant>
| INCREMENT BY <constant>
| { MINVALUE <constant> | NO MINVALUE }
| { MAXVALUE <constant> | NO MAXVALUE }
| { CYCLE | NO CYCLE }
| { CACHE [<constant> ] | NO CACHE }
}
NEXT VALUE FOR [ database_name . ] [ schema_name . ] sequence_name
[ OVER (<over_order_by_clause>) ]
2. 시퀀스 객체를 활용한 채번 방식/옵션 변경에 따른 채번 성능 테스트 (참고. 개인 PC에서 테스트 진행함)
2-1. NOCACHE 에서 채번 테스트
CREATE SEQUENCE SeqNo_nocache
START WITH 1
INCREMENT BY 1
no Cache;
GO
declare
@seqval int = 0
, @Cnt int = 0;
select getdate();
while (@Cnt <= 1000000)
begin
SELECT @seqval = NEXT VALUE FOR SeqNo_nocache;
set @Cnt = @Cnt + 1;
end
select GETDATE();
Result> 약 21초
2010-12-17 20:28:32.870
2010-12-17 20:28:53.127
2-2. CACHE 10/50/100 상태에서 채번 테스트
CREATE SEQUENCE SeqNo_Cache10
START WITH 1
INCREMENT BY 1
Cache 10;
GO
SELECT * FROM sys.sequences WHERE name = 'SeqNo_Cache10'
declare
@seqval int = 0
, @Cnt int = 0;
select getdate();
while (@Cnt <= 1000000)
begin
SELECT @seqval = NEXT VALUE FOR SeqNo_Cache100;
set @Cnt = @Cnt + 1;
end
select GETDATE();
Result> Cache 10 : 약9초
2010-12-17 20:31:40.393
2010-12-17 20:31:49.953
Result> Cache 50 : 약9초
2010-12-17 20:32:41.960
2010-12-17 20:32:50.643
Result> cache 100 : 약9초
2010-12-17 20:34:24.297
2010-12-17 20:34:33.090
2-3. sp_sequence_get_range 사용하여 채번 (하나씩)
declare
@seqval int = 0
, @Cnt int = 0
, @range_first_value sql_variant
, @range_first_value_output sql_variant;
select getdate();
while (@Cnt <= 1000000)
begin
EXEC sp_sequence_get_range
@sequence_name = N'seqno_cache10'
, @range_size = 1
, @range_first_value = @range_first_value_output OUTPUT ;
set @Cnt = @Cnt + 1;
end
select GETDATE();
Result> 54초
2010-12-17 20:38:00.330
2010-12-17 20:38:54.593
2-4. sp_sequence_get_range 사용하여 채번 (필요한 범위를 한방에~ )
declare
@seqval int = 0
, @Cnt int = 0
, @range_first_value sql_variant
, @range_first_value_output sql_variant
, @range_last_value_output sql_variant;
select getdate();
EXEC sp_sequence_get_range
@sequence_name = N'seqno_cache10'
, @range_size = 1000000
, @range_first_value = @range_first_value_output OUTPUT
, @range_last_value = @range_last_value_output output;
select @range_first_value_output, @range_last_value_output
select GETDATE();
Result> 0초
2010-12-17 20:41:02.427
2010-12-17 20:41:02.427
3. 하나의 쿼리에서 동일한 시퀀스를 여러번 호출하면 어떻게 될까?
select next value for SeqNo_Cache10, next value for SeqNo_Cache10
Result> 동일한 값이 나온다.
5000151 5000151
4. 시퀀스에 over 구문은 왜 있을까?
4-1. over 절 사용 안하는 경우
select
x.name
, next value for SeqNo_Cache10 as seqNo
from (select top 100 name from sys.columns) x
Result> 이름이 정렬되어지지 않은 상태로 채번되어진다.
4-2. over절 사용하는 경우 (update, merge 구문과 같이 사용 불가함)
select
x.name
, next value for SeqNo_Cache10 over (order by name) as seqNo
from (select top 100 name from sys.columns) x
Result> 이름 정렬순으로 낮은 순서로 채번된다.
5. 시퀀스 번호 재시작하기
ALTER SEQUENCE dbo.Seq1 RESTART WITH 1;
6. 테스트하고 느낀 점
> 캐싱 개수에 상관없이 절대량 처리 속도는 거의 비슷하다. 동시성만 고려하여 캐싱 수치를 적절히 잡으면 될 것 같다.
> 캐싱을 사용할 경우 DB서버를 재시작하면 캐싱된 번호가 사라져버릴꺼라고 생각했는데, 유지되어진다. (좋다~ ^^)
> 대량 범위를 확보하고 싶은 경우 프로시져를 반드시 쓰기를 추천합니다.
> Identity와 Sequence를 동시에 사용할 수 있다니, 기대만빵입니다. 어느 것을 사용할지는 사용자의 선택~
7. Itzik Ben-Gan Blog에 Sequence 에 대한 퀴즈도 있으니 관심 있으신 분은 참고하세요~
*. 참고 자료
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx
http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/75978/TSQL-Challenge-with-Denali-s-Sequence-Object.aspx
http://sqlserverpedia.com/blog/sql-server-bloggers/2-of-my-favorite-new-t-sql-features-in-sql-server-denali-sql-11/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+sqlserverpedia+%28SQLServerPedia%29&utm_content=Google+Reader
블로그의 "일간 방문자수 보여주기" 구현 사례
이 글을 작성한 목적은 일정 기간동안 컬럼의 값을 증가시키다가 일정기간마다 초기화가 필요한 경우에 활용하면 좋을 듯 하여
블로그 일간 방문자수 보여주기 기능을 사례로 들어서 테스트해 본 것입니다.
원활한 테스트를 위해서 “일간 방문자수 보여주기” 기능에 대한 정의는 아래와 같이 단순화하겠습니다.
1. 임의의 사용자가 블로그에 방문하면 일간 방문자수를 +1 증감시켜준다.
2. 날짜가 변경되면 일간 방문자수를 0으로 초기화되며, 새로 방문자가 올 경우 1로 보여준다.
3. 이전날짜의 방문자수를 따로 보여주지 않는다.
제가 생각해 본 이 방식의 장점은 아래와 같습니다.
1. 매일 0시에 대량의 업데이트를 할 필요가 없다.
2. 접근이 없는 블로그에 대해서는 불필요한 업데이트를 하지 않아도 된다.
3. 웹서비스의 경우 불특정 다수를 상대로 서비스하기 때문에, 사용자가 많아짐에 따라서 순간적인 병목이 발생할 수 있는데요.
필요한 경우에만 데이터를 업데이트 함으로써 부하를 분산할 수 있습니다.
그럼 이제 테스트를 진행하도록 하겠습니다.
use tempdb
go
-- @@ 예제 테이블 생성 및 데이터 추가
drop table BlogMainTable
create table BlogMainTable (
blogId varchar(50) -- 블로그이름
, VisitorCount int -- 오늘 블로그에 방문한 방문자수
, lastModified datetime -- 블로그 최종데이터 변경일자
)
insert into BlogMainTable values ( 'Blog_01', 5, getdate() - 1 ) -- 어제 50명이 방문하고 오늘 방문자가 없는 블로그
insert into BlogMainTable values ( 'Blog_02', 10, getdate()) -- 오늘 10명이 방문한 블로그
-- @@ 어제 방문자가 5명이였고, 오늘 방문자가 없는 블로그 조회 쿼리
select
blogId
, VisitorCount as VisitorCount_real
, case
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) < DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then 1 -- 최종변경일시가 어제라면 1을 보여줌
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) = DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then VisitorCount -- 최종변경일시가 오늘이면 +1된 건수를 보여줌
else 0
end as VisitorCount_view
, lastModified
from BlogMainTable
where blogId = 'Blog_01'
-- @@ 오늘 10명이 방문한 블로그
select
blogId
, VisitorCount as VisitorCount_real
, case
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) < DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then 1 -- 최종변경일시가 어제라면 1을 보여줌
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) = DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then VisitorCount -- 최종변경일시가 오늘이면 +1된 건수를 보여줌
else 0
end as VisitorCount_view
, lastModified
from BlogMainTable
where blogId = 'Blog_02'
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- @@ 실제 적용시에는 업데이트와 조회를 한방에 처리하면 좀 더 비용을 절감할 수 있습니다.
-- @@ 업데이트와 조회를 한번에 처리하는 예제는 아래와 같습니다.
declare
@blogId varchar(50)
, @VisitorCount_before int = 0
, @VisitorCount_view int = 0
, @lastModified datetime
-- 현재 테이블에 저장된 데이터 확인
select * from BlogMainTable where blogId = 'Blog_01'
-- 데이터 변경하기
update bm
set
@blogId = blogId
, @VisitorCount_view = VisitorCount = case
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) < DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then 1 -- 지난달의 기록이면 노출
when DATEADD( day, DATEDIFF(day, 0, lastModified), 0) = DATEADD( day, DATEDIFF(day, 0, GETDATE()), 0) then VisitorCount + 1 -- 이번달의 Sum이면 합계금액 노출
else 0
end
, @lastModified = lastModified = getdate()
from BlogMainTable as bm
where blogId = 'Blog_01'
-- 업데이트된 결과 확인(Blog_01의 경우 어제일자이므로 1로 변경됨)
select @blogId as blogId , @VisitorCount_view as VisitorCount_view, @lastModified as lastModified
