http://cafe.naver.com/sqlmvp/371
-- test 목적
-- 5일간 데이터만 보관한다.
-- delete dml 의 비효율을 보완하기 위한 방법
use master
go
if exists (select * from master.dbo.sysdatabases where name ='split')
begin
alter database split set single_user with rollback immediate
drop database split
end
go
create database split
go
use split
go
-- 이하 실행 및 테스트
-- manage table
if object_id ('dbo.d_manage') is not null
drop table dbo.d_manage
go
create table dbo.d_manage(type varchar(100), i_dt smalldatetime)
go
create unique clustered index cl_d_manage on d_manage(i_dt, type)
go
insert into dbo.d_manage values('day', '20081201')
insert into dbo.d_manage values('day', '20081202')
insert into dbo.d_manage values('day', '20081203')
insert into dbo.d_manage values('day', '20081204')
insert into dbo.d_manage values('day', '20081205')
go
if object_id ('dbo.d_20081201') is not null
begin
drop table dbo.d_20081201
drop table dbo.d_20081202
drop table dbo.d_20081203
drop table dbo.d_20081204
drop table dbo.d_20081205
end
-- manage target table
create table dbo.d_20081201(clubid varchar(100) not null, i_dt smalldatetime not null check (i_dt = '20081201'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081202(clubid varchar(100) not null, i_dt smalldatetime not null check (i_dt = '20081202'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081203(clubid varchar(100) not null, i_dt smalldatetime not null check (i_dt = '20081203'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081204(clubid varchar(100) not null, i_dt smalldatetime not null check (i_dt = '20081204'), type varchar(100) not null, value varchar(100) not null)
create table dbo.d_20081205(clubid varchar(100) not null, i_dt smalldatetime not null check (i_dt = '20081205'), type varchar(100) not null, value varchar(100) not null)
go
-- test data 입력
set nocount on
insert into dbo.d_20081201 values ('1', '20081201', 'a', rand() * 10)
insert into dbo.d_20081201 values ('2', '20081201', 'a', rand() * 10)
insert into dbo.d_20081201 values ('1', '20081201', 'b', rand() * 10)
insert into dbo.d_20081201 values ('2', '20081201', 'b', rand() * 10)
insert into dbo.d_20081202 values ('1', '20081202', 'a', rand() * 10)
insert into dbo.d_20081202 values ('2', '20081202', 'a', rand() * 10)
insert into dbo.d_20081202 values ('1', '20081202', 'b', rand() * 10)
insert into dbo.d_20081202 values ('2', '20081202', 'b', rand() * 10)
insert into dbo.d_20081203 values ('1', '20081203', 'a', rand() * 10)
insert into dbo.d_20081203 values ('2', '20081203', 'a', rand() * 10)
insert into dbo.d_20081203 values ('1', '20081203', 'b', rand() * 10)
insert into dbo.d_20081203 values ('2', '20081203', 'b', rand() * 10)
insert into dbo.d_20081204 values ('1', '20081204', 'a', rand() * 10)
insert into dbo.d_20081204 values ('1', '20081205', 'a', rand() * 10)
insert into dbo.d_20081204 values ('2', '20081204', 'a', rand() * 10)
insert into dbo.d_20081204 values ('1', '20081204', 'b', rand() * 10)
insert into dbo.d_20081204 values ('2', '20081204', 'b', rand() * 10)
insert into dbo.d_20081205 values ('1', '20081205', 'a', rand() * 10)
insert into dbo.d_20081205 values ('2', '20081205', 'a', rand() * 10)
insert into dbo.d_20081205 values ('1', '20081205', 'b', rand() * 10)
insert into dbo.d_20081205 values ('2', '20081205', 'b', rand() * 10)
go
create unique clustered index cl_d_20081201 on d_20081201 (clubid, type, i_dt)
create unique clustered index cl_d_20081202 on d_20081202 (clubid, type, i_dt)
create unique clustered index cl_d_20081203 on d_20081203 (clubid, type, i_dt)
create unique clustered index cl_d_20081204 on d_20081204 (clubid, type, i_dt)
create unique clustered index cl_d_20081205 on d_20081205 (clubid, type, i_dt)
go
create view d
as
select * from dbo.d_20081201
union all select * from dbo.d_20081202
union all select * from dbo.d_20081203
union all select * from dbo.d_20081204
union all select * from dbo.d_20081205
go
-- 유저 데이터
set statistics io on
set statistics profile on
set statistics io off
set statistics profile off
-- #################################################################################
-- 원하는 쿼리 (원하는 성능이 나오는가? : OK 해당 테이블만 정확히 억세스 한다.)
-- #################################################################################
select * from dbo.d where i_dt = cast('20081204' as smalldatetime) and type ='a' and clubid = '1'
sp_helptext 'dbo.d '
-- 결과
-- 1 2008-12-04 00:00:00 a 8.14244
-- 실행계획
-- rows executes stmttext
1 1 select * from dbo.d where i_dt = cast('20081204' as smalldatetime) and type ='a' and clubid = '1' -- 결과 -- 1 2008-12-04 00:00:00 a 8.14244 -- 실행계획 -- rows executes stmttext -- 1 1 select * from d where i_dt = cast('20081204' as smalldatetime) an
1 1 |--Compute Scalar(DEFINE:([d_20081204].[clubid]=[d_20081204].[clubid], [d_20081204].[i_dt]=[d_20081204].[i_dt], [d_20081204].[type]=[d_20081204].[type], [d_20081204].[value]=[d_20081204].[value]))
1 1 |--Clustered Index Seek(OBJECT:([split].[dbo].[d_20081204].[cl_d_20081204]), SEEK:([d_20081204].[clubid]='1' AND [d_20081204].[type]='a') ORDERED FORWARD)
-- #################################################################################
-- #################################################################################
-- 아래 프로시저를 이용해 동적으로 view 를 만들어 줘야 한다.
--1 batch 작업으로 일간 batch 작업을 시작 한다.
-- 오늘은 20081206 이라고 가정한다.
-- 호출시 테이블을 만들어 주는 프로시저
go
if object_id('dbo.usp_d_maketable') is not null
drop proc dbo.usp_d_maketable
go
create proc dbo.usp_d_maketable
(@adddate smalldatetime)
as
declare @sql varchar(8000)
, @adddatechar char(8)
set @adddatechar = convert(char(8), @adddate, 112)
select @adddatechar
-- 이중 실행에 따른 오류 처리
if exists( SELECT *
FROM sysobjects
where name = 'd_'+ @adddatechar )
begin
set @sql = 'drop table dbo.d_'+ @adddatechar
exec (@sql)
end
set @sql = 'create table dbo.d_'+ @adddatechar +' (clubid varchar(100) not null, i_dt smalldatetime not null check (i_dt = '''+@adddatechar+'''), type varchar(100) not null, value varchar(100) not null)'
exec (@sql)
go
-- 호출시 인덱스를 만들어 주는 프로시저
if object_id('dbo.usp_d_makeindex') is not null
drop proc dbo.usp_d_makeindex
go
create proc dbo.usp_d_makeindex
(@adddate smalldatetime)
as
declare @sql varchar(8000)
, @adddatechar char(8)
set @adddatechar = convert(char(8), @adddate, 112)
select @adddatechar
set @sql = 'create unique clustered index cl_d_'+ @adddatechar +' on d_'+ @adddatechar +' (clubid, i_dt, type)'
exec (@sql)
go
-- 호출시 view 를 변경시켜줄 프로시저
if object_id('dbo.usp_d_change_view') is not null
drop proc dbo.usp_d_change_view
go
create proc dbo.usp_d_change_view
(
@addDate smalldatetime
, @type varchar(100)
, @cutOffDay int
)
as
declare @viewheader varchar(8000)
, @viewbody varchar(8000)
, @errStatus int
select @viewheader ='' , @viewbody =''
if not exists (select * from dbo.d_manage where type = @type and i_dt = @addDate)
insert into d_manage values (@type , @adddate)
set @viewheader =
'
alter view d
as
'
select @viewbody = @viewbody + 'union all select * from dbo.d_'+ convert(char(8), i_dt, 112) + ' ' + char(13) + char(10)
from dbo.d_manage
where i_dt > dateadd(dd, @cutOffDay * -1 , @addDate) and type = @type
select @viewbody = stuff(@viewbody, 1,10, '')
exec (@viewheader + @viewbody)
set @errStatus = @@error
if @errStatus <> 0
raiserror ('aa',15,1)
else
delete from dbo.d_manage
where
i_dt <= dateadd(dd, @cutOffDay * -1 , @addDate)
and type = @type
go
set statistics profile off
set statistics io off
go
-- 실제 작업
-- 웹에서 테이블 생성 프로시저 호출
exec dbo.usp_d_maketable '20081206'
go
-- 해당 테이블을 대상으로 하둡엔 리듀스 프레임 워크에서 데이터 밀어넣기
-- 가상 데이터 삽입
insert into dbo.d_20081206 values ('1', '20081206', 'a', rand() * 10)
insert into dbo.d_20081206 values ('2', '20081206', 'a', rand() * 10)
insert into dbo.d_20081206 values ('1', '20081206', 'b', rand() * 10)
insert into dbo.d_20081206 values ('2', '20081206', 'b', rand() * 10)
go
-- 데이터 삽입과 인덱스 생성을 더 빠르게 하기위해 인덱스를 나중에 생성
exec dbo.usp_d_makeindex '20081206'
go
-- 동적 SQL 을 이용해서 view 를 고쳐줌
exec dbo.usp_d_change_view '20081206', 'day', 5
go
-- 과거 비효율 적으로 하나의 테이블에서 기간에 해당하는 테이블을 set rowcount 1000 delete d where i_dt < 날짜
-- 형식으로 지우면서 2시간 ~ 3시간을 소모함
-- 과거 테이블 drop
-- 0초
drop table dbo.d_20081201
go
exec sp_helptext d
-- 결과
-------------------------------------
-- CREATE view d
-- as
-- select * from dbo.d_20081202
-- union all select * from dbo.d_20081203
-- union all select * from dbo.d_20081204
-- union all select * from dbo.d_20081205
-- union all select * from dbo.d_20081206
-- 실제 작업 test 2
exec dbo.usp_d_maketable '20081207'
go
insert into dbo.d_20081207 values ('1', '20081207', 'a', rand() * 10)
insert into dbo.d_20081207 values ('2', '20081207', 'a', rand() * 10)
insert into dbo.d_20081207 values ('1', '20081207', 'b', rand() * 10)
insert into dbo.d_20081207 values ('2', '20081207', 'b', rand() * 10)
go
exec dbo.usp_d_makeindex '20081207'
go
exec dbo.usp_d_change_view '20081207', 'day', 5
go
drop table dbo.d_20081202
go
sp_helptext d
go
select * from dbo.d_manage
go
-- type i_dt
-- ----- -----------------
-- day 2008-12-03 00:00:00
-- day 2008-12-04 00:00:00
-- day 2008-12-05 00:00:00
-- day 2008-12-06 00:00:00
-- day 2008-12-07 00:00:00
select * from dbo.d
go
[출처] delete dml 을 ddl 로 고치기 (특수한 업무의 효율을 높이기 위해서 ) (sqlmvp) |작성자 왕눈봄맘
DBA 101_Best Practices for All DBAs to Follow--Auckland.pdf
nprotect제한.vbs
sc_5893.pdf