BS Blog: sql튜닝

응용 프로그램이 더 빠르게 실행되도록 하기 위해서는 여기 저기를 조금씩 손보기만 하면 됩니다. 문제는 어떻게 손보는가에 있죠! 조만간 응용 프로그램의 SQL 쿼리가 여러분이 의도한 방식대로 응답하지 않는 상황에 직면하게 될 것입니다. 원하는 데이터를 반환하지 않거나 아니면 너무 길어서 적합하지 않습니다. SQL이 보고서나 엔터프라이즈 응용 프로그램의 속도를 떨어뜨려 엄청난 시간 동안 기다려야 하는 상황이 발생하면 사용자는 그리 즐거울 수 없을 것입니다. 부모님이 자녀가 귀가 시간을 어긴 이유를 듣고 싶어하지 않듯 사용자 역시 쿼리가 그렇게 오래 걸리는 이유를 알고 싶어하지 않습니다. (“엄마, 죄송해요. LEFT JOIN을 너무 많이 사용했네요.”) 사용자는 응용 프로그램이 신속히 응답하고 보고서가 분석 데이터를 즉시 반환하기를 원합니다. 저 역시도 웹 서핑 중 한 페이지를 로드하는데 10초(사실 5초 정도) 이상이 걸리면 참을 수가 없어집니다.
이러한 문제를 해결하기 위해서는 그 문제의 원인을 찾아 내는 것이 중요합니다. 그렇다면 어디부터 시작해야 할까요? 문제의 원인은 일반적으로 데이터베이스 디자인과 그 데이터베이스를 액세스하는 쿼리에 있습니다. 이번 달 컬럼에서는 SQL Server 기반 응용 프로그램의 성능이나 확장성을 향상시키는데 사용할 수 있는 네 가지 테크닉을 살펴 보겠습니다. 그리고 LEFT JOIN과 CROSS JOIN 사용 및 IDENTITY 값 검색도 살펴 보겠습니다. 마술같은 해결책은 없다는 것을 기억하십시오. 데이터베이스와 쿼리를 조정하려면 시간이 걸리고 분석과 함께 수차례의 테스팅이 필요합니다. 여기 제시된 테크닉은 증명이 된 것이지만 사용자 응용 프로그램에 따라 더 잘 실행되는 테크닉과 그렇지 않은 테크닉이 있을 수 있습니다.

INSERT에서 IDENTITY 반환 T
가장 궁금한 문제 즉, SQL INSERT를 실행한 후 어떻게 IDENTITY 값을 검색하는지부터 살펴 보겠습니다. 문제는, 그 값을 검색하는 쿼리를 어떻게 작성하는지가 아니라 언제 어디서 작성하는가 입니다. SQL Server에서, 활성 데이터베이스 연결에서 가장 최신 SQL 문 실행에 의해 만들어진 IDENTITY 값을 검색하는 문은 다음과 같습니다.

          SELECT @@IDENTITY

이 SQL은 강력하지가 않으므로 가장 최근의 SQL 문이 INSERT가 아니거나 INSERT SQL이 아닌 다른 연결에 대해 이 SQL을 실행한다면 예상하는 값을 얻지 못할 것이라는 사실을 명심해야 합니다. IDENTITY를 검색하려면 다음과 같이 INSERT SQL 직후에 동일한 연결에서 이 코드를 실행해야 합니다.

          INSERT INTO Products (ProductName) VALUES ('Chalk')

          

          SELECT @@IDENTITY

단일 연결에서 Northwind 데이터베이스에 대해 이러한 쿼리를 실행하면 Chalk라는 신제품에 대한 IDENTITY 값이 반환될 것입니다. 따라서 ADO를 사용하는 Visual Basic 응용 프로그램에서 다음 명령문을 실행할 수 있습니다.

          Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _

          (ProductName) VALUES ('Chalk');SELECT @@IDENTITY")

          

          lProductID = oRs(0)

이 코드는 그 쿼리에 대한 행 카운트를 반환하지 않도록 SQL Server에 알리고 INSERT 문을 실행하며 그 새 행에 대해 만들어진 IDENTITY 값을 반환합니다. SET NOCOUNT ON 문은 반환된 Recordset에 새 IDENTITY 값이 들어 있는 한 행과 열이 있다는 것을 뜻합니다. 이 문이 없으면 (INSERT 문이 데이터를 반환하지 않으므로) 빈 Recorset가 반환되며 그 다음 반환되는 두 번째 Recordset에 IDENTITY 값이 들어 있습니다. 따라서 INSERT가 Recordset를 반환하도록 할 생각이 아니었던 경우에는 특히나 당황스러울 수 있습니다. 이러한 상황은, SQL Server는 행 카운트(즉, 영향을 받는 행)를 확인하고 그 카운트를 Recordset 표시로 해석하기 때문에 발생합니다. 따라서 올바른 데이터는 두 번째 Recordset로 밀려납니다. ADO에서 NextRecordset 메서드를 사용하면 이 두 번째 Recordset를 확인할 수 있지만 이 Recordset이 반환되는 첫 번째이자 유일한 값이라면 훨씬 쉽고 효율적일 것입니다.
이 테크닉이 작업을 실행하긴 하지만 SQL 문에 추가 코드가 필요합니다. 동일한 결과를 얻을 수 있는 또 다른 방법은 다음 코드에서 볼 수 있는 것처럼 INSERT 앞에 SET NOCOUNT ON 문을 사용하고 그 테이블의 FOR INSERT 트리거에 SELECT @@IDENTITY 문을 넣는 것입니다. 이렇게 하면 그 테이블에 대한 어떤 INSERT 문이나 자동으로 IDENTITY 값을 반환하게 됩니다.

          CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS 

              SELECT @@IDENTITY 

          GO

이 트리거는 Product 테이블에 INSERT가 실행될 때만 발생하므로 성공적인 INSERT 후에는 언제나 IDENTITY를 반환합니다. 이 테크닉을 사용하면 응용 프로그램 내 어디서나 동일한 방식으로 IDENTITY 값을 검색할 수 있습니다.

인라인 값 VS. 임시 테이블
종종 쿼리는 GROUP BY 후 표준 쿼리를 실행해야만 수집할 수 있는 다른 데이터에 데이터를 조인해야 하는 경우가 있습니다. 예를 들어 가장 최근 주문 5건에 대한 정보를 반환하고 싶다면 먼저 그 최근 주문 5건이 무엇인지부터 알아야 합니다. 이 주문은 주문 ID를 반환하는 SQL 쿼리를 사용하면 검색할 수 있습니다. 이 데이터는 임시 테이블에 저장될 수 있으며 그런 다음 Product 테이블로 조인되어 그 주문에 대해 판매된 제품 수량을 반환합니다.

          CREATE TABLE #Temp1 (OrderID INT NOT NULL, _

                               OrderDate DATETIME NOT NULL)

          

          INSERT INTO #Temp1 (OrderID, OrderDate)

          SELECT     TOP 5 o.OrderID, o.OrderDate

          FROM Orders o ORDER BY o.OrderDate DESC

          

          SELECT     p.ProductName, SUM(od.Quantity) AS ProductQuantity

          FROM     #Temp1 t 

              INNER JOIN [Order Details] od ON t.OrderID = od.OrderID

              INNER JOIN Products p ON od.ProductID = p.ProductID 

          GROUP BY p.ProductName

          ORDER BY p.ProductName

          

          DROP TABLE #Temp1

이 SQL 일괄 처리는 임시 테이블을 만들어 그 테이블에 데이터를 입력하고 다른 데이터를 조인한 다음 그 임시 테이블을 삭제합니다. 이 쿼리는 I/O가 많으므로 임시 테이블 대신 인라인 뷰를 사용하도록 다시 작성할 수 있습니다. 인라인 뷰는 간단하게 말하면 FROM 절에서 조인될 수 있는 쿼리입니다. 따라서 임시 테이블의 tempdb에서 많은 I/O 와 디스크 액세스를 허비하는 대신 인라인 뷰를 사용해서도 동일한 결과를 얻을 수 있습니다.

          SELECT p.ProductName, 

              SUM(od.Quantity) AS ProductQuantity

          FROM     (

              SELECT TOP 5 o.OrderID, o.OrderDate

              FROM     Orders o 

              ORDER BY o.OrderDate DESC

              ) t 

              INNER JOIN [Order Details] od ON t.OrderID = od.OrderID

              INNER JOIN Products p ON od.ProductID = p.ProductID 

          GROUP BY

              p.ProductName

          ORDER BY

              p.ProductName

이 쿼리는 이전 쿼리보다 효율적일 뿐만 아니라 더 짧습니다. 임시 테이블은 많은 리소스를 소비합니다. 따라서 데이터를 다른 쿼리에 조인시키기만 하면 되는 경우에는 인라인 뷰를 사용하여 리소스를 보존하는 방법을 시도해 보는 것이 좋을 것입니다.

LEFT JOIN과 NULL 피하기
물론, LEFT JOIN을 실행하고 NULL 값을 사용해야 할 때가 있습니다. 하지만 모든 경우에 항상 그래야 하는 것은 아닙니다. SQL 쿼리를 구성하는 방식을 변경하면 실행 시간이 몇 분이 걸리는 보고서를 단 몇 초 만에 실행되는 보고서로 만들 수 있습니다. 그리고 응용 프로그램이 원하는 방식으로 쿼리의 데이터를 변경해야 하는 경우도 종종 있습니다. TABLE 데이터 형식은 리소스 사용량을 줄여주지만 쿼리에는 최적화할 수 있는 부분이 여전히 많이 남아 있습니다. SQL에서 일반적으로 사용되는 아주 유용한 기능은 LEFT JOIN입니다. 이 기능은 첫 번째 테이블의 모든 행과 두 번째 테이블의 일치하는 모든 행, 그리고 첫 번째 테이블의 행과 일치하지 않는 두 번째 테이블의 모든 행을 검색하는데 사용할 수 있습니다. 예를 들어 모든 Customer와 그 주문을 반환하고 싶다면 LEFT JOIN은 주문을 한 Customer와 주문을 하지 않은 Customer를 표시할 것입니다.
이 도구는 지나치게 남용될 수 있습니다. LEFT JOIN은 NULL(존재하지 않음) 데이터에 대해 데이터를 일치시키는 작업을 하므로 부담이 큽니다. 이 실행을 피할 수 없는 경우도 있지만 그럴 경우 부담이 커집니다. LEFT JOIN은 INNER JOIN보다 부담이 더 크므로 LEFT JOIN을 사용하지 않도록 쿼리를 다시 작성할 수 있다면 부담이 크게 줄어들 수 있습니다(그림 1의 다이어그램 참조).

그림 1 쿼리

-1){ thisSrc = this.src; ns = thisSrc.lastIndexOf('='); ne = thisSrc.length; orgImg = thisSrc.substr(ns + 1, ne); this.src = './dnImage/' + orgImg;}" src="http://tfile.nate.com/download.asp?FileID=7514955" localfile="yes" height="160" width="109">
그림 1 쿼리

LEFT JOIN을 사용하는 쿼리의 속도를 높이기 위해서는 TABLE 데이터 형식을 만들고 첫 번째 테이블(LEFT JOIN의 왼쪽에 있는 테이블)에서 모든 행을 삽입한 다음 두 번째 테이블의 값을 사용하여 그 TABLE 데이터 형식을 업데이트합니다. 이 테크닉은 2단계 프로세스이지만 표준 LEFT JOIN과 비교하면 시간을 크게 줄일 수 있습니다. 자신의 응용 프로그램에 맞는 최고 성능의 쿼리를 얻을 때까지 각각에 대해 서로 다른 테크닉을 시도하여 시간을 측정해 보는 것이 좋습니다.
쿼리 속도를 테스트할 때는 여러 번 실행해서 평균을 구하는 것이 좋습니다. 쿼리(또는 저장 프로시저)는 SQL Server 메모리의 프로시저 캐시에 저장될 수 있으므로 처음에는 더 오래 걸리지만 계속 시도할수록 점점 더 짧아집니다. 이 외에도 쿼리 실행 중 동일한 테이블에 대해 다른 쿼리가 실행되고 있을 수도 있습니다. 그렇게 되면 다른 쿼리가 테이블을 잠궜다가 해제하는 동안 사용자의 쿼리는 기다려야 할 수도 있습니다. 예를 들어 다른 사람이 테이블의 데이터를 업데이트하는 동안 그 테이블에 대해 쿼리를 실행하면 그 업데이트가 실행되는 동안에는 쿼리 실행에 더 많은 시간이 걸릴 수 있습니다.
LEFT JOIN으로 인한 감속을 피할 수 있는 가장 쉬운 방법은 가능한 한 많이 LEFT JOIN을 중심으로 데이터베이스를 디자인하는 것입니다. 예를 들어, 한 제품에 카테고리가 있을 수도 있고 없을 수도 있다고 가정해 봅시다. 제품 테이블에 그 카테고리의 ID가 저장되어 있는데 특정 제품에 대한 카테고리가 존재하지 않는다면 그 필드에는 NULL 값을 저장할 수 있습니다. 그런 다음 LEFT JOIN을 실행하여 모든 제품과 그 카테고리를 얻습니다. “No Category” 값을 가진 카테고리를 만들 수 있으므로 NULL 값을 허용하지 않도록 외래 키 관계를 지정합니다. 이렇게 하면 이제 INNER JOIN을 사용하여 모든 제품과 그 카테고리를 검색할 수 있습니다. 추가 데이터로 인해 작업이 더 많아진 것처럼 보일 수도 있지만 SQL 일괄 처리에서 부담이 큰 LEFT JOIN을 제거할 수 있으므로 아주 유용한 테크닉입니다. 데이터베이스의 보드 전체에 이 개념을 사용하면 처리 시간이 크게 줄어듭니다. 불과 몇 초도 사용자에게는 많은 것을 의미하며 온라인 데이터베이스 응용 프로그램을 액세스하는 사용자가 많을 경우 이 시간은 더욱 길어진다는 것을 기억하십시오.

Cartesian 제품을 현명하게 사용하기
이 팁의 경우에는 일반적인 의견과 달리, 특정한 경우 Cartesian 제품을 사용하도록 주장합니다. Cartesian 제품(CROSS JOIN)은 많은 비난을 받았으며 개발자들은 종종 이 제품을 절대 사용하지 말 것을 권고 받습니다. 많은 경우 이 제품은 부담이 너무 커서 효과적이지 않습니다. 하지만 SQL의 여느 도구와 마찬가지로 이 도구 역시 적절히 사용하기만 하면 유용합니다. 예를 들어, 그 달에는 주문을 하지 않은 고객에 대해서조차 매달 데이터를 반환할 쿼리를 실행하고 싶다면 Cartesian 제품이 아주 편리할 것입니다. 그림 2 (영문)의 SQL이 바로 그렇습니다.
이 방법이 마법처럼 여겨지지는 않겠지만 Customer에서 Orders로의 표준 INNER JOIN을 실행하여 월별로 묶고 판매를 요약하면 해당 고객이 주문을 한 월만을 반환 받게 된다는 점을 생각해 보십시오. 그렇게 되면 고객이 제품을 주문하지 않은 달에 대해서는 0 값을 반환 받지 못합니다. 월별 판매가 표시된 고객별 그래프를 만들려고 한다면 시각적으로 식별할 수 있도록 월 판매가 0인 달도 포함된 그래프를 그리고 싶을 것입니다. 그림 2 (영문)의 SQL을 사용하면 Order 테이블에 비판매 관련 행이 없으므로 이 데이터는 판매 금액이 0인 달은 건너뜁니다(발생하지 않은 것은 저장하지 않는 것으로 가정됩니다).
그림 3 (영문)의 코드는 더 길지만, 판매가 없는 달에 대해서도 모든 판매 데이터를 받는다는 목표를 달성할 수 있습니다. 먼저, 지난 해 모든 달의 목록을 받아서 첫 번째 TABLE 데이터 형식 테이블(@tblMonths)에 넣습니다. 그러면 이 코드는 그 기간 동안 판매를 한 모든 고객의 회사명 목록을 받아 또 다른 TABLE 데이터 형식 테이블(@tblCus-tomers)에 넣습니다. 이 두 테이블은 실제 판매 수치를 제외하고는 결과 집합을 만드는데 필요한 기본 데이터를 모두 저장합니다.
첫 번째 테이블에는 모든 달(12행)이 표시되며 그 기간 동안 판매를 한 모든 고객은 두 번째 테이블(81)에 표시됩니다. 모든 고객이 지난 12달 동안 매달 제품을 구입하지는 않았으므로 INNER 또는 LEFT JOIN을 실행하면 매달 모든 고객을 반환하지는 않으며 고객이 뭔가를 구입한 달과 그 고객만을 반환합니다.
Cartesian 제품은 모든 달에 대해 모든 고객을 반환할 수 있습니다. Cartesian 제품은 기본적으로 첫 번째 테이블에 두 번째 테이블을 곱하므로 첫 번째 테이블의 행 수 곱하기 두 번째 테이블의 행수가 들어 있는 행 집합이 만들어 집니다. 따라서 Cartesian 제품은 @tblFinal 테이블에 972행을 반환합니다. 마지막 단계는 이 날짜 범위동안 개별 고객에 대한 월간 총 판매량을 사용해 @tblFinal 테이블을 업데이트하고 최종 행 집합을 선택하는 것입니다.
Cartesian 제품은 리소스 집약적이므로 진짜 이 제품이 필요한 것이 아니라면 CROSS JOIN을 주의해서 사용하는 것이 좋습니다. 예를 들어, 제품과 카테고리에 CROSS JOIN을 실행한 다음 WHERE 절, DISTINCT 또는 GROUP BY를 사용하여 대부분의 행을 필터링하는 경우 INNER JOIN을 사용하면 훨씬 효과적인 방식으로 동일한 결과를 얻을 수 있습니다. 월간 판매일이 표시된 그래프를 로드하고자 하는 경우처럼 모든 가능성에 대해 데이터가 반환되기를 원하는 경우 Cartesian 제품은 아주 유용할 수 있습니다. 하지만 대부분의 경우 INNER JOIN이 훨씬 더 효율적이므로 다른 용도로는 사용하지 말아야 합니다.

기타
다음은 SQL 쿼리의 효율성을 높이는데 도움이 될 수 있는 다른 몇몇 일반적인 테크닉입니다. 모든 판매 담당자를 지역별로 묶은 다음 그 판매액을 집계하려고 한다고 가정합니다. 단, 데이터베이스에서 활성으로 표시된 판매 담당자만을 원한다고 합니다. 이 판매 담당자를 지역별로 묶은 다음 HAVING 절이나 WHERE 절을 사용하여 활성 상태가 아닌 판매 담당자를 제거할 수 있습니다. WHERE 절에서 이 작업을 하면 그룹화해야 하는 행의 수가 줄어들므로 HAVING 절을 사용하는 것보다 훨씬 효과적입니다. HAVING 절에서 행 기반 기준을 필터링하면 이 쿼리는 WHERE 절에서 제거되었을 데이터를 그룹화합니다.
효율성을 높이기 위한 또 다른 트릭은 GROUP BY 절을 사용하는 대신 DISTINCT 키워드를 사용하여 고유한 데이터 행 목록을 찾아내는 것입니다. 이 경우 DISTINCT 키워드를 사용하는 SQL이 더 효과적입니다. GROUP BY는 집계 함수 (SUM, COUNT, MAX 등)를 계산해야 하는 경우를 위해 남겨 두십시오. 이 외에도, 쿼리가 항상 고유한 행을 반환하는 경우에는 IDSTINCT 키워드를 사용하지 마십시오. 이러한 경우 DISTINCT 키워드는 오버헤드만 가중시킬 뿐입니다.
다양한 테크닉을 사용하여 쿼리를 최적화하고 특정한 비즈니스 규칙을 구현할 수 있다는 것을 보았습니다. 문제는 몇몇 테크닉을 시도하여 그 성능을 비교해 보아야 한다는 것입니다. 가장 중요한 것은 테스트를 하고 또 해야 한다는 것입니다. 이 컬럼의 다음 호에서는 데이터베이스 디자인, 우수한 인덱싱 실행 기준, 그리고 SQL Server 보안 패러다임을 포함한 SQL Server 개념을 살펴 보겠습니다.

Johnny 에게 질문이나 의견 있으시면 mmdata@microsoft.com으 로 메일을 보내십시오.

Johnny Papa는 노스캐롤라이나 주 Raleigh에 있는 MJM Investigations의 정보 기술 담당 부사장으로 Professional ADO 2.5 RDS Programming with ASP 3.0(Wrox, 2000)을 저술했으며 업계 컨퍼런스에서 발표하는 모습을 자주 볼 수 있습니다. 연락처는mmdata@microsoft.com입 니다.

BS Blog

2012-09-17

DBMS/SQL] 쿼리문의 속도 향상 시리즈.1

DBMS/SQL]인덱스를 이용한 조건검색

DBMS/MSSQL] SQL 성능을 높이는 5가지 방법