COUNT(1) + COUNT(DISTINCT()) намного медленнее, чем выполнение 2 запросов по отдельности

Объяснение запроса:

  • Person (обозначается PersonID) может иметь или не иметь соответствующий Job (обозначается JobID).
  • Если есть соответствующий Job, привязка сохраняется в таблице PersonJob (PersonID ‹=> JobID).
  • Person без Job игнорируются.
  • Job также имеет CityID.
  • Для каждого Job.CityID запрос хочет знать общее количество Person, а также количество уникальных Person.HouseID

Запрос:

SELECT
  Job.CityID, COUNT(1) NumTotal, COUNT(DISTINCT(Person.HouseID)) NumDistinct
FROM
  Job
  INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
  INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
GROUP BY
  Job.CityID

Статистика:

  • SELECT COUNT(1) FROM PersonJob ~ 600.000
  • SELECT COUNT(1) FROM Person ~ 800.000
  • SELECT COUNT(DISTINCT(Person.HouseID)) FROM Person ~ 10.000
  • SELECT COUNT(1) FROM Job ~ 500
  • MS SQL Server 10.50

Проблема:

  • COUNT(1) часть запроса при отдельном запуске выполняется за 0,25 с.

    SELECT
  Job.CityID, COUNT(1) NumTotal
FROM
  Job
  INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
  INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
GROUP BY
  Job.CityID

  • COUNT(DISTINCT(Person.HouseID)) часть запроса при отдельном запуске выполняется за 0,80 сек.

    SELECT
  Job.CityID, COUNT(DISTINCT(Person.HouseID)) NumDistinct
FROM
  Job
  INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
  INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
GROUP BY
  Job.CityID

  • Весь запрос выполняется за 3,10 сек — в 3 раза медленнее, почему?

Планы выполнения:

  • Я не специалист в их чтении, извините.
  • Насколько я могу судить, проблема заключается в COUNT(DISTINCT)
  • In partial query:
    • 25% Hash match (Aggregate) (output Job.CityID)
    • 15% Hash match (Inner Join) (output Job.CityID, Person.HouseID)
      • 30% Index scan (output Person.PersonID, Person.HouseID)
      • 14% поиск по индексу (выход PersonJob.PersonID)
  • In full query:
    • 03% Hash match (Partial aggregate) (output Job.CityID, COUNT(*))
    • Совпадение хэша 31% (совокупность) (выход Job.CityID)
    • 29% Таблица Spool (выход Job.CityID, Person.HouseID)

sql sql-server sqlperformance
person Codeguard    schedule 19.10.2016    source источник
comment
В ваших сравнениях используются join?   -  person Gordon Linoff    schedule 19.10.2016
comment
Не уверен, что вы имеете в виду, возможно, частичные запросы? Добавил их.   -  person Codeguard    schedule 19.10.2016
comment
Не связано, но: distinct НЕ является функцией. distinct (Person.HouseID) точно то же самое, что и distinct Person.HouseID   -  person a_horse_with_no_name    schedule 19.10.2016
comment
Сравните фактические планы выполнения ваших трех запросов, и вы должны увидеть, в чем разница.   -  person Vladimir Baranov    schedule 19.10.2016
comment
добавлены детали плана выполнения   -  person Codeguard    schedule 19.10.2016
comment
Отдельное агрегирование считается вредным   -  person Martin Smith    schedule 19.10.2016
comment
Эта статья выглядит очень многообещающе! Вы должны были опубликовать это как ответ   -  person Codeguard    schedule 19.10.2016

Ответы (2)


arrow_upward
4
arrow_downward

Это известная проблема в версиях SQL Server до 2012 года.

Вы можете попробовать переписать этот код на основе здесь.

WITH T1
     AS (SELECT Job.CityID,
                Person.HouseID
         FROM   Job
                INNER JOIN PersonJob
                        ON ( PersonJob.JobID = Job.JobID )
                INNER JOIN Person
                        ON ( Person.PersonID = PersonJob.PersonID )),
     PartialSums
     AS (SELECT COUNT(*) AS CountStarPartialCount,
                HouseID,
                CityID
         FROM   T1
         GROUP  BY CityID,
                   HouseID)
SELECT CityID,
       SUM(CountStarPartialCount) AS NumTotal,
       COUNT(HouseID)             AS NumDistinct
FROM   PartialSums
GROUP  BY CityID

SQL Server 2012 имеет некоторые улучшения в этой области. См. Считается ли агрегация отдельных элементов вредной?

person Martin Smith    schedule 19.10.2016
comment
Никогда не задумывался об этой особенности оптимизатора. Я узнал что-то новое сегодня. Спасибо. - person Vladimir Baranov; 19.10.2016
comment
Спасибо! По крайней мере, я знаю, что это проблема с MS SQL. Я опубликовал свое окончательное решение как отдельный ответ. - person Codeguard; 19.10.2016

arrow_upward
1
arrow_downward

После прочтения обходного пути, предоставленного Мартином Смитом, я решил, что обходной путь слишком сложен для чтения и понимания и станет полным беспорядком, если возникнет необходимость в дополнительном столбце DISTINCT. Я решил оставить частичные запросы LEFT JOIN следующим образом:

SELECT
  Job.CityID, NumTotal.Value, NumDistinct.Value
FROM
  Job
  LEFT JOIN
  (
    SELECT
      Job.CityID, COUNT(1) AS Value
    FROM
      Job
      INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
      INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
    GROUP BY
      Job.CityID
  ) NumTotal ON (NumTotal.CityID = Job.CityID)
  LEFT JOIN
  (
    SELECT
      Job.CityID, COUNT(DISTINCT Person.HouseID) AS Value
    FROM
      Job
      INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
      INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
    GROUP BY
      Job.CityID
  ) NumDistinct ON (NumDistinct.CityID = Job.CityID)
GROUP BY
  Job.CityID

Это выполняется за 0,70 секунды, а "обходной" sql - за 0,60 секунды. Это означает, что LEFT JOIN'inig в 5 раз быстрее, чем «исходный полный запрос», и только на 20% медленнее, чем «обходной путь», при этом его намного легче читать и расширять.

person Codeguard    schedule 19.10.2016