쿼리 성능 튜닝(SQL Server Compact)

아티클
05/27/2011

사용하는 쿼리를 최적화하여 SQL Server Compact 4.0 응용 프로그램의 성능을 향상시킬 수 있습니다. 다음 섹션에서는 쿼리 성능 최적화를 위해 사용할 수 있는 기술에 대해 대략적으로 설명합니다.

인덱스 조정

유용한 인덱스를 만드는 것은 쿼리 성능을 향상시키는 가장 중요한 방법 중 하나입니다. 유용한 인덱스는 디스크 I/O 작업을 줄이고 시스템 리소스를 덜 사용하면서 데이터를 찾을 수 있도록 해줍니다.

유용한 인덱스를 만들려면 데이터의 사용 방식, 쿼리의 유형과 실행 빈도, 쿼리 프로세서가 인덱스를 사용하여 데이터를 찾는 방법 등을 알아야 합니다.

만들 인덱스를 선택한 후 사용자 작업 성능에 가장 큰 영향을 미치게 될 주요 쿼리를 검사합니다. 이러한 쿼리에 도움이 되는 인덱스를 만듭니다. 인덱스를 추가한 후 쿼리를 다시 실행하여 성능이 향상되었는지 확인합니다. 성능이 향상되지 않으면 해당 인덱스를 제거합니다.

대부분의 성능 최적화 기술과 마찬가지로 이 방법에도 장단점이 있습니다. 예를 들어 SELECT 쿼리의 경우 인덱스가 많을수록 실행 속도가 빨라집니다. 그러나 DML(INSERT, UPDATE 및 DELETE) 연산의 경우는 각 연산에서 유지 관리해야 하는 인덱스가 늘어나 속도가 크게 느려집니다. 따라서 쿼리가 주로 SELECT 문으로 구성된 경우 인덱스가 많을수록 유리합니다. 그러나 응용 프로그램이 DML 연산을 많이 수행하는 경우에는 만들 인덱스 수를 신중히 고려해야 합니다.

SQL Server Compact에서는 쿼리의 평가와 최적화에 도움이 되는 실행 계획을 지원합니다. 또한 SQL Server Compact에서는 SQL Server 2008 R2와 동일한 실행 계획 스키마를 사용합니다. 단, SQL Server Compact에서는 연산자의 하위 집합을 사용합니다. 자세한 내용은 https://schemas.microsoft.com/sqlserver/2004/07/showplan/에서 Microsoft Showplan Schema를 참조하십시오.

다음 섹션에서는 유용한 인덱스를 만드는 방법에 대해 자세히 설명합니다.

선택도 높은 인덱스 만들기

중요 쿼리의 WHERE 절에 사용된 열에 인덱싱을 하면 일반적으로 성능이 향상됩니다. 그러나 이러한 효과는 인덱스의 선택도에 따라 좌우됩니다. 선택도란 총 행에 대한 한정 행의 비율을 말합니다. 이 비율이 낮으면 인덱스의 선택도가 높은 것입니다. 이 인덱스는 대부분의 행을 제거하여 결과 집합의 크기를 크게 줄일 수 있습니다. 따라서 이 인덱스는 만들면 유용합니다. 반대로 선택도가 낮은 인덱스는 그다지 유용하지 않습니다.

고유 인덱스는 선택도가 가장 높습니다. 일치하는 행이 하나만 존재하므로 정확히 한 행만 반환하는 쿼리에 가장 유용합니다. 예를 들어 고유 ID 열의 인덱스는 특정 행을 빠르게 찾는 데 도움이 됩니다.

인덱스의 선택도는 SQL Server Compact 테이블에서 sp_show_statistics 저장 프로시저를 실행하여 확인할 수 있습니다. 예를 들어 두 열 "Customer ID"와 "Ship Via"의 선택도를 평가하는 경우 다음 저장 프로시저를 실행합니다.

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI 0 7 0

ANATR 0 4 0

ANTON 0 13 0

AROUT 0 14 0

BERGS 0 23 0

BLAUS 0 8 0

BLONP 0 14 0

BOLID 0 7 0

BONAP 0 19 0

BOTTM 0 20 0

BSBEV 0 12 0

CACTU 0 6 0

CENTC 0 3 0

CHOPS 0 12 0

COMMI 0 5 0

CONSH 0 4 0

DRACD 0 9 0

DUMON 0 8 0

EASTC 0 13 0

ERNSH 0 33 0

(90 rows affected)

및

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1 0 320 0

2 0 425 0

3 0 333 0

(3 rows affected)

"Customer ID" 열을 보여주는 결과는 중복도가 훨씬 낮습니다. 즉, 이 열의 인덱스는 "Ship Via" 열의 인덱스보다 선택도가 높습니다.

이 저장 프로시저의 사용에 대한 자세한 정보는 sp_show_statistics (SQL Server Compact), sp_show_statistics_steps (SQL Server Compact) 및 sp_show_statistics_columns(SQL Server Compact)를 참조하십시오.

다중 열 인덱스 만들기

다중 열 인덱스는 단일 열 인덱스를 확장한 것입니다. 다중 열 인덱스는 키 열의 접두사 집합과 일치하는 필터 식을 평가하는 데 유용합니다. 예를 들어 복합 인덱스 CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 는 다음 쿼리를 평가하는 데 도움이 됩니다.

... WHERE "Last Name" = 'Doe'
... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

그러나 이 인덱스는 다음 쿼리에는 유용하지 않습니다.

... WHERE "First Name" = 'John'

다중 열 인덱스를 만들 때는 가장 선택도가 높은 열을 키의 맨 왼쪽에 두어야 합니다. 이렇게 하면 여러 식을 일치시킬 때 인덱스의 선택도가 더 높아집니다.

작은 테이블의 인덱싱 금지

작은 테이블은 하나 또는 소수의 데이터 페이지에 맞는 내용이 포함된 테이블입니다. 크기가 작은 테이블의 경우 일반적으로 인덱싱보다는 테이블 스캔이 더 효율적이므로 인덱싱을 피합니다. 이렇게 하면 인덱스 페이지의 로드와 처리에 드는 비용이 절감됩니다. 아주 작은 테이블에 대해 인덱스를 만들지 않으면 최적화 프로그램이 이 인덱스를 선택할 가능성이 사라집니다.

SQL Server Compact에서는 4Kb 페이지에 데이터를 저장합니다. 저장소 엔진의 오버헤드 때문에 실제 페이지 수는 약간 더 많을 수 있지만 다음 식을 사용하여 대략적인 페이지 수를 계산할 수 있습니다.

<열의 크기 합계(바이트)> * <행 수>

<페이지 수> = -----------------------------------------------------------------

4096

예를 들어 테이블에 다음 스키마가 포함된 경우를 가정해 봅니다.

열 이름	형식(크기)
Order ID	INTEGER(4바이트)
Product ID	INTEGER(4바이트)
Unit Price	MONEY(8바이트)
Quantity	SMALLINT(2바이트)
Discount	REAL(4바이트)

이 테이블에는 2820개의 행이 있습니다. 이 수식에 따르면 데이터를 저장하는 데 약 16페이지가 사용됩니다.

<페이지 수> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15.15

인덱싱 대상 선택

항상 기본 키에 인덱스를 만드는 것이 좋습니다. 외래 키에 인덱스를 만드는 것이 유용한 경우도 있습니다. 기본 키와 외래 키가 테이블 조인에 주로 사용되기 때문입니다. 이러한 키에 인덱스를 만들면 최적화 프로그램은 보다 효율적인 인덱스 조인 알고리즘을 사용할 수 있습니다. 쿼리가 다른 열을 사용하여 테이블을 조인하는 경우에도 같은 이유에서 해당 열에 인덱스를 만드는 것이 유용합니다.

PRIMARY KEY 및 FOREIGN KEY 제약 조건이 생성되면 SQL Server Compact에서는 자동으로 해당 키에 대한 인덱스를 만들어 쿼리를 최적화할 때 사용합니다. 기본 키와 외래 키는 작은 것이 좋습니다. 그래야 조인 속도가 빨라집니다.

필터 절에 인덱스 사용

인덱스를 사용하면 특정 유형의 필터 절을 보다 빠르게 평가할 수 있습니다. 모든 필터 절은 쿼리의 최종 결과 집합 크기를 줄여주지만 스캔해야 하는 데이터 양을 줄여주는 필터 절도 있습니다.

검색 인수(SARG)는 정확한 일치, 값의 범위 또는 AND로 조인된 둘 이상의 항목 결합을 지정하여 검색을 제한합니다. 이 인수는 다음 중 한 가지 형식을 취합니다.

열 연산자 <상수 또는 변수>

<상수 또는 변수> 연산자 열

SARG 연산자에는 =, >, <, >=, <=, IN, BETWEEN이 포함되며 LIKE가 포함될 수도 있습니다. 접두어 일치의 경우 LIKE 'John%'과 같이 사용할 수 있습니다. SARG는 AND로 조인된 여러 조건을 포함할 수 있습니다. SARG는 다음과 같이 특정 값과 일치하는 쿼리가 될 수 있습니다.

"Customer ID" = 'ANTON'
'Doe' = "Last Name"

SARG는 다음과 같이 값 범위와 일치하는 쿼리가 될 수 있습니다.

"Order Date" > '1/1/2002'
"Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
"Customer ID" IN ('ANTON', 'AROUT')

SQL Server Compact 쿼리 프로세서는 각 행이 필터 절을 만족하는지 여부를 확인하기 위해 모든 행을 평가해야 하기 때문에 SARG 연산자를 사용하지 않는 식은 성능 향상에 도움이 되지 않습니다. 따라서 SARG 연산자를 사용하지 않는 식에는 인덱스가 유용하지 않습니다. SARG가 아닌 연산자에는 NOT, <>, NOT EXISTS, NOT IN, NOT LIKE 및 내장 함수가 포함됩니다.

쿼리 최적화 프로그램 사용

SQL Server Compact 최적화 프로그램에서는 기본 테이블에 대한 액세스 방법을 결정할 때 SARG 절에 대한 인덱스가 존재하는지 여부를 확인합니다. 인덱스가 있으면 최적화 프로그램은 반환되는 행 수를 계산하여 인덱스를 평가합니다. 그런 다음 해당 인덱스를 사용하여 한정하는 행을 찾는 데 필요한 비용을 계산합니다. 테이블 스캔보다 비용이 적게 들면 인덱스된 액세스를 선택합니다. 인덱스의 첫 번째 열 또는 열의 접두어 집합이 SARG에 사용되고 SARG가 하한, 상한 또는 두 값을 모두 설정하여 검색을 제한하는 경우 인덱스가 유용할 수 있습니다.

응답 시간과 총 시간 이해

응답 시간은 쿼리가 첫 번째 레코드를 반환하는 데 드는 시간입니다. 총 시간은 쿼리가 모든 레코드를 반환하는 데 드는 시간입니다. 대화형 응용 프로그램의 경우 응답 시간은 사용자가 쿼리가 처리 중임을 시각적으로 확인하는 데 걸리는 인지 시간이므로 중요합니다. 일괄 처리 응용 프로그램에서는 총 시간이 전체적인 처리량을 반영합니다. 응용 프로그램 및 쿼리에 대한 성능 기준을 결정한 다음 그에 맞게 쿼리를 디자인해야 합니다.

예를 들어 100개의 레코드를 반환하고 처음 다섯 개 레코드로 목록을 채우는 쿼리를 가정해 봅니다. 이 경우 100개의 레코드를 모두 반환하는 데 걸리는 시간은 문제가 되지 않습니다. 대신 목록을 채우는 데 필요한 처음 몇 개의 레코드를 빨리 반환하기만 하면 됩니다.

많은 쿼리 연산은 중간 결과를 저장하지 않고 수행할 수 있습니다. 이를 일컬어 파이프라인 연산이라고 합니다. 파이프라인 연산의 예로는 프로젝션, 선택, 조인을 들 수 있습니다. 이러한 연산으로 구현된 쿼리는 결과를 즉시 반환할 수 있습니다. SORT 및 GROUP-BY와 같은 기타 연산의 경우 부모 연산으로 결과를 반환하기 위해 모든 입력을 사용해야 합니다. 이를 일컬어 구체화가 필요한 연산이라고 합니다. 이러한 연산으로 구현된 쿼리는 구체화 때문에 일반적으로 초기에 작업 시간이 지연됩니다. 이러한 초기 지연 이후에는 레코드가 아주 빠르게 반환됩니다.

응답 시간이 문제가 되는 쿼리는 구체화를 피해야 합니다. 예를 들어 인덱스를 사용하여 ORDER-BY 쿼리를 구현하면 정렬을 사용할 때보다 응답 시간이 단축됩니다. 다음 섹션에서는 이 내용을 좀 더 자세히 설명합니다.

응답 시간 단축을 위한 ORDER-BY / GROUP-BY / DISTINCT 열 인덱스

ORDER-BY, GROUP-BY 및 DISTINCT 연산은 모두 일종의 정렬입니다. SQL Server Compact 쿼리 프로세서는 두 가지 방법으로 정렬을 구현합니다. 레코드가 인덱스에 의해 이미 정렬된 경우 프로세서는 인덱스만 사용하면 됩니다. 그렇지 않으면 임시 작업 테이블을 사용하여 레코드를 먼저 정렬해야 합니다. 이러한 사전 정렬 작업은 CPU 성능이 낮고 메모리가 제한된 장치에서 초기 지연 시간을 크게 증가시킬 수 있으므로 응답 시간이 중요한 경우에는 이를 피해야 합니다.

다중 열 인덱스 컨텍스트에서 ORDER-BY 또는 GROUP-BY가 특정 인덱스를 고려하게 하려면 ORDER-BY 또는 GROUP-BY 열이 인덱스 열의 접두어 집합과 정확한 순서로 일치해야 합니다. 예를 들어 CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) 인덱스를 사용하면 다음 쿼리를 최적화할 수 있습니다.

... ORDER BY / GROUP BY "Last Name" ...
... ORDER BY / GROUP BY "Last Name", "First Name" ...

그러나 다음 쿼리를 최적화하는 데는 도움이 되지 않습니다.

... ORDER BY / GROUP BY "First Name" ...
... ORDER BY / GROUP BY "First Name", "Last Name" ...

DISTINCT 연산이 다중 열 인덱스를 고려할 수 있도록 하려면 프로젝션 목록이 모든 인덱스 열과 일치해야 합니다. 이 경우 순서까지 일치할 필요는 없습니다. 앞의 인덱스는 다음 쿼리를 최적화하는 데 도움이 될 수 있습니다.

... DISTINCT "Last Name", "First Name" ...
... DISTINCT "First Name", "Last Name" ...

그러나 다음 쿼리를 최적화하는 데는 도움이 되지 않습니다.

... DISTINCT "First Name" ...
... DISTINCT "Last Name" ...

참고

쿼리가 항상 자체적으로 고유한 열을 반환하는 경우 DISTINCT 키워드는 오버헤드만 증가시키므로 지정하지 않도록 합니다.

JOIN을 사용하도록 하위 쿼리 다시 작성

JOIN을 사용하도록 하위 쿼리를 다시 작성하여 성능을 향상시킬 수 있습니다. JOIN을 만들면 쿼리에서 정의한 것과 다른 순서로 테이블을 평가할 수 있다는 장점이 있습니다. 하위 쿼리를 사용하면 하위 쿼리 식을 평가하기 위해 하위 쿼리의 모든 행을 검색할 필요가 없다는 장점이 있습니다. 예를 들어 EXISTS 하위 쿼리는 첫 번째 한정 행을 만날 경우 TRUE를 반환할 수 있습니다.

참고

SQL Server Compact 쿼리 프로세서는 JOIN을 사용하도록 항상 IN 하위 쿼리를 다시 작성합니다. IN 하위 쿼리 절을 포함하는 쿼리에서는 이 방법을 사용하지 않아도 됩니다.

예를 들어 할인율이 25% 이상인 품목이 하나 이상 포함된 모든 주문을 확인하려는 경우 다음과 같은 EXISTS 하위 쿼리를 사용할 수 있습니다.

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

JOIN을 사용하여 다음과 같이 다시 작성할 수 있습니다.

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

OUTER JOIN의 사용 제한

OUTER JOIN은 최적화 프로그램이 INNER JOIN 테이블의 경우와 달리 OUTER JOIN 테이블의 조인 순서를 다시 정렬하지 않는다는 점에서 INNER JOIN과 다릅니다. 외부 테이블(LEFT OUTER JOIN의 왼쪽 테이블 및 RIGHT OUTER JOIN의 오른쪽 테이블)에 먼저 액세스한 후 내부 테이블에 액세스합니다. 이 고정된 조인 순서로 인해 최적화되지 않은 실행 계획이 생성될 수 있습니다.

INNER JOIN을 포함하는 쿼리에 대한 자세한 내용은 Microsoft 기술 자료를 참조하십시오.

매개 변수화된 쿼리 사용

응용 프로그램이 상수만 약간 다른 일련의 쿼리를 실행할 경우 매개 변수화된 쿼리를 사용하여 성능을 향상시킬 수 있습니다. 예를 들어 고객별로 주문을 반환하려면 다음 쿼리를 실행합니다.

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

매개 변수화된 쿼리는 쿼리를 한 번 컴파일한 후 컴파일된 계획을 여러 차례 실행하여 성능을 향상시킵니다. 프로그래밍 차원에서 캐시된 쿼리 계획을 포함하는 명령 개체를 그대로 보존해야 합니다. 이전 명령 개체를 제거하고 새 명령 개체를 만들면 캐시된 계획이 삭제됩니다. 이 경우 쿼리를 다시 컴파일해야 합니다. 여러 개의 매개 변수화된 쿼리를 인터리브 방식으로 실행해야 하는 경우 매개 변수화된 쿼리의 실행 계획을 캐시하는 여러 개의 명령 개체를 만들 수 있습니다. 이렇게 하면 모든 쿼리를 다시 컴파일해야 하는 상황을 피할 수 있습니다.

필요할 때만 쿼리 실행

SQL Server Compact 쿼리 프로세서는 관계형 데이터베이스에 저장된 데이터를 쿼리할 수 있는 강력한 도구입니다. 그러나 쿼리 프로세서와 관련된 비용이 기본적으로 발생합니다. 쿼리 프로세서가 실행 계획을 실제로 수행하려면 먼저 이를 컴파일, 최적화 및 생성해야 합니다. 단시간에 끝나는 간단한 쿼리에서도 마찬가지입니다. 따라서 쿼리를 직접 구현하면 성능을 크게 향상시킬 수 있습니다. 밀리초를 다투는 핵심 구성 요소의 경우 간단한 쿼리는 직접 구현하는 것이 바람직합니다. 크고 복잡한 쿼리의 경우에는 쿼리 프로세서에 이 작업을 맡기는 것이 좋습니다.

예를 들어 주문 ID별로 정렬된 일련의 주문에 대해 고객 ID를 조회하려는 경우를 가정해 봅니다. 이 작업은 다음과 같은 두 가지 방법으로 수행할 수 있습니다. 첫째, 각 조회에 대해 다음 단계를 수행합니다.

Orders 기본 테이블을 엽니다.
특정 "Order ID"를 사용하여 행을 찾습니다.
"Customer ID"를 검색합니다.

둘째, 각 조회에 대해 다음 쿼리를 실행합니다.

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

SQL Server Compact 쿼리 프로세서는 선언적 SQL 문을 수동 구현이 가능한 동일한 세 개의 연산으로 변환하므로 쿼리를 사용하는 방법이 직접 실행하는 방법보다 간단하지만 속도는 더 느립니다. 이 세 단계는 순차적으로 수행됩니다. 어느 방법을 선택할 것인가는 응용 프로그램에서 간편성과 성능 중 어느 것이 더 중요하냐에 따라 결정됩니다.