SQL 성능을 높이는 4가지 방법

응용 프로그램이 더 빠르게 실행되도록 하기 위해서는 여기 저기를 조금씩 손보기만 하면 됩니다. 문제는 어떻게 손보는가에 있죠! 조만간 응용 프로그램의 SQL 쿼리가 여러분이 의도한 방식대로 응답하지 않는 상황에 직면하게 될 것입니다. 원하는 데이터를 반환하지 않거나 아니면 너무 길어서 적합하지 않습니다. SQL이 보고서나 엔터프라이즈 응용 프로그램의 속도를 떨어뜨려 엄청난 시간 동안 기다려야 하는 상황이 발생하면 사용자는 그리 즐거울 수 없을 것입니다. 부모님이 자녀가 귀가 시간을 어긴 이유를 듣고 싶어하지 않듯 사용자 역시 쿼리가 그렇게 오래 걸리는 이유를 알고 싶어하지 않습니다. (“엄마, 죄송해요. LEFT JOIN을 너무 많이 사용했네요.”) 사용자는 응용 프로그램이 신속히 응답하고 보고서가 분석 데이터를 즉시 반환하기를 원합니다. 저 역시도 웹 서핑 중 한 페이지를 로드하는데 10초(사실 5초 정도) 이상이 걸리면 참을 수가 없어집니다. 
<V></V>가장 궁금한 문제 즉, SQL INSERT를 실행한 후 어떻게 IDENTITY 값을 검색하는지부터 살펴 보겠습니다. 문제는, 그 값을 검색하는 쿼리를 어떻게 작성하는지가 아니라 언제 어디서 작성하는가 입니다. SQL Server에서, 활성 데이터베이스 연결에서 가장 최신 SQL 문 실행에 의해 만들어진 IDENTITY 값을 검색하는 문은 다음과 같습니다.

SELECT @@IDENTITY

<V></V>이 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이 반환되는 첫 번째이자 유일한 값이라면 훨씬 쉽고 효율적일 것입니다. 
<V></V>이 테크닉이 작업을 실행하긴 하지만 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. 임시 테이블 
<V></V>종종 쿼리는 GROUP BY 후 표준 쿼리를 실행해야만 수집할 수 있는 다른 데이터에 데이터를 조인해야 하는 경우가 있습니다. 예를 들어 가장 최근 주문 5건에 대한 정보를 반환하고 싶다면 먼저 그 최근 주문 5건이 무엇인지부터 알아야 합니다. 이 주문은 주문 ID를 반환하는 SQL 쿼리를 사용하면 검색할 수 있습니다. 이 데이터는 임시 테이블에 저장될 수 있으며 그런 다음 Product 테이블로 조인되어 그 주문에 대해 판매된 제품 수량을 반환합니다.