|
안녕하세요. 김대우 입니다. 더운 여름 잘 지내고 계신지요?
이번에 소개해 드릴 내용은 김연욱님이 이끌어 주시는 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'
|
|--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
|
--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)
|