delete dml 을 ddl 로 고치기 (특수한 업무의 효율을 높이기 위해서 )

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) |작성자 왕눈봄맘