정렬의 최적화 (03.정렬을 최소화하는 SQL 작성)

/* 03.정렬을 최소화하는 SQL 작성 */

 

USE jwjung

GO

IF OBJECT_ID('Shippers') IS NOT NULL

             DROP TABLE Shippers

GO

IF OBJECT_ID('Orders') IS NOT NULL

             DROP TABLE Orders

GO

 

SELECT * INTO Shippers FROM Northwind.dbo.Shippers

GO

SELECT a.* INTO Orders FROM Northwind.dbo.Orders a, Northwind.dbo.Orders b

GO

 

SELECT COUNT(*) FROM Shippers;

GO

--3

SELECT COUNT(*) FROM Orders;

GO

--688900

 

ALTER TABLE Shippers ADD CONSTRAINT Shippers_pk PRIMARY KEY CLUSTERED (ShipperID)

GO

CREATE NONCLUSTERED INDEX Orders_x01 ON Orders (ShipVia, ShippedDate)

GO

 

EXEC sp_helpindex 'Shippers';

--index_name      index_description             index_keys

--Shippers_pk      clustered, unique, primary key located on PRIMARY ShipperID

 

EXEC sp_helpindex 'Orders';

--index_name      index_description             index_keys

--Orders_x01      nonclustered located on PRIMARY ShipVia, ShippedDate

 

 

SET STATISTICS PROFILE ON

SET STATISTICS TIME ON

SET STATISTICS IO ON

 

--(1) 불필요한 distinct 제거

SELECT DISTINCT ShipVia

FROM Orders

WHERE ShippedDate >= CONVERT(DATETIME, '19970101', 112)

             AND ShippedDate < CONVERT(DATETIME, '19980101', 112)

ORDER BY ShipVia

GO

 

--Rows   Executes             StmtText

--3         1           SELECT DISTINCT ShipVia FROM Orders WHERE ShippedDate >= CONVERT(DATETIME, '19970101', 112) AND ShippedDate < CONVERT(DATETIME, '19980101', 112) ORDER BY ShipVia

--3         1             |--Stream Aggregate(GROUP BY:([jwjung].[dbo].[Orders].[ShipVia]))

--330340            1                  |--Index Scan(OBJECT:([jwjung].[dbo].[Orders].[Orders_x01]),  WHERE:([jwjung].[dbo].[Orders].[ShippedDate]>='1997-01-01 00:00:00.000' AND [jwjung].[dbo].[Orders].[ShippedDate]<'1998-01-01 00:00:00.000') ORDERED FORWARD)

 

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

--테이블 'Orders'. 검색 수 1, 논리적 읽기 수 2229, 물리적 읽기 수 0, 미리 읽기 수 18, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

 

--CPU 시간 = 78밀리초, 경과 시간 = 110밀리초

 

--테이블 간의 관계를 알고 있다면, EXISTS를 사용하여 쿼리를 변경할 수 있다.

--(물론 Orders 테이블의 ShipVia 컬럼에 null 또는 잘못된 값이 없어야 하며, 혹시 있더라도

--그런 값은 무시한다는 전제 조건이 있어야 한다.)

 

SELECT a.ShipperID

FROM Shippers a

WHERE EXISTS

             (

             SELECT 1

             FROM Orders x

             WHERE x.ShipVia = a.ShipperID

                           AND x.ShippedDate >= CONVERT(DATETIME, '19970101', 112)

                           AND x.ShippedDate < CONVERT(DATETIME, '19980101', 112)

             )

ORDER BY a.ShipperID

GO

 

--Rows   Executes             StmtText

--3         1           SELECT a.ShipperID FROM Shippers a WHERE EXISTS ( SELECT 1 FROM Orders x WHERE x.ShipVia = a.ShipperID AND x.ShippedDate >= CONVERT(DATETIME, '19970101', 112)    AND x.ShippedDate < CONVERT(DATETIME, '19980101', 112)             ) ORDER BY a.ShipperID

--3         1             |--Nested Loops(Left Semi Join, OUTER REFERENCES:([a].[ShipperID]))

--3         1                  |--Clustered Index Scan(OBJECT:([jwjung].[dbo].[Shippers].[Shippers_pk] AS [a]), ORDERED FORWARD)

--3         3                  |--Index Seek(OBJECT:([jwjung].[dbo].[Orders].[Orders_x01] AS [x]), SEEK:([x].[ShipVia]=[jwjung].[dbo].[Shippers].[ShipperID] as [a].[ShipperID] AND [x].[ShippedDate] >= '1997-01-01 00:00:00.000' AND [x].[ShippedDate] < '1998-01-01 00:00:00.000') ORDERED FORWARD)

 

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

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

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

 

--CPU 시간 = 0밀리초, 경과 시간 = 0밀리초

 

--EXISTS는 세미 조인(Semi Join) 방식으로 수행되는데, 메인 쿼리의 로우를 가지고 서브쿼리의 로우와 끝까지 조인하는 게 아니라

--(메인 쿼리에서 제공된 로우별로) 한 번만 조인에 성공하면 참을 리턴하고 조인을 멈춘다.

 

 

--(2) 비효율적인 count 개선

DECLARE @cnt INT

 

SELECT @cnt = count(*)

FROM Orders

WHERE ShipVia = 3

             AND ShippedDate >= CONVERT(DATETIME, '19970101', 112)

             AND ShippedDate < CONVERT(DATETIME, '19980101', 112)

 

IF @cnt > 0

             PRINT '데이터 있음 : ' + CONVERT(VARCHAR, @cnt)

ELSE

             PRINT '데이터 없음'

 

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

 

--Rows   Executes             StmtText

--1         1           SELECT @cnt = count(*) FROM Orders WHERE ShipVia = 3 AND ShippedDate >= CONVERT(DATETIME, '19970101', 112) AND ShippedDate < CONVERT(DATETIME, '19980101', 112)

--0         0             |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))

--1         1                  |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))

--103750            1                       |--Index Seek(OBJECT:([jwjung].[dbo].[Orders].[Orders_x01]), SEEK:([jwjung].[dbo].[Orders].[ShipVia]=(3) AND [jwjung].[dbo].[Orders].[ShippedDate] >= '1997-01-01 00:00:00.000' AND [jwjung].[dbo].[Orders].[ShippedDate] < '1998-01-01 00:00:00.000') ORDERED FORWARD)

 

 

--데이터의 존재 여부만 확인

DECLARE @cnt INT

 

SELECT @cnt = COUNT(*)

WHERE EXISTS

             (

                           SELECT 1

                           FROM Orders

                           WHERE ShipVia = 3

                                        AND ShippedDate >= CONVERT(DATETIME, '19970101', 112)

                                        AND ShippedDate < CONVERT(DATETIME, '19980101', 112)

             )

 

IF @cnt > 0

             PRINT '데이터 있음 : ' + CONVERT(VARCHAR, @cnt)

ELSE

             PRINT '데이터 없음'

 

--Rows   Executes             StmtText

--1         1           SELECT @cnt = COUNT(*) WHERE EXISTS (SELECT 1 FROM Orders WHERE ShipVia = 3 AND ShippedDate >= CONVERT(DATETIME, '19970101', 112) AND ShippedDate < CONVERT(DATETIME, '19980101', 112)             )

--0         0             |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1008],0)))

--1         1                  |--Stream Aggregate(DEFINE:([Expr1008]=Count(*)))

--1         1                       |--Nested Loops(Left Semi Join)

--1         1                            |--Constant Scan

--1         1                            |--Index Seek(OBJECT:([jwjung].[dbo].[Orders].[Orders_x01]), SEEK:([jwjung].[dbo].[Orders].[ShipVia]=(3) AND [jwjung].[dbo].[Orders].[ShippedDate] >= '1997-01-01 00:00:00.000' AND [jwjung].[dbo].[Orders].[ShippedDate] < '1998-01-01 00:00:00.000') ORDERED FORWARD)

 

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

 

 

DECLARE @cnt INT

 

SELECT @cnt = COUNT(*)

FROM (

             SELECT TOP 1 ShipVia

             FROM Orders

             WHERE ShipVia = 3

                           AND ShippedDate >= CONVERT(DATETIME, '19970101', 112)

                           AND ShippedDate < CONVERT(DATETIME, '19980101', 112)

             ) a

 

IF @cnt > 0

             PRINT '데이터 있음 : ' + CONVERT(VARCHAR, @cnt)

ELSE

             PRINT '데이터 없음'

 

--Rows   Executes             StmtText

--1         1           SELECT @cnt = COUNT(*) FROM ( SELECT TOP 1 ShipVia FROM Orders           WHERE ShipVia = 3 AND ShippedDate >= CONVERT(DATETIME, '19970101', 112) AND ShippedDate < CONVERT(DATETIME, '19980101', 112)      ) a

--0         0             |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))

--1         1                  |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))

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

--1         1                            |--Index Seek(OBJECT:([jwjung].[dbo].[Orders].[Orders_x01]), SEEK:([jwjung].[dbo].[Orders].[ShipVia]=(3) AND [jwjung].[dbo].[Orders].[ShippedDate] >= '1997-01-01 00:00:00.000' AND [jwjung].[dbo].[Orders].[ShippedDate] < '1998-01-01 00:00:00.000') ORDERED FORWARD)

 

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

 

 

--//(3) 부적절한 UNION 대신 UNION ALL 사용

USE jwjung

GO

 

IF OBJECT_ID('Orders_2008') IS NOT NULL

             DROP TABLE Orders_2008

GO

IF OBJECT_ID('Orders_2009') IS NOT NULL

             DROP TABLE Orders_2009

GO

 

SELECT a.*

             , '2008' + SUBSTRING(CONVERT(CHAR(8), OrderDate, 112), 5, 4) AS 기준일자

INTO Orders_2008

FROM Northwind.dbo.Orders a

GO

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

SELECT a.*

             , '2009' + SUBSTRING(CONVERT(CHAR(8), OrderDate, 112), 5, 4) AS 기준일자

INTO Orders_2009

FROM Northwind.dbo.Orders a

GO

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

 

CREATE UNIQUE INDEX Orders_2008_x01 ON Orders_2008 (기준일자, OrderID)

GO

 

CREATE UNIQUE INDEX Orders_2009_x01 ON Orders_2009 (기준일자, OrderID)

GO

 

SELECT * FROM Orders_2008;

GO

SELECT * FROM Orders_2009;

GO

 

SELECT OrderID, 기준일자, Freight

FROM Orders_2008

WHERE Freight >= 50

UNION

SELECT OrderID, 기준일자, Freight

FROM Orders_2009

WHERE Freight >= 50

GO

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

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

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

 

--Rows   Executes             StmtText

--720     1           SELECT OrderID, 기준일자, Freight FROM Orders_2008 WHERE Freight >= 50 UNION SELECT OrderID, 기준일자, Freight FROM Orders_2009 WHERE Freight >= 50

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

--720     1                  |--Concatenation

--360     1                       |--Table Scan(OBJECT:([jwjung].[dbo].[Orders_2008]), WHERE:([jwjung].[dbo].[Orders_2008].[Freight]>=($50.0000)))

--360     1                       |--Table Scan(OBJECT:([jwjung].[dbo].[Orders_2009]), WHERE:([jwjung].[dbo].[Orders_2009].[Freight]>=($50.0000)))

 

--CPU 시간 = 0밀리초, 경과 시간 = 55밀리초

 

--중복된 로우를 제거하고자 'Sort(DISTINCT ORDER BY)' 오퍼레이션이 나타났다.

 

SELECT OrderID, 기준일자, Freight

FROM Orders_2008

WHERE Freight >= 50

UNION ALL

SELECT OrderID, 기준일자, Freight

FROM Orders_2009

WHERE Freight >= 50

GO

 

--Rows   Executes             StmtText

--720     1           SELECT OrderID, 기준일자, Freight FROM Orders_2008 WHERE Freight >= 50 UNION ALL SELECT OrderID, 기준일자, Freight FROM Orders_2009 WHERE Freight >= 50

--720     1             |--Concatenation

--360     1                  |--Table Scan(OBJECT:([jwjung].[dbo].[Orders_2008]), WHERE:([jwjung].[dbo].[Orders_2008].[Freight]>=($50.0000)))

--360     1                  |--Table Scan(OBJECT:([jwjung].[dbo].[Orders_2009]), WHERE:([jwjung].[dbo].[Orders_2009].[Freight]>=($50.0000)))

 

 

--//(4) NL 조인을 활용 - 부분적인 부분범위 처리

USE jwjung

GO

IF OBJECT_ID('Customers') IS NOT NULL

             DROP TABLE Customers

GO

 

SELECT TOP 100000

             IDENTITY(INT, 1, 1) AS CustomerID, CompanyName, ContactName, ContactTitle

             , Address, City, Region, PostalCode, Country, Phone, Fax

INTO Customers

FROM Northwind.dbo.Customers a

             , Northwind.dbo.[Order Details] b

GO

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

 

ALTER TABLE Customers ADD CONSTRAINT Customers_pk PRIMARY KEY CLUSTERED (CustomerID)

GO

 

CREATE INDEX Customers_x01 ON Customers (Country, CompanyName)

GO

 

SELECT TOP 10 * FROM Customers

ORDER BY CompanyName, CustomerID

GO

 

--Rows   Executes             StmtText

--10       1           SELECT TOP 10 * FROM Customers ORDER BY CompanyName, CustomerID

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

--10       1                  |--Parallelism(Gather Streams, ORDER BY:([jwjung].[dbo].[Customers].[CompanyName] ASC, [jwjung].[dbo].[Customers].[CustomerID] ASC))

--40       4                       |--Sort(TOP 10, ORDER BY:([jwjung].[dbo].[Customers].[CompanyName] ASC, [jwjung].[dbo].[Customers].[CustomerID] ASC))

--100000            4                            |--Clustered Index Scan(OBJECT:([jwjung].[dbo].[Customers].[Customers_pk]))

 

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

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

 

SELECT TOP 10 b.*

FROM (

             SELECT TOP 10

                           CompanyName, CustomerID

             FROM Customers

             ORDER BY CompanyName, CustomerID

             ) a

             , Customers b

WHERE a.CustomerID = b.CustomerID

ORDER BY a.CompanyName, a.CustomerID

OPTION (FORCE ORDER, LOOP JOIN)

GO

 

--Rows   Executes             StmtText

--10       1           SELECT TOP 10 b.* FROM ( SELECT TOP 10 CompanyName, CustomerID FROM Customers ORDER BY CompanyName, CustomerID          ) a, Customers b WHERE a.CustomerID = b.CustomerID ORDER BY a.CompanyName, a.CustomerID OPTION (FORCE ORDER, LOOP JOIN)

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

--10       1                  |--Parallelism(Gather Streams, ORDER BY:([jwjung].[dbo].[Customers].[CompanyName] ASC, [jwjung].[dbo].[Customers].[CustomerID] ASC))

--10       4                       |--Nested Loops(Inner Join, OUTER REFERENCES:([jwjung].[dbo].[Customers].[CustomerID]))

--10       4                            |--Parallelism(Distribute Streams, RoundRobin Partitioning)

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

--10       1                            |         |--Parallelism(Gather Streams, ORDER BY:([jwjung].[dbo].[Customers].[CompanyName] ASC, [jwjung].[dbo].[Customers].[CustomerID] ASC))

--40       4                            |              |--Sort(TOP 10, ORDER BY:([jwjung].[dbo].[Customers].[CompanyName] ASC, [jwjung].[dbo].[Customers].[CustomerID] ASC))

--100000            4                            |                   |--Index Scan(OBJECT:([jwjung].[dbo].[Customers].[Customers_x01]))

--10       10                          |--Clustered Index Seek(OBJECT:([jwjung].[dbo].[Customers].[Customers_pk] AS [b]), SEEK:([b].[CustomerID]=[jwjung].[dbo].[Customers].[CustomerID]) ORDERED FORWARD)

 

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

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

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

 

 

SET STATISTICS PROFILE OFF

SET STATISTICS TIME OFF

SET STATISTICS IO OFF