정렬의 최적화 (01.정렬이 발생하는 경우)

/*

정렬의 최적화

*/

 

/* 01.정렬이 발생하는 경우 */

 

------기본 셋팅

USE jwjung;

GO

 

IF OBJECT_ID('상품') IS NOT NULL

             DROP TABLE 상품

GO

IF OBJECT_ID('일별상품가격') IS NOT NULL

             DROP TABLE 일별상품가격

GO

 

CREATE TABLE 상품 (

             상품ID                  INT                      NOT NULL

             ,상품명                 SYSNAME           NOT NULL

             ,입력일시              DATETIME           NULL

             ,상품설명              CHAR(2000)       NULL

);

GO

CREATE TABLE 일별상품가격 (

             상품ID                  INT                      NOT NULL

             ,기준일자              CHAR(8)             NOT NULL

             ,상품가격              NUMERIC(12, 0) NOT NULL

             ,비고                                 VARCHAR(1000) NULL

);

GO

 

--상품 테이블에 데이터 입력

INSERT INTO 상품

SELECT TOP 100

             id, name, crdate, name

FROM master.dbo.sysobjects

ORDER BY id DESC

GO

--(100개 행이 영향을 받음)

 

--일별상품가격 테이블에 데이터 입력

INSERT INTO 일별상품가격

SELECT a.상품ID

             , CONVERT(VARCHAR(8), CONVERT(DATETIME, '20090101', 112) + b.rnum - 1, 112)

             , 1000 - b.rnum

             , '비고 : ' + CONVERT(VARCHAR, b.rnum)

FROM 상품 a

             , (

                           SELECT ROW_NUMBER() OVER(ORDER BY a.OrderID) AS rnum

                           FROM Northwind.dbo.Orders a, Northwind.dbo.Orders b

             ) b

WHERE b.rnum <= 365

GO

--(36500개 행이 영향을 받음)

 

ALTER TABLE 상품

ADD CONSTRAINT 상품_pk PRIMARY KEY NONCLUSTERED (상품ID)

GO

 

ALTER TABLE 일별상품가격

ADD CONSTRAINT 일별상품가격_pk PRIMARY KEY NONCLUSTERED (상품ID, 기준일자)

GO

 

EXEC sp_helpindex '상품';

EXEC sp_helpindex '일별상품가격';

 

SELECT * FROM 상품;

GO

--(100개 행이 영향을 받음)

--테이블 '상품'. 검색 수 1, 논리적 읽기 수 34, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

SELECT * FROM 일별상품가격;

GO

--(36500개 행이 영향을 받음)

--테이블 '일별상품가격'. 검색 수 1, 논리적 읽기 수 198, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

DBCC IND('jwjung', '상품', 1);

DBCC IND('jwjung', '상품', 2);

 

DBCC TRACEON(3604);

DBCC PAGE(jwjung, 1, 151, 3);

DBCC TRACEOFF(3604);

 

SET STATISTICS PROFILE ON

SET STATISTICS TIME ON

SET STATISTICS IO ON

--(1) Order by 사용 시

--쿼리에 기술된 order by 조건과 인덱스의 정렬 순서가 일치하지 않으면 정렬이 발생한다.

--둘의 정렬순서가 일치하면 정렬이 발생하지 않지만, 이때도 order by 조건의 컬럼을 가공하면

--정렬이 발생하므로 주의해야 한다.

 

--//order by 조건과 인덱스가 일치하지 않을 때

SELECT TOP 1 *

FROM 일별상품가격 WITH (INDEX(일별상품가격_pk))

ORDER BY 상품ID, 기준일자 DESC

GO

--Rows   Executes             StmtText

--1         1           SELECT TOP 1 * FROM 일별상품가격 WITH (INDEX(일별상품가격_pk)) ORDER BY 상품ID, 기준일자 DESC

--1         1             |--Top(TOP EXPRESSION:((1)))

--1         1                  |--Parallelism(Gather Streams, ORDER BY:([jwjung].[dbo].[일별상품가격].[상품ID] ASC, [jwjung].[dbo].[일별상품가격].[기준일자] DESC))

--3         4                       |--Sort(TOP 1, ORDER BY:([jwjung].[dbo].[일별상품가격].[상품ID] ASC, [jwjung].[dbo].[일별상품가격].[기준일자] DESC))

--36500 4                           |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1005]) WITH UNORDERED PREFETCH)

--36500 4                                 |--Index Scan(OBJECT:([jwjung].[dbo].[일별상품가격].[일별상품가격_pk]))

--36500 36500                         |--RID Lookup(OBJECT:([jwjung].[dbo].[일별상품가격]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

 

--(1개 행이 영향을 받음)

--테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

--테이블 '일별상품가격'. 검색 수 4, 논리적 읽기 수 36813, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

--//적절한 인덱스가 있지만, order by 컬럼을 가공했을 때

--인덱스를 효율적(또는 정상적)으로 액세스할 수 있도록 검색조건을 기술하더라도 order by 컬럼을 가공하면 정렬이 발생한다.

SELECT TOP 1 *

FROM 일별상품가격

WHERE 기준일자 BETWEEN '20090301' AND '20090315'

ORDER BY CONVERT(DATETIME, 기준일자, 112)

GO

--Rows   Executes             StmtText

--1         1           SELECT TOP 1 * FROM 일별상품가격 WHERE 기준일자 BETWEEN '20090301' AND '20090315' ORDER BY CONVERT(DATETIME, 기준일자, 112)

--1         1             |--Sort(TOP 1, ORDER BY:([Expr1004] ASC))

--0         0                  |--Compute Scalar(DEFINE:([Expr1004]=CONVERT(datetime,[jwjung].[dbo].[일별상품가격].[기준일자],112)))

--1500   1                       |--Table Scan(OBJECT:([jwjung].[dbo].[일별상품가격]), WHERE:([jwjung].[dbo].[일별상품가격].[기준일자]>='20090301' AND [jwjung].[dbo].[일별상품가격].[기준일자]<='20090315'))

 

--(1개 행이 영향을 받음)

--테이블 '일별상품가격'. 검색 수 1, 논리적 읽기 수 198, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

--불필요한 CONVERT 함수 때문에 인덱스의 정렬 순서를 활용하지 못해서 정렬이 발생했다.

--CONVERT 함수 제거 시 논리적 읽기수 참고

SELECT TOP 1 *

FROM 일별상품가격

WHERE 기준일자 BETWEEN '20090301' AND '20090315'

GO

--(1개 행이 영향을 받음)

--테이블 '일별상품가격'. 검색 수 1, 논리적 읽기 수 32, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

 

 

--//group by 사용 시

--group by 조건에 의해서도 정렬 연산이 발생할 수 있다. 이때는 실행계획에 집계(Aggregate) 연산이 함께 발생한다.

SELECT 기준일자, MAX(상품가격)

FROM 일별상품가격

WHERE 기준일자 BETWEEN '20090301' AND '20090305'

GROUP BY 기준일자

GO

 

--(5개 행이 영향을 받음)

--테이블 '일별상품가격'. 검색 수 1, 논리적 읽기 수 198, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

--Rows   Executes             StmtText

--5         1           SELECT 기준일자, MAX(상품가격) FROM 일별상품가격 WHERE 기준일자 BETWEEN '20090301' AND '20090305' GROUP BY 기준일자

--5         1             |--Stream Aggregate(GROUP BY:([jwjung].[dbo].[일별상품가격].[기준일자]) DEFINE:([Expr1004]=MAX([jwjung].[dbo].[일별상품가격].[상품가격])))

--500     1                  |--Sort(ORDER BY:([jwjung].[dbo].[일별상품가격].[기준일자] ASC))

--500     1                       |--Table Scan(OBJECT:([jwjung].[dbo].[일별상품가격]), WHERE:([jwjung].[dbo].[일별상품가격].[기준일자]>='20090301' AND [jwjung].[dbo].[일별상품가격].[기준일자]<='20090305'))

 

 

--//distinct 사용 시

--select-list에 distinct를 사용하면 결과 집합에서 중복된 값이 제거되어야 하므로, 이 과정에서 정렬 연산이 발생할 수 있다.

SELECT DISTINCT 상품명

FROM 상품

WHERE 상품명 LIKE 'all%'

GO

--Rows   Executes             StmtText

--5         1           SELECT DISTINCT 상품명 FROM 상품 WHERE 상품명 LIKE 'all%'

--5         1             |--Sort(DISTINCT ORDER BY:([jwjung].[dbo].[상품].[상품명] ASC))

--5         1                  |--Table Scan(OBJECT:([jwjung].[dbo].[상품]), WHERE:([jwjung].[dbo].[상품].[상품명] like N'all%'))

 

--//union 사용 시

--중복된 로우가 제거되고서 결과 집합이 출력

DECLARE @기준일자1 CHAR(8), @기준일자2 CHAR(8)

SET @기준일자1 = '20090101'

SET @기준일자2 = '20091231'

 

SELECT 상품ID, 기준일자, 상품가격

FROM 일별상품가격

WHERE 기준일자 = @기준일자1

UNION

SELECT 상품ID, 기준일자, 상품가격

FROM 일별상품가격

WHERE 기준일자 = @기준일자2

GO

--Rows   Executes             StmtText

--200     1           SELECT 상품ID, 기준일자, 상품가격 FROM 일별상품가격 WHERE 기준일자 = @기준일자1 UNION SELECT 상품ID, 기준일자, 상품가격 FROM 일별상품가격 WHERE 기준일자 = @기준일자2

--200     1             |--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC, [Union1010] ASC))

--200     1                  |--Concatenation

--100     1                       |--Table Scan(OBJECT:([jwjung].[dbo].[일별상품가격]), WHERE:([jwjung].[dbo].[일별상품가격].[기준일자]=[@기준일자1]))

--100     1                       |--Table Scan(OBJECT:([jwjung].[dbo].[일별상품가격]), WHERE:([jwjung].[dbo].[일별상품가격].[기준일자]=[@기준일자2]))

 

 

--//순위 창 함수 사용 시

SELECT a.*

             , ROW_NUMBER() OVER(ORDER BY 입력일시 DESC)

FROM 상품 a

WHERE 상품명 LIKE 'a%'

GO

--Rows   Executes             StmtText

--5         1             |--Sequence Project(DEFINE:([Expr1003]=row_number))

--5         1                  |--Segment

--5         1                       |--Sort(ORDER BY:([a].[입력일시] DESC))

--5         1                            |--Table Scan(OBJECT:([jwjung].[dbo].[상품] AS [a]), WHERE:([jwjung].[dbo].[상품].[상품명] as [a].[상품명] like N'a%'))

 

 

--//병합 조인 시

--먼저, 상품 테이블에 [상품명 + 상품ID] 컬럼으로 구성된 상품_x01 인덱스를 추가하고

CREATE NONCLUSTERED INDEX 상품_x01 ON 상품(상품명, 상품ID)

GO

 

SELECT TOP 10 *

FROM 상품 a INNER MERGE JOIN 일별상품가격 b

             ON a.상품ID = b.상품ID AND b.기준일자 BETWEEN '20090301' AND '20090331'

WHERE a.상품명 = 'spt_monitor'

ORDER BY b.상품ID, b.기준일자

GO

--Rows   Executes             StmtText

--10       1           SELECT TOP 10 * FROM 상품 a INNER MERGE JOIN 일별상품가격 b ON a.상품ID = b.상품ID AND b.기준일자 BETWEEN '20090301' AND '20090331' WHERE a.상품명 = 'spt_monitor' ORDER BY b.상품ID, b.기준일자

--10       1             |--Sort(TOP 10, ORDER BY:([a].[상품ID] ASC, [b].[기준일자] ASC))

--31       1                  |--Merge Join(Inner Join, MERGE:([a].[상품ID])=([b].[상품ID]), RESIDUAL:([jwjung].[dbo].[상품].[상품ID] as [a].[상품ID]=[jwjung].[dbo].[일별상품가격].[상품ID] as [b].[상품ID]))

--1         1                       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

--1         1                       |    |--Index Seek(OBJECT:([jwjung].[dbo].[상품].[상품_x01] AS [a]), SEEK:([a].[상품명]=N'spt_monitor') ORDERED FORWARD)

--1         1                       |    |--RID Lookup(OBJECT:([jwjung].[dbo].[상품] AS [a]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

--2822   1                       |--Sort(ORDER BY:([b].[상품ID] ASC))

--3100   1                            |--Table Scan(OBJECT:([jwjung].[dbo].[일별상품가격] AS [b]), WHERE:([jwjung].[dbo].[일별상품가격].[기준일자] as [b].[기준일자]>='20090301' AND [jwjung].[dbo].[일별상품가격].[기준일자] as [b].[기준일자]<='20090331'))

 

--(10개 행이 영향을 받음)

--테이블 '일별상품가격'. 검색 수 1, 논리적 읽기 수 198, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

--테이블 '상품'. 검색 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

--NL 조인하도록 조인 힌트를 변경하고서 테스트한 결과

SELECT TOP 10 *

FROM 상품 a INNER LOOP JOIN 일별상품가격 b

             ON a.상품ID = b.상품ID AND b.기준일자 BETWEEN '20090301' AND '20090331'

WHERE a.상품명 = 'spt_monitor'

ORDER BY b.상품ID, b.기준일자

GO

 

--Rows   Executes             StmtText

--10       1           SELECT TOP 10 * FROM 상품 a INNER LOOP JOIN 일별상품가격 b ON a.상품ID = b.상품ID AND b.기준일자 BETWEEN '20090301' AND '20090331' WHERE a.상품명 = 'spt_monitor' ORDER BY b.상품ID, b.기준일자

--10       1             |--Top(TOP EXPRESSION:((10)))

--10       1                  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003], [Expr1006]) WITH ORDERED PREFETCH)

--10       1                       |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[상품ID]))

--1         1                       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

--1         1                       |    |    |--Index Seek(OBJECT:([jwjung].[dbo].[상품].[상품_x01] AS [a]), SEEK:([a].[상품명]=N'spt_monitor') ORDERED FORWARD)

--1         1                      |    |    |--RID Lookup(OBJECT:([jwjung].[dbo].[상품] AS [a]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

--10       1                       |    |--Index Seek(OBJECT:([jwjung].[dbo].[일별상품가격].[일별상품가격_pk] AS [b]), SEEK:([b].[상품ID]=[jwjung].[dbo].[상품].[상품ID] as [a].[상품ID] AND [b].[기준일자] >= '20090301' AND [b].[기준일자] <= '20090331') ORDERED FORWARD)

--10       10                     |--RID Lookup(OBJECT:([jwjung].[dbo].[일별상품가격] AS [b]), SEEK:([Bmk1003]=[Bmk1003]) LOOKUP ORDERED FORWARD)

 

--(10개 행이 영향을 받음)

--테이블 '일별상품가격'. 검색 수 1, 논리적 읽기 수 12, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

--테이블 '상품'. 검색 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

EXEC sp_helpindex '상품';

--index_name      index_description             index_keys

--상품_pk             nonclustered, unique, primary key located on PRIMARY         상품ID

--상품_x01           nonclustered located on PRIMARY 상품명, 상품ID

 

EXEC sp_helpindex '일별상품가격';

--index_name      index_description             index_keys

--일별상품가격_pk nonclustered, unique, primary key located on PRIMARY         상품ID, 기준일자

 

 

SET STATISTICS PROFILE OFF

SET STATISTICS TIME OFF

SET STATISTICS IO OFF