'SQL' 태그의 글 목록 (2 Page)

SQL

쿼리 테크닉 - 2부 2008.05.07
김연욱(Vichang)님의 Inside Query Performance - (2) 2008.05.06
김연욱(Vichang)님의 Inside Query Performance - (1) 2008.05.06

쿼리 테크닉 - 2부

2008. 5. 7. 08:25

728x90

글 손호성

하지만, 외부 조인을 사용하지 않고도 이를 해결할 수 있는 방법이 있다. 쿼리가 조금은 이해되지 않을 수도 있을 것이다. 이 쿼리는 MAX 함수가 메인이되어 사용되며, 후위 문자열을 잘라서 도서 번호를 찾고 있다.

select Type,
	substring(
		max(
			dbo.LPAD(ytd_sales,10,0) + title_id
		)
	,11,6) MaxSaledTitle,
	max(ytd_sales) Max_ytd_sales
from pubs.dbo.titles
group by type

우선, 여기에 사용된 LPad 함수에 대해서 보자. 이 함수는 우리가 흔히 주문 번호등을 따는 경우 숫자를 문자열로 만들어서 처리하는 경우가 많다. 1을 "00001"등으로 만드는 경우이다. 이러한 숫자열을 포맷화된 문자열로 만든다.

CREATE FUNCTION LPAD(
	@s varchar(255), @n int, @p varchar(255) 
) 
returns varchar(255)
as
BEGIN
return IsNULL(REPLICATE(@p,@n-LEN(@s)),'')+@s
END

해당 UDF에 대해서 이해하였다면, 다음의 쿼리를 수행한다. 숫자 형식인 판매량을 문자열로 변경하는 것이다. 숫치를 같은 자릿수의 문자열로 변경하여도 정렬 순서는 크게 변경되지 않는다. "10000가"와 "10001나"는 어떤 것이 MAX인가? 당연 "10001나"이다. 두 개의 크기 비교는 앞의 네 자리 "10000"과 "10001"이 중요한 팩터이지 "가"나 "나"가 중요한 역할을 하는 것이 아니다. 즉, 앞에 숫자가 코끼리고, 그 뒤에 붙는 것은 문자열은 개미라는 것이다.

select type,
	dbo.LPAD(ytd_sales,10,0) ytd_sales, 
	title_id 
from pubs.dbo.titles
order by type, ytd_sales desc, title_id

<그림 14> LPAD Order By

그래서, 아래 쿼리 처럼 양식화된 문자열에 도서 번호를 붙여서 정렬해도 정렬 순서는 바뀌지 않게 된다.

select type,
	dbo.LPAD(ytd_sales,10,0) + title_id  	ytd_sales_title_id
from pubs.dbo.titles
order by type, ytd_sales_title_id desc

<그림 15> 문자열 정렬

이제, 이렇게 붙어진 것에서 MAX 값을 취하고, 그 값에서 정해진 뒷 자리를 분리해놓으면, 원래의 값을 찾아내게 된다. 두 쿼리의 실행 계획을 살펴보자. 외부 조인을 사용하는 쿼리는 MAX 함수를 사용하는 쿼리에 비교해서 두 배 이상의 비용이 소모된다. 즉, 그만큼이나 MAX 함수를 사용하는 것이 좋을 수 있다는 것이다. 물론, 인덱스가 어떻게 고려되었느냐에 따라서 다르겠지만 말이다.

<그림 16> 두 쿼리의 실행 계획 비교

T-SQL 기본 테크닉

SQL을 공부하는 데에 여러 가지 방법들이 있다지만, 그래도 몇 가지 기본 적인 사항들을 알고 시작하는 것이 자기 발전에 도움이 된다. 이러한 방법들 중에는 정말 별것 아닌 내용들도 있고, 자신이 잘 모르고 있던 내용들도 있을 것이다. 중요한 것은 실무에 이러한 내용들을 적용해 본 적이 있는가 하는 것이다. 내용만 알고 써먹지 않는다면, 올바른 지식 활용 습관(?)이 아니다. 캬캬캬.

우선 첫번쨰로, 사전 테이블(Dictionary table)의 이용에 대해서 생각해보자. 데이터베이스는 집합을 처리하는 게 주목적이다. 집합이 없을 때는 만들어서 처리할 수도 없다. 테이블이 존재하지 않는다고, 그냥 있는 것만 쓰지 마라. 새로운 것을 만들어서 처리하는 능력이 필요하다.

그림 17과 같이 Date_sales라는 테이블이 있다고 가정하자. PDate 컬럼에는 일자가 문자열로 저장되어 있다.

<그림 17> Date_Sales 테이블

이 테이블에서 1998년에 요일별 판매량을 집계한다고 가정해보자. 그럼 보통 어떻게 처리할까? 아마도 문자열 컬럼인 PDate를 Datetime 형식으로 변경하고, 변형된 컬럼을 기준으로 요일을 뽑아서 집계해야 할것이다. PDate 컬럼에 인덱스가 있다고 하더라도 집계시에는 사용할 수 없고 재가공해야만 한다.

select  
datepart(
	weekday,
		cast(
			substring(pdate,1,4) + '-' + 
			substring(pdate,5,2) + '-' + 
			substring(pdate,7,2) 
		as smalldatetime)
) 요일,
avg(sales) 평균판매량
from date_sales
where pdate between '19980101' and '19981231'
group by datepart(weekday,cast(substring(pdate,1,4) + '-' + substring(pdate,5,2) + '-' + substring(pdate,7,2) as smalldatetime))
order by 1

이것을 있는 그대로만으로 해결할려고 하면 답이 없다. 하지만, 별도의 집합을 만들어서 두 집합 사이의 교집합을 구하면 쉽게 해결할 수 있다. 게다가, 컬럼의 변형이 없으므로 인덱스도 제대로 이용할 수 있다. 여기서 사용되는 별도의 집합은 날짜 사전 테이블이다. 이 테이블의 샘플 데이터가 그림 18에 나와있다.

<그림 18> DIC_Date 테이블

새로운 집합이 만들어졌으며로, 보다 편하게 쿼리할 수 있다. Dic_date 테이블에는 요일이 저장된 컬럼이 있으로로 굳이 Date_sales 테이블의 Pdate 컬럼을 변형할 필요가 없다.

select b.pweek 요일
,avg(a.sales) 평균판매량
from date_sales a inner join Dic_date b
on a.pdate = b.pdate
and a.pdate between '19980101' and '19981231'
group by b.pweek
order by 1

외부 조인을 사용한 쿼리와 별도 사전 테이블의 실행 계획을 비교해보면, 사전 테이블을 사용한 쿼리가 절반 이상 빠른 것을 알 수 있다. 모집단의 크기가 커질수록 이런 경향은 더 가속화된다.

<그림 19> 두 실행 계획 비교

두 번째, 쿼리 테크닉은 존재 하지 않는 데이터를 생성하는 인라인 메모리 뷰(Inline memory view)를 이용하는 것이다. 존재 하지 않는 데이터는 컬럼이 여러 개인 것과 여러 로우로 구성되는 경우 등이 있다. 여러 컬럼인 것은 "select '남자' c1, '여자' c2"과 같이 특정 값에 컬럼 명을 붙이는 경우이고, 여러 로우로 구성되는 경우는 UNION ALL을 사용하는 "select 1 num union all select 2 union all select 3"과 같은 쿼리이다. 물론, 이 두가지를 복합해서 여러 컬럼과 여러 로우로 구성되게 쿼리를 작성할 수도 있다.

세 번째는 CASE WHEN 문장을 쿼리에 삽입하는 것이다. 이 문장은 SQL 서버에서 쿼리의 동적인 힘을 불어넣어준다. 이 동적인 힘의 SQL의 여러 부분에서 사용되는 것이 가능하다. 컬럼에서도 가능하고, Where 조건절이나 Group by 절에서도 이용할 수 있다. 다음 쿼리는 컬럼에 CASE WHEN을 사용한 예이다.

select (
	case type 
	when 'UNDECIDED' then 'business' 
	else type end),
	sum(ytd_sales)
from pubs.dbo.titles
group by (
	case type 
	when 'UNDECIDED' then 'business' 
	else type end)

두번째와 세번째는 다른 기법들과 같이 사용되는 경우가 많다. 네 번쨰는 데이터를 복제하여 사용하는 방식이다. 집합 복제의 기능에는 대략 세 가지가 있다. 요소의 복제, 존재하지 않는 데이터를 발생, 집합을 여러 차원으로 복하는 기능이다.

<그림 20> 집합 복제의 주요 기능들

요소의 복사라는 것은 집합과 하나의 원소를 가지는 값간에 크로스 조인이 작용될때 발생하는 것이다. 집합 2의 원소는 집합 1에 모두 복제되게 된다.

<그림 21> 요소의 복사

예를 들어 다음과 같은 쿼리가 있다고 가정하자. 이 쿼리에서 동일한 역할을 수행하는 두 개의 서브 쿼리가 있다. 바로 Pubs.dbo.sales 테이블의 평균을 계산하는 쿼리이다. 이 값이 필요할 때마다, 이 값을 서브 쿼리해와야만 한다.

select title_id,sum(qty) title_qty,
  (select avg(qty) avgQty from pubs.dbo.sales) avgQty,
  (sum(qty)-(select avg(qty) avgQty from pubs.dbo.sales)) calcQty
from pubs.dbo.sales
group by title_id

하지만, 요소 복제를 이용하면 이것을 쉽게 해결할 수 있다. 밑의 쿼리가 이것이다. 하나의 원소를 가지는 서브 쿼리와의 크로스 조인으로 집합 1은 이 값을 원하는 만큼 사용할 수 있다.

select title_id,title_qty,avgQty,title_qty-avgQty as calcQty
from 
(
 select title_id,sum(qty) title_qty
 from pubs.dbo.sales
 group by title_id
) as a,
(
 select avg(qty) avgQty from pubs.dbo.sales
) as b

이것은 성능면에서도 매우 좋은 방식이라고 할 수 있다. 쿼리 1은 서브 쿼리를 사용할 때마다 그만큼의 I/O 비용이 들게 된다. 하지만, 두번째 방식은 그럴 필요가 없다. 비용면에서도 59.36% : 40.64%로 훨씬 이익이다.

<그림 22> 두 쿼리의 비교 - 요소복제

하나 이상의 로우들로 구성된 집합과의 크로스 조인은 로우수만큼 집합 1을 복제하게 된다. 이러한 집합 복제가 가지는 의미는 존재하지 않는 데이터를 발생시키는 것이 가능하다는 것이다.

<그림 23> 존재하지 않는 데이터의 발생

예를 들어서 다음과 같은 집계 결과가 있다고 보자. 지역별로 통계를 내는 것이다. 집계라는 작업은 기본적으로 사실만을 기준으로 하는 것이고, 존재하는 데이터만을 기준으로 하는 것이다. 하지만, 현실이라는 것은 그렇지 않다. 존재하지 않는 데이터도 있어야할 필요가 있다. 쿼리 결과엣 경기도에는 남자는 118명으로 집계되어 있지만, 여성에 대한 데이터는 없다. 실존하지 않기 때문이다. 하지만, 많은 경우 여성은 0명이라는 데이터라도 있었으면 한다. 이것을 어떻게 처리할 것이냐? 일반적으로 이런 경우에는 절차적인 방식을 사용하는 것이 기본이다. 하지만, 쿼리로도 이러한 작업이 가능하다.

select area,gender,sum(something) total
from CustomerArea
group by area,gender

area       gender total       
---------- ------ ----------- 
경기         남      118
서울         남      100
부산         여      150
서울         여      120
청주         여      200

다음의 결과를 보면, 존재하지 않는 데이터를 발생시켜서 집계를 하고 있다. 이것이 하나 이상의 로우들로 구성된 집합과의 크로스 조인 효과이다.

<그림 24> 존재하지 않는 데이터의 발생

그날 세미나에서도 이야기했지만, 필자 스스로에게 이런 세미나는 거의 2년만이었다. 세미나 장에서 많은 SQLER 여러분들을 만났고, 100%는 아니지만, 하고 싶은 이야기도 모두 다했다. 다만, 좀더 액션이 있었다면, 뒤에서 졸고 계신 분들을 줄일수 있었을텐데.ㅋㅋㅋ. 모쪼록 좋은 기회를 주셨던 모든 분들과, 날씨 과히 좋지 않았는데, 참여해주셨던 많은 분들께 정.말. 감.사.합.니.다.

끝으로 DEEP INSIDE SQL SERVER 2000에 관련되어 질문하고자 하시는 분이 있으면, 필자의 전자 메일 주소로 [SQLMAG]라는 말머리를 붙여서 메일을 보내기 바란다. 메일 주소는 역시 다음과 같다.

kind511@dreamwiz.com

최종 수정일 : 2005년 3월 16일

'데이터베이스 > SQL Server' 카테고리의 다른 글

[펌] MSSQL 모든 Table Schema를 구하는 스크립트 (0)	2008.05.08
쿼리 테크닉 - 1부 (0)	2008.05.07
SQL Sever에 별칭(Alias)을 달아보자 (0)	2008.05.07
김종균 : SQL Server 2005 .NET CLR 통합기능 (0)	2008.05.07
김민석 : SQL Server 2005의 Partitioned table 강좌(1) (0)	2008.05.07

김연욱(Vichang)님의 Inside Query Performance - (2)

2008. 5. 6. 07:52

728x90

▶ 김연욱(Vichang)님의 Inside Query Performance - (2)

안녕하세요. 김대우 입니다. 더운 여름 잘 지내고 계신지요?

이번에 소개해 드릴 내용은 김연욱님이 이끌어 주시는 Inside Query Performance 입니다.

아주 좋은 SQL 쿼리 성능에 대한 연재글을 올려 주고 계시며 SQLER에 많은 도움을 주시는 분이지요.

이하 내용은 김연욱님이 SQLER 자유게시판에 올려주신 항목을 정리한 내용입니다.

언제나 많은 도움 주시는 김연욱님 감사 드리구요. 앞으로도 좋은... 더운 여름 더더욱 열나게 하는 ^_^;;;

좋은 글 부탁 드립니다. 감사합니다.

이하 김연욱님의 글입니다.

일반적으로 이력관리 모델에서는 시작일자와 종료일자컬럼을 가진다.

--> 이부분에 대한것은 워낙에 내용이 길기때문에 생략.

--> 뭐 그냥 시작일자나 종료일자 하나만 가지는 설계를 사용하신다면 할 말 없음

이력모델에서 시작일자 + 종료일자 인덱스를 사용하는것이 좋을지

아니면 종료일자 + 시작일자 인덱스를 사용하는것이 좋을지

한번 테스트 해보자.

선행지식을 위해 between을 생각해보자.

일반적으로 우리가 사용하는 between은 컬럼 between 상수값1 and 상수값2 의 형태이다.

하지만, 조금 변형하여 상수값 between 컬럼1 and 컬럼2 의 형태로도 사용이 가능하다.

간단하게 예를 들어보면

벙개를 위한 장소를 몰색하던 00군이 인터넷으로 벙개용 좌석예약을 할려구 한다.

--drop table 좌석예약
create table 좌석예약(좌석등급 char(1), 시작번호 int, 종료번호 int, 좌석수 int, 구분 varchar(10))
go
insert into 좌석예약 values('A', 1, 100, 100, '공석')
insert into 좌석예약 values('B', 1, 100, 100, '공석')
go

마침 00주점에 이러한 시스템이 있었다.

사무실에서 pc로 단가가 적은 B석 51번부터 10개의 좌석을 예약한다고 해보자.

이럴때 어떻게 쿼리해야할까 ?

--1. 좌석상태확인
select * from 좌석예약
where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
and 51 between 시작번호 and 종료번호

좌석 등급 시작번호 종료번호 좌석수 구분
---- ----------- ----------- ----------- ----------
B 1 100 100 빈자리

(1개 행 적용됨)

이제 between 사용에 감이 잡히겠지요.

기왕 하는김에 좌석예약을 해보자.
　

--2. 좌석예약
set nocount on
declare @좌석등급 char(1), @좌석수 int, @시작번호 int, @종료번호 int, @구분 varchar(10)
declare @좌석위치 int
set @좌석등급 = 'B'
set @좌석수 = 10
set @좌석위치 = 51
set @구분 = 'SQLER 벙개'

set xact_abort on
begin tran
   if exists(select * from 좌석예약 where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
and @좌석위치 between 시작번호 and 종료번호)
begin
   update 좌석예약
   set 시작번호 = @좌석위치,
   종료번호 = @좌석위치 + @좌석수 - 1,
   좌석수 = @좌석수,
   구분 = @구분,
   @시작번호 = 시작번호,
   @종료번호 = 종료번호
   where 좌석등급 = 'B' and 좌석수 >= 10 and 구분='빈자리'
   and @좌석위치 between 시작번호 and 종료번호

   if @시작번호 < @좌석위치
begin
   insert into 좌석예약 values (@좌석등급, @시작번호, @좌석위치-1,
@좌석위치-@시작번호, '빈자리')
end

   if @종료번호 > (@좌석위치 + @좌석수)
begin
   insert into 좌석예약 values (@좌석등급, @좌석위치+@좌석수, @종료번호,
@종료번호-@좌석위치-@좌석수, '빈자리')
end
end
commit tran
set nocount off
go

예약을 잘되었는지 확인해보면...

　

select * from 좌석예약 where 좌석등급 = 'B' order by 시작번호

좌석 등급    시작번호    종료번호    좌석수 구분
---- ----------- ----------- ----------- ----------
B    1 50    50    빈자리
B    51    60    10    SQLER 벙개
B    61    100 39    빈자리

(3개 행 적용됨)

51번부터 60번까지 예약이 잘되있다... 근데 난 언제나 벙개에 함 나갈볼런지...

/*
선행지식
1. 재미없는 긴글을 끝까지 읽어줄 강인한 인내력 (필수)

2. sql서버는 복합컬럼인덱스일경우 첫번째 컬럼의 통계정보만을 사용함. (중요)
3. 인덱스에 대한 기본지식
4. between에 대한 기본지식
   일반적으로 between을 이용할경우는 변수 between 시작값 and 종료값 의 형식으로
   사용하지만 상수값 between 시작변수 and 종료변수 와 같이 사용할 수 도 있다.
   (좀더 자세한 내용은 관련 서적 참고)

해당인덱스를 clustered index 로 할것인지 nonclustered index 로 할것인지는 별개의
문제이므로 논외로 합니다.
*/

--언제나 테스트는 tempdb에서
use tempdb
go

--역시나 테스트를 위해서 테이블 하나 만들구
--drop table a
create table a(edt int not null, sdt int not null, num int identity)
go

--테스트를 편하게 하고자 날짜형 대신에 숫자형으로 테스트
/* ========================================================================== */
--켜져있으면 끄놓구
set statistics io off
set statistics profile off

--테스트용 샘플 넣구
set nocount on
declare @i int
set @i = 0
while (@i < 100000)
begin
insert a values(@i+1, @i)
set @i = @i + 2
end
set nocount off
select count(*) from a --50000건

--테스트를 위해 켜두고
set statistics io on
set statistics profile on
go

--아무런 제약조건, 인덱스 없는 상태에서 그냥
select num from a

|--Table Scan(OBJECT:([tempdb].[dbo].[A]))
--논리적 읽기 수 137 (전체 페이지수가 137page임)
　

--먼저 시작일 + 종료일자로 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(sdt, edt)

--저 앞에 있는놈을 보면
select num from a where 90 between sdt and edt

|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
AND Convert([@2])<=[A].[EDT]))
-- 논리적 읽기 수 137
　

-- 저 뒤에 있는 놈을 보면
select num from a where 90000 between sdt and edt

|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137
　

-- 강제로 pk를 타라고 힌트를 주면 어떨까 ?
select num from a (index=pk_a) where 90000 between sdt and edt
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))

|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90000),
WHERE:(90000<=[A].[EDT])
--논리적 읽기 수 108
　

--그럼, top 1 을 주고 인덱스를 타라고 힌트를 주면 어떨까 ?
select top 1 num from a (index=pk_a) where 90000 between sdt and edt

|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90000),
WHERE:(9000<=[A].[EDT]) ORDERED FORWARD)

--논리적 읽기 수 108
　

--일단, pk 지우고
alter table a drop constraint pk_a
　

--이번에는 종료일 + 시작일루 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(edt, sdt)
　

--저 앞에 있는놈을 보면
select num from a where 90 between sdt and edt

|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137
　

--그럼, pk를 타라구 힌트를 주면
select num from a (index(pk_a))
where 90 between sdt and edt
select num from a (index(pk_a)) where 90 between sdt and edt

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90),
WHERE:(90>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 120
　

--요번에는 top 1 까지 주면
select top 1 num from a (index(pk_a))
where 90 between sdt and edt

|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90),
WHERE:(90>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 3
　

-- 요번엔 저뒤에 있는놈을 보면
select num from a where 90000 between sdt and edt

|--Table Scan(OBJECT:([tempdb].[dbo].[A]), WHERE:(Convert([@1])>=[A].[SDT]
   AND Convert([@2])<=[A].[EDT]))
--논리적 읽기 수 137

--이번에두 힌트사용
select num from a (index(pk_a)) where 90000 between sdt and edt

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
   |--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000),
   WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 15
　

--그럼, top 1 을 주면
select top 1 num from a (index(pk_a)) where 90000 between sdt and edt

|--Top(1)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[A]))
|--Index Seek(OBJECT:([tempdb].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000),
WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)
--논리적 읽기 수 3
　

-- 통계정보를 만들어 주면 좀더 낳지 않을까(역시 동일한 결과) - 각자 해보세요...
CREATE STATISTICS ust_a_edt
   ON test..a (edt)
   WITH FULLSCAN
CREATE STATISTICS ust_a_sdt
   ON test..a (sdt)
   WITH FULLSCAN
UPDATE STATISTICS a(pk_a)
   WITH SAMPLE 100 PERCENT

결론 : sql서버에서 --> 상수값 between 시작값 and 종료값 조인을 할경우
1. 시작일 + 종료일 인덱스일경우
1) 시작일만을 사용하므로 해당조건의 선택도가 항상동일함에도 불구하고,

   시작일 <= 상수값 의 조건만을 활용한다.

   |--Index Seek(OBJECT:([test].[dbo].[A].[PK_A]), SEEK:([A].[SDT] <= 90),
   WHERE:(90<=[A].[EDT]) ORDERED FORWARD)

2) 시작일 <= 상수값의 조건으로 인덱스를 스캔하면서 종료일 >= 상수값의 조건으로

   필터처리한다.(무식한놈)

2. 종료일 + 시작일 인덱스일경우
1) 종료일만을 사용하므로 해당조건의 선택도가 항상동일함에도 불구하고,

   종료일 >= 상수값 의 조건만을 활용한다.

|--Index Seek(OBJECT:([test].[dbo].[A].[PK_A]), SEEK:([A].[EDT] >= 90000),
WHERE:(90000>=[A].[SDT]) ORDERED FORWARD)

2) 종료일 >= 상수값의 조건으로 인덱스를 스캔하면서 시작일 <= 상수값의 조건으로

   필터처리한다.(무식한놈)

3. 결론
1) 종료일 + 시작일 순으로 인덱스를 만들고나서

2) SELECT 절에 TOP 1 을 추가하고

3) INDEX(인덱스명) 힌트를 사용하면 항상 동일한 결과를 나타낸다.

※ 시작일 + 종료일 순으로 인덱스를 만들면 위와같이(1번에서 3번까지) 해도 비효율적인 스캔을 한다.

　

누군가가 딴지를 걸어주기를 기다렸는데 아무두 안걸어주어서 내가 딴지건다.

딴지) 뭐땜시, 복잡하게 종료일자 + 시작일자로 인덱스를 만드나, 그냥 시작일자 하나만 있어두 되던데...

--켜져있으면 끄놓구
set statistics io off
set statistics profile off
go

--일단, pk 지우고
alter table a
drop constraint pk_a

--이번에는 종료일 + 시작일루 pk 잡아주고
alter table a add constraint pk_a primary key nonclustered(sdt)

--테스트를 위해 다시 켜두고
set statistics io on
set statistics profile on
go

select top 1 num from a where sdt <= 90 order by sdt desc

|--Top(1)
   |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
   SEEK:([a].[sdt] <= 90) ORDERED BACKWARD)
--논리적 읽기 수 3

select top 1 num from a where sdt <= 9000 order by sdt desc

|--Top(1)
   |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
   SEEK:([a].[sdt] <= 9000) ORDERED BACKWARD)
--논리적 읽기 수 3

　

두경우 모두 논리적 읽기 수가 3이다. 이것이 더 좋지 않느냐, 쿼리도 깔끔하구..
　

그럼, sample을 조금 바꿔서 다른경우를 살펴보자.

--켜져있으면 끄놓구
set statistics io off
set statistics profile off

--drop table k1
create table k1(상품코드 int not null, 상품명 varchar(100))
go
alter table k1
   add constraint pk_k1 primary key nonclustered(상품코드)
go
set nocount on
declare @i int
set @i = 1
while(@i <= 1000)
begin
   insert into k1 values(@i, 'HDD ' +convert(varchar(10),@i)+'G')
   set @i = @i + 1
end
set nocount off
go

--drop table k2
create table k2(적용일자 datetime not null, 종료일자 datetime not null, 단가 int)
go
set nocount on
declare @i datetime
set @i = '2000-01-01'
while(@i <= '2003-04-30')
begin
insert into k2 values(@i, dateadd(dd,1,@i), convert(int, rand()*1000))
set @i = dateadd(dd, 1, @i)
end
set nocount off

--drop table k3
create table k3(상품코드 int not null, 적용일자 datetime not null,
종료일자 datetime not null, 단가 int)
go
alter table k3
   add constraint pk_k3 primary key nonclustered(상품코드, 적용일자)
go
set nocount on
insert into k3
   select k1.상품코드, k2.적용일자, k2.종료일자, k2.단가
   from k1 cross join k2
set nocount off

--drop table k4
create table k4(상품코드 int not null, 적용일자 datetime not null,
종료일자 datetime not null, 단가 int)
go
alter table k4
   add constraint pk_k4 primary key nonclustered(상품코드, 종료일자, 적용일자)
go
set nocount on
insert into k4
   select k1.상품코드, k2.적용일자, k2.종료일자, k2.단가
   from k1 cross join k2
set nocount off

　

　

　

--다시 테스트를 위하여 켜놓구
set statistics io on
set statistics profile on
go

select count(*) from k3 --1216000
'k3' 테이블. 스캔 수 1, 논리적 읽기 수 3458

select count(*) from k4 --1216000
'k4' 테이블. 스캔 수 1, 논리적 읽기 수 4662

　

인덱스size 차이로 인하여 k4가 page수가 더 많다.

--1. 1번 상품의 '2003-03'월 단가변동내역이 필요하다면 ?

select 적용일자, 단가
from k3
where 적용일자 <= '2003-03-31' and 종료일자 >= '2003-03-01'
and 상품코드=1

|--Table Scan(OBJECT:([tempdb].[dbo].[k3]),
   WHERE:(([k3].[적용일자]<=Convert([@1]) AND [k3].[종료일자]>=Convert([@2]))
   AND [k3].[상품코드]=Convert([@3])))

'k3' 테이블. 스캔 수 1, 논리적 읽기 수 5197

　

적용일자가 2003-03-31일 보다 작은것이 대부분이기 때문에 table scan을 선택했다.

　

/* ========================================================================== */

select 적용일자, 단가
from k4
where 적용일자 <= '2003-03-31' and 종료일자 >= '2003-03-01'
and 상품코드=1

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[k4]))
   |--Index Seek(OBJECT:([tempdb].[dbo].[k4].[pk_k4]),
SEEK:([k4].[상품코드]=Convert([@3]) AND [k4].[종료일자] >= Convert([@2])),
WHERE:([k4].[적용일자]<=Convert([@1])) ORDERED FORWARD)

'k4' 테이블. 스캔 수 1, 논리적 읽기 수 36

　

인덱스만을 읽어서 범위를 줄인후에 테이블을 읽어서 단가를 가져왔다.

논리적읽기수를 비교해봐라 엄청난 차이다.

물론, 상황에 따라서 적절한 인덱스 설계를 해야하겠지만

일반적으로 이력모델에서는 종료일자 + 시작일자 의 복합인덱스가 유리한 경우가 많다.

　

▶ 김연욱(Vichang)님의 Inside Query Performance - (2)

▶ 저작권 : 이 홈페이지의 내용은 모두 자유롭게 사용하실 수 있습니다.

'데이터베이스 > SQL Server' 카테고리의 다른 글

제 3부: 성능 향상을 위한 SQL Server 구성 (0)	2008.05.06
데이터 웨어하우징용 Microsoft SQL Server 2000 RDBMS (0)	2008.05.06
김연욱(Vichang)님의 Inside Query Performance - (1) (0)	2008.05.06
인덱스 관리 개선기능 (0)	2008.05.06
SQL Server 2005 - DMVStats 1.01 (0)	2008.05.06

김연욱(Vichang)님의 Inside Query Performance - (1)

2008. 5. 6. 07:51

728x90

▶ 김연욱(Vichang)님의 Inside Query Performance - (1)

안녕하세요. 김대우 입니다. 더운 여름 잘 지내고 계신지요?

이번에 소개해 드릴 내용은 김연욱님이 이끌어 주시는 Inside Query Performance 입니다.

아주 좋은 SQL 쿼리 성능에 대한 연재글을 올려 주고 계시며 SQLER에 많은 도움을 주시는 분이지요.

이하 내용은 김연욱님이 SQLER 자유게시판에 올려주신 항목을 정리한 내용입니다.

언제나 많은 도움 주시는 김연욱님 감사 드리구요. 앞으로도 좋은... 더운 여름 더더욱 열나게 하는 ^_^;;;

좋은 글 부탁 드립니다. 감사합니다.

이하 김연욱님의 글입니다.

1. 문자컬럼에다가 숫자컬럼을 비교하면 인덱스를 타는가 ?

2. 숫자컬럼에다가 문자컬럼을 비교하면 인덱스를 타는가 ?

3. 문자컬럼에다가 datetime컬럼을 비교하면 인덱스를 타는가 ?

4. datetime컬럼에다가 문자컬럼을 비교하면 인덱스를 타는가 ?

밑에 결과 보지말구 그냥 5초만 생각보세요.

중수이상 되시는 분은 빨랑 백스페이스를 눌러서 나가주세용.

--tempdb에서 테스트
use tempdb
go

--있으면 지우고
drop table a
go

--테스트용 테이블 하나 만들고
create table a(id int identity, cha varchar(10))
go
　

--역시나 테스트용 데이터 넣구
set nocount on
go
declare @i int
set @i = 1
while (@i <= 10000)
begin
insert into a(cha) values (convert(varchar(10), @i))
set @i = @i + 1
end
go
set nocount off
go

--인덱스 만들구
create index idx on a (cha)
create index idx2 on a (id)
go

--테스트를 위해 io량을 보고, 실행계획을 보기위해 설정
set statistics io on
set statistics profile on
go

--어떤경우에 인덱스를 쓸까 ?
--1. 그냥한번 풀로 읽어보면
select * from a

|--Table Scan(OBJECT:([tempdb].[dbo].[a]))

'a' 테이블. 스캔 수 1, 논리적 읽기 수 28
　

--2. 정상적으로 컬럼형식을 맞춰서 검색하면 ?
select * from a where id = 1

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx2]), SEEK:([a].[id]=Convert([@1])) ORDERED FORWARD)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 3

　

select * from a where cha='1'

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
|--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]), SEEK:([a].[cha]='1') ORDERED FORWARD)

'a' 테이블. 스캔 수 1, 논리적 읽기 수 3
--제대로 3페이지만 인덱스를 사용하여 검색

　

--3. 그럼 숫자컬럼에다가 문자를 비교하면 ?
select * from a where id = '1'

--4. 문자컬럼에다가 숫자를 비교하면 ?
select * from a where cha=1

|--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:(Convert([a].[cha])=Convert([@1])))

'a' 테이블. 스캔 수 1, 논리적 읽기 수 28

--웬 테이블 스캔....

생각하신대로 결과가 나왔나요 ?

그럼 이번에는 많이 사용하는 datetime 컬럼을 테스트해보자.

--다른 샘플을 만들기 위해 잠시 off시키고
set statistics io off
set statistics profile off
go
　

--있으면 지우고
drop table b
go
　

--테스트용 테이블 하나 만들고
create table b(dt1 datetime, dt2 char(8))
go
　

--역시나 테스트용 데이터 넣구
set nocount on
go
declare @i datetime
set @i = '2000-01-01'
while (@i <= '2003-12-31')
begin
insert into b values (@i, convert(varchar(8), @i, 112))
set @i = dateadd(dd, 1, @i)
end
go
set nocount off
go
　

--인덱스 만들구
create index idx on b (dt1)
create index idx2 on b (dt2)
go

--테스트를 위해 io량을 보고, 실행계획을 보기위해 설정
set statistics io on
set statistics profile on
go

--어떤경우에 인덱스를 쓸까 ?
--1. 그냥한번 풀로 읽어보면
select * from b

|--Table Scan(OBJECT:([tempdb].[dbo].[b]))

'b' 테이블. 스캔 수 1, 논리적 읽기 수 5

　

--2. 정상적으로 컬럼형식을 맞춰서 검색하면 ?
declare @dt1 datetime
set @dt1 = '2003-05-05'
select * from b where dt1 = @dt1

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
|--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx]), SEEK:([b].[dt1]=[@dt1]) ORDERED FORWARD)

'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

　

declare @dt2 char(8)
set @dt2 = '20030505'
select * from b where dt2 = @dt2

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
|--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx2]), SEEK:([b].[dt2]=[@dt2]) ORDERED FORWARD)

'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

　

--3. datetime 컬럼에다가 문자를 비교하면 ?
declare @dt2 char(8)
set @dt2 = '20030505'
select * from b where dt1 = @dt2

|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
|--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx]), SEEK:([b].[dt1]=Convert([@dt2])) ORDERED FORWARD)

'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

　

--4. 문자컬럼에다가 datetime을 비교하면 ?
declare @dt1 datetime
set @dt1 = '2003-05-05'
select * from b where dt2 = @dt1

|--Table Scan(OBJECT:([tempdb].[dbo].[b]), WHERE:(Convert([b].[dt2])=[@dt1]))

'b' 테이블. 스캔 수 1, 논리적 읽기 수 5

--웬 테이블 스캔....

　

생각하신대로 결과가 나왔나요 ?
　

결론, 문자대 숫자의 비교는 무조건 문자를 숫자로 변환후, 또는 문자를 datetime형으로

변환후 비교할려구 한다.(자동형변환에 의하야 - 무조건 형식을 맟춰서 사용해야하겠네요.)

프로그램 개발시 주의하세요 !!!

난이의 밥벌이에 대해서 좀더 알아 보도록 하겠습니다. ^_^

▶ 김연욱(Vichang)님의 Inside Query Performance - (1)

'데이터베이스 > SQL Server' 카테고리의 다른 글

데이터 웨어하우징용 Microsoft SQL Server 2000 RDBMS (0)	2008.05.06
김연욱(Vichang)님의 Inside Query Performance - (2) (0)	2008.05.06
인덱스 관리 개선기능 (0)	2008.05.06
SQL Server 2005 - DMVStats 1.01 (0)	2008.05.06
SQL Server 2005 파티션 제외(Partition Elimination) 기능 (0)	2008.04.30

PREV 1 2 3 4 5 ···8 NEXT

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

컴퓨토피아

SQL

쿼리 테크닉 - 2부

'데이터베이스 > SQL Server' 카테고리의 다른 글

김연욱(Vichang)님의 Inside Query Performance - (2)

'데이터베이스 > SQL Server' 카테고리의 다른 글

김연욱(Vichang)님의 Inside Query Performance - (1)

'데이터베이스 > SQL Server' 카테고리의 다른 글

+ Recent posts

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역