복붙노트

[SQL] 조건부 집계 성능

SQL

조건부 집계 성능

우리는 다음과 같은 데이터를 보자

 IF OBJECT_ID('dbo.LogTable', 'U') IS NOT NULL  DROP TABLE dbo.LogTable

 SELECT TOP 100000 DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0) datesent 
 INTO [LogTable]
 FROM    sys.sysobjects
 CROSS JOIN sys.all_columns

나는 행의 수, 지난해 행의 수와 지난 10 년 동안 행의 수를 계산합니다. 이것은 다음과 같이 서브 쿼리 조건 집계 쿼리를 사용하거나 사용하여 달성 될 수있다

-- conditional aggregation query
SELECT
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable


-- subqueries
SELECT
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-1,GETDATE())
) last_year_cnt,
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-10,GETDATE())
) last_ten_year_cnt

당신은 쿼리 계획에 대한 질의와 모양을 수행하는 경우에 당신은 같은 것을 볼 수

분명히, 최초의 솔루션이 훨씬 더 좋은 쿼리 계획, 비용 추정, 심지어 SQL 명령 외모보다 간결하고 멋진 있습니다. 당신은 SET STATISTICS TIME ON 나는 다음과 같은 결과를 얻을 수 사용하여 쿼리의 CPU 시간을 측정하는 경우에는, (나는 거의 같은 결과를 여러 번 측정 한)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 41 ms.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 26 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

따라서, 제 2 용액은 약간 더 (또는 같은) 조건 집합을 사용하여 용액보다 성능이있다. 우리가 datesent 속성에 인덱스를 만들 경우의 차이는 더욱 분명해진다.

CREATE INDEX ix_logtable_datesent ON dbo.LogTable(DateSent)

그런 다음 두 번째 솔루션은 인덱스 대신 테이블 스캔과 질의 CPU 시간 성능이 내 컴퓨터에서 16ms로 떨어진다의 탐색 사용하기 시작합니다.

내 질문이 있습니다 : 조건부 집계 솔루션은 인덱스가없는 경우 적어도 하위 쿼리 솔루션을 능가하지 않는 이유 (1), (2) 조건부 집계 쿼리 조건 통합 솔루션 '인덱스'를 작성 (또는 재 작성 할 수 있습니다 ) 순서로 스캔하지 않도록, 또는 우리가 성능에 대해 우려하는 경우 조건부 집계는 일반적으로 부적합합니다?

(!) 참고 : 우리는 솔루션에 항상 리드를 스캔하여 모든 행의 수를 선택하기 때문에이 시나리오는 조건 집합에 대한 매우 낙관적이라고 말할 수 있습니다. 모든 행의 개수는 필요하지 않은 경우, 서브 쿼리와 다음 인덱스 용액의 스캔이없는 반면, 조건부 응집 용액 주사 어쨌든을 수행한다.

편집하다

블라디미르하기 Baranov는 기본적으로 (대단히 감사합니다) 첫 번째 질문에 대답했다. 그러나 두 번째 질문은 남아있다. StackOverflow에 매우 offten 조건 통합 솔루션을 사용하여 응답하고 그들이 많은 관심이 가장 우아하고 맑은 용액으로 인정되고 (때로는 가장 효율적인 솔루션으로 제안되고) 유치에 나는 볼 수 있습니다. 따라서, 나는 약간 질문을 일반화합니다 :

당신은 나에게 조건부 집계 특히 하위 쿼리 솔루션을 능가하는 성능을 예를 줄 수 있을까요?

단순화를 위해 우리가 오늘날 데이터베이스 서버 어쨌든 메모리에 데이터의 대부분을 유지하기 때문에 물리적 액세스가 (데이터 버퍼 캐시에) 존재하지 않는 것으로 가정하자.

해결법

  1. ==============================

    1.부질 방법은 데이터 분포에 따라 조건부 응집보다 빠르거나 느릴 수있다.

    부질 방법은 데이터 분포에 따라 조건부 응집보다 빠르거나 느릴 수있다.

    테이블에 적절한 인덱스가있는 경우 인덱스가 테이블 대신의 전체 검사의 관련 부분을 검사 할 수있는 것이기 때문에 당연히, 다음 서브 쿼리는, 그 혜택을 누릴 가능성이 높다. 적절한 인덱스를 갖는 것은 어쨌든 전체 인덱스를 스캔하기 때문에 크게 조건부 집계 방법에 도움 않을 수 있습니다. 인덱스가 테이블보다 좁은 엔진이 메모리에 적은 페이지를 읽을해야하는 경우에만 혜택이 될 것입니다.

    이 알면 당신이 선택하는 어떤 방법을 결정할 수 있습니다.

    나는 5M 행으로 더 큰 테스트 테이블을했다. 테이블에는 인덱스가 없었다. 나는 SQL 센트리 계획 Explorer를 사용하여 IO 및 CPU 통계를 측정 하였다. 나는이 테스트를 위해 SQL 서버 2014 SP1-CU7 (12.0.4459.0) 고속 64 비트를 사용했다.

    당신이 설명 된대로 실제로 원래 쿼리 즉 하위 쿼리가 3 배 높았다 읽고 빨리에도 불구하고 있었다 행동했습니다.

    인덱스가없는 테이블에 몇 가지 시도 후 나는 DATEADD 식의 값을 보유하는 조건부 집계 및 추가 변수를 다시 썼다.

    전체 시간은 훨씬 빠르게되었다.

    그럼 난 COUNT와 SUM을 대체 그것은 조금 더 빨리 다시 비트가되었다.

    결국, 조건부 집계 꽤 많이 빠른 서브 쿼리로되었다.

    캐시를 따뜻하게 (CPU = 375)

    SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

    부질 (CPU = 1,031)

    SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-1,GETDATE())
) last_year_cnt,
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-10,GETDATE())
) last_ten_year_cnt
OPTION (RECOMPILE);

    원래 조건 집계 (CPU = 1641)

    SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

    변수 조건부 응집 (CPU = 1,078)

    DECLARE @VarYear1 datetime = DATEADD(year,-1,GETDATE());
DECLARE @VarYear10 datetime = DATEADD(year,-10,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

    변수 및 COUNT 대신 SUM 조건부 응집 (CPU = 1,062)

    SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE NULL END) AS last_year_cnt,
    COUNT(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

    이러한 결과를 바탕으로 내 생각은 한 번 계산하는 스마트 충분했다 WHERE 동안 CASE는, 각 행에 대해 DATEADD를 호출한다는 것입니다. 플러스 COUNT는 작은 조금 더 효율적 SUM보다.

    결국, 조건부 집계는 약간 느린 서브 쿼리 (1,031 대 1,062)보다, 어쩌면 때문에 좀 더 효율적인 CASE보다는 그 자체, 게다가 꽤 몇 행 아웃 필터, COUNT가있다, 그래서 적은 행을 처리하는 위치입니다.

    내가 읽기의 숫자가 더 중요하다고 생각하기 때문에 실제로 나는 조건부 집계를 사용합니다. 테이블은 버퍼 풀에 맞게 및 숙박 작은 경우, 어떤 쿼리는 최종 사용자를 위해 빠른 것입니다. 테이블이 사용 가능한 메모리보다 큰 경우 그러나, 나는 디스크에서 읽는 것은 상당히 하위 쿼리를 늦출 것이라고 기대합니다.

    반면에, 가능한 한 빨리 행을 필터링하는 것도 중요하다.

    여기를 보여줍니다 테스트의 약간의 변화이다. 여기에 확실 행이 필터 기준을 만족하지 있는지 확인하기 위해, GETDATE ()는 + 100 년으로 임계 값을 설정합니다.

    캐시 따뜻한 (CPU = 344)

    SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

    부질 (CPU = 500)

    SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,100,GETDATE())
) last_year_cnt
OPTION (RECOMPILE);

    원래 조건 집계 (CPU = 937)

    SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,100,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

    변수 조건부 응집 (CPU = 750)

    DECLARE @VarYear100 datetime = DATEADD(year,100,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

    변수 및 COUNT 대신 SUM 조건부 응집 (CPU = 750)

    SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

    아래는 서브 쿼리와 계획입니다. 당신은 그들이 표 검사 단계에서 필터링 된 모든의, 0 행이 두 번째 하위 쿼리에서 스트림 집계에 들어간 것을 볼 수 있습니다.

    그 결과, 하위 쿼리를 다시 빠릅니다.

    여기에 내가 이전 시험의 필터링 기준 변경 : 모든> <로 대체되었다. 그 결과, 조건 COUNT 대신 없음의 모든 행을 계산. 놀랍게도! 서브 쿼리는 813 대신 500의 동안 조건부 집계 쿼리는 같은 750 밀리했다.

    여기에 하위 쿼리에 대한 계획은 다음과 같습니다

    여기있어. 하위 쿼리 방법의 성능 데이터 분포에 따라 달라집니다. 조건부 집계의 성능 데이터 분포에 의존하지 않습니다.

    부질 방법은 데이터 분포에 따라 조건부 응집보다 빠르거나 느릴 수있다.

    이 알면 당신이 선택하는 어떤 방법을 결정할 수 있습니다.

    당신은 테이블 스캔 운영자 위에 마우스를 가져 가면 서로 다른 변종의 실제 데이터 크기를 볼 수 있습니다.

    이제 성능 차이가 예상 계획에 흐르는 데이터의 양의 차이에 의한 것이 분명해진다.

    간단한 경우 COUNT (*)에는 출력리스트는 없다 (럼 값이 필요하지 않다), 데이터 크기 (43메가바이트) 작다.

    조건부 응집의 경우,이 양이 테스트 2와 3 사이에서 변경되지 않으며, 항상 72메가바이트이다. 출력 목록은 하나의 열 datesent 있습니다.

    서브 쿼리의 경우,이 금액은 데이터 분포에 따라 변경한다.

  2. ==============================

    2.여기에 큰 테이블에 서브 쿼리가 (약 40-50sec) 매우 느렸다 내 예제가 나는 초에 가속화 FILTER (조건부 집계)로 쿼리를 다시 작성하는 조언을 받았다. 나는 놀랐다.

    여기에 큰 테이블에 서브 쿼리가 (약 40-50sec) 매우 느렸다 내 예제가 나는 초에 가속화 FILTER (조건부 집계)로 쿼리를 다시 작성하는 조언을 받았다. 나는 놀랐다.

    지금 당신은 단지 한 번 큰 테이블에 조인 때문에 나는 항상 FILTER 조건부 집계를 사용하고, 모든 검색 필터로 이루어집니다. 그것은 나쁜 생각이 큰 테이블에 하위 선택합니다.

    스레드 : 표로 보고서에 대한 포스트 그레스에서 내부 선택과 SQL 성능 문제

    다음과 같이 내가하는 표로 보고서를 필요

    예 (쉬운 플랫 물건 먼저 다음 복잡한 표로 물건) :

    RecallID | RecallDate | Event |..| WalkAlone | WalkWithPartner |..| ExerciseAtGym
256      | 10-01-19   | Exrcs |..| NULL      | NULL            |..| yes
256      | 10-01-19   | Walk  |..| yes       | NULL            |..| NULL
256      | 10-01-19   | Eat   |..| NULL      | NULL            |..| NULL
257      | 10-01-19   | Exrcs |..| NULL      | NULL            |..| yes

    내 SQL은 표에 답 기반 열에 대한 내부 선택을했다,이 모습 :

    select 
-- Easy flat stuff first
r.id as recallid, r.recall_date as recalldate, ... ,

-- Example of Tabulated Columns:
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l 
where l.id=aq.answer_choice_id and aq.question_id=13 
and aq.id=ans.activity_question_id and aq.activity_id=27 and ans.event_id=e.id) 
     as transportationotherintensity,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=66 and l.id=aq.answer_choice_id and aq.question_id=14
and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as commutework,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=67 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as commuteschool,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=95 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id) 
     as dropoffpickup,

    성능은 끔찍했다. 고든 리노 프는 한 번 모든 표로 선택에 적절한 필터와 큰 테이블 ANSWERS_T에 가입 권장합니다. 즉 1 초에 최대주었습니다.

    select ans.event_id,
       max(l.description) filter (where aq.question_id = 13 and aq.activity_id = 27) as transportationotherintensity
       max(l.description) filter (where l.id = 66 and aq.question_id = 14 and aq.activity_id = 67) as commutework,
       . . .
from activity_questions_t aq join
     lookup_t l 
     on l.id = aq.answer_choice_id join
     answers_t ans
     on aq.id = ans.activity_question_id
group by ans.event_id

    3. from https://stackoverflow.com/questions/45795898/conditional-aggregation-performance by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 생성하고 SQL 서버 쿼리 연결된 데이터베이스 서버합니까?  (0) 2020.06.08
[SQL] 자료 10 변환을 사용하여 SQL 만에 자료 (36)  (0) 2020.06.08
[SQL] CASE 조건 및 SUM과 SELECT 쿼리 ()  (0) 2020.06.08
[SQL] LIKE 만 전체 단어 매치?  (0) 2020.06.08
[SQL] 비 "독특한"행을 선택하는 방법  (0) 2020.06.08
,

티스토리툴바