한 쌍의 열 (둘 다 int) 에 조인되는 3 개의 “큰”테이블이 있습니다.

• Table1에는 ~ 2 억 개의 행이 있습니다.
• Table2 ~ 150 만 행
• Table3에는 ~ 600 만 개의 행이 있습니다.

각 테이블에는 클러스터 인덱스가 Key1, Key2다음, 하나 더 열을. Key1카디널리티가 낮고 매우 비뚤어집니다. 항상 WHERE조항 에서 참조됩니다 . 절 Key2에서 언급되지 않았습니다 WHERE. 각 조인은 다 대다입니다.

문제는 카디널리티 추정에 있습니다. 각 조인의 출력 추정치가 더 커지는 대신 작아 집니다 . 결과는 실제 결과가 수백만에 달할 때 최종 수백 명이 줄어 듭니다.

CE가 더 나은 견적을 낼 수있는 방법이 있습니까?

SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

내가 시도한 솔루션 :

• 에 다중 열 통계 작성 Key1,Key2
• 만들기 톤 에 필터링 된 통계를 Key1(이 꽤 도움이되지만 나는 데이터베이스에 사용자가 만든 통계의 수천 끝.)

마스킹 된 실행 계획 (나쁜 마스킹에 대해 죄송합니다)

내가보고있는 경우 결과에는 9 백만 개의 행이 있습니다. 새로운 CE는 180 개의 행을 추정합니다. 레거시 CE는 6100 개의 행을 추정합니다.

재현 가능한 예는 다음과 같습니다.

DROP TABLE IF EXISTS #Table1, #Table2, #Table3;
CREATE TABLE #Table1 (Key1 INT NOT NULL, Key2 INT NOT NULL, T1Key3 INT NOT NULL, CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED (Key1, Key2, T1Key3));
CREATE TABLE #Table2 (Key1 INT NOT NULL, Key2 INT NOT NULL, T2Key3 INT NOT NULL, CONSTRAINT pk_t2 PRIMARY KEY CLUSTERED (Key1, Key2, T2Key3));
CREATE TABLE #Table3 (Key1 INT NOT NULL, Key2 INT NOT NULL, T3Key3 INT NOT NULL, CONSTRAINT pk_t3 PRIMARY KEY CLUSTERED (Key1, Key2, T3Key3));

-- Table1 
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2),
     DataSize (Key1, NumberOfRows)
     AS (SELECT 1, 2000 UNION
         SELECT 2, 10000 UNION
         SELECT 3, 25000 UNION
         SELECT 4, 50000 UNION
         SELECT 5, 200000)
INSERT INTO #Table1
SELECT Key1
     , Key2 = ROW_NUMBER() OVER (PARTITION BY Key1, T1Key3 ORDER BY Number)
     , T1Key3
FROM DataSize
     CROSS APPLY (SELECT TOP(NumberOfRows)
                         Number
                       , T1Key3 = Number%(Key1*Key1) + 1
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smaller number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table2
SELECT DISTINCT
       Key1
     , Key2
     , T2Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1*10)
                         T2Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smallest number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table3
SELECT DISTINCT
       Key1
     , Key2
     , T3Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1)
                         T3Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;


DROP TABLE IF EXISTS #a;
SELECT col = 1
INTO #a
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
WHERE t1.Key1 = 1;

DROP TABLE IF EXISTS #b;
SELECT col = 1
INTO #b
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN #Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

답변

-- Note: Add "WITH FULLSCAN" to each if you want a perfect 20,000 row estimate
CREATE STATISTICS st_#Table1 ON #Table1 (Key2) WHERE Key1 = 1
CREATE STATISTICS st_#Table2 ON #Table2 (Key2) WHERE Key1 = 1
CREATE STATISTICS st_#Table3 ON #Table3 (Key2) WHERE Key1 = 1

DROP TABLE IF EXISTS #Table1_extract, #Table2_extract, #Table3_extract, #c
-- Extract only the subset of rows that match the filter predicate
-- (Or better yet, extract only the subset of columns you need!)
SELECT * INTO #Table1_extract FROM #Table1 WHERE Key1 = 1
SELECT * INTO #Table2_extract FROM #Table2 WHERE Key1 = 1
SELECT * INTO #Table3_extract FROM #Table3 WHERE Key1 = 1
-- Now perform the join on those extracts, removing the filter predicate
SELECT col = 1
INTO #c
FROM #Table1_extract t1
JOIN #Table2_extract t2
    ON t1.Key2 = t2.Key2
JOIN #Table3_extract t3
    ON t1.Key2 = t3.Key2

SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key2 = t2.Key2
      AND t1.Key1 = 1
      AND t2.Key1 = 1
     JOIN Table3 t3
       ON t2.Key2 = t3.Key2
      AND t3.Key1 = 1;