Блог

Главная - Вопросы по программированию - Несколько ПОДСЧЕТОВ для одного SQL-запроса: в поисках эффективности

Несколько ПОДСЧЕТОВ для одного SQL-запроса: в поисках эффективности

#sql #sql-server #count #sum #multiple-conditions

#sql #sql-server #подсчет #сумма #несколько условий

Вопрос:

Мне просто хотелось бы узнать ваше мнение об эффективности запроса. Я подсчитываю значения в очень большой таблице (несколько миллионов записей). Это MSSQL, но я думаю, что он должен применяться к любому движку базы данных. Сейчас я делаю следующее:

 SELECT Task,
    COUNT(*) as Total,
    SUM(CASE WHEN Status = 'Active' THEN 1 ELSE 0 END) AS Active,
    SUM(CASE WHEN Status = 'Active' AND AppType = 'MOBILE' THEN 1 ELSE 0 END) AS ActiveMobile,
    SUM(CASE WHEN Status = 'Active' AND AppType = 'WEB' THEN 1 ELSE 0 END) AS ActiveWeb,
    SUM(CASE WHEN Status = 'OnHold' THEN 1 ELSE 0 END) AS onHold,
    SUM(CASE WHEN Status = 'onHold' AND AppType = 'MOBILE' THEN 1 ELSE 0 END) AS onHoldMobile,
    SUM(CASE WHEN Status = 'onHold' AND AppType = 'WEB' THEN 1 ELSE 0 END) AS onHoldWeb,
    SUM(CASE WHEN Status = 'Active' OR Status = 'onHold' THEN 1 ELSE 0 END) AS ActiveAndOnHold,
    SUM(CASE WHEN (Status = 'Active' OR Status = 'onHold') AND AppType = 'MOBILE' THEN 1 ELSE 0 END) AS ActiveAndOnHoldMobile,
    SUM(CASE WHEN (Status = 'Active' OR Status = 'onHold') AND AppType = 'WEB' THEN 1 ELSE 0 END) AS ActiveAndOnHoldWeb
FROM events
GROUP BY Task;

Я понимаю, что снова и снова считаю одно и то же, и я должен иметь возможность добавлять частичные результаты, но, честно говоря, я не мог понять, как это сделать, не обходя таблицу более одного раза.

Фактический запрос содержит еще около 20 сумм с комбинациями тех же данных. Выполнение запроса занимает некоторое время (около двух часов). Мне интересно, есть ли лучший способ сделать это.

Любое предложение приветствуется.

Спасибо

Комментарии:

1. Помимо оптимизации запросов вы можете посмотреть, осталось ли у вашего экземпляра SQL Server немного ресурсов, и поиграть с его максимальной степенью параллелизма (он же maxdop ). Если оно отличается от 0, то вы можете добавить option (maxdop x) после вашего запроса x максимальное количество потоков для каждого оператора в вашем плане запроса ( 0 = неограниченно). Следите за загрузкой процессора и ввода-вывода, если вы собираетесь запускать это где-нибудь рядом с производством!

2. 2 часа!!! Насколько велики данные? В этом случае я бы ожидал, что стоимость в основном будет связана с перемещением данных для group by , а не с количеством sum() s.

3. Спасибо @Sander, я проверю это.

4. @Gordon Linoff. Хотел бы я знать, почему это занимает так много времени.

5. CASE WHEN Status = 'Active' AND Status = 'onHold' , если это не опечатка, всегда будет false: 0 ,,, вы можете добавить sum(active) sum(onhold), уже вычисленные в запросе (Active, OnHold), чтобы получить сумму activeamp; onhold .. то же самое касается active (mobile) onhold (mobile) и т. Д….

Ответ №1:

Вы можете сохранить промежуточные флаги. Я сомневаюсь, что это ускорит ваш запрос, но упростит его обслуживание:

 SELECT Task, COUNT(*) as Total,
        SUM(is_active) AS Active,
        SUM(is_active * is_mobile) AS ActiveMobile,
        . . .
FROM events e CROSS APPLY
     (VALUES (CASE WHEN Status = 'Active' THEN 1 ELSE 0 END),
             (CASE WHEN Status = 'OnHold' THEN 1 ELSE 0 END),
             (CASE WHEN AppType = 'WEB' THEN 1 ELSE 0 END),
             (CASE WHEN AppType = 'MOBILE' THEN 1 ELSE 0 END),
             . . .
     ) v(is_active, is_onhold, is_web, is_mobile)
GROUP BY Task;

Это может повлиять на производительность, если ваши сравнения на самом деле более громоздкие, чем простое равенство строк.

Комментарии:

1. Спасибо @Gordon Linoff, вы правы, это чище. Спасибо за идею. Я проверю это.

Ответ №2:

Убедитесь, что ваш запрос имеет полностью покрывающий индекс. Это может иметь огромное значение. Убедитесь, что ваш план запроса использует несколько ядер. Если вы посмотрите на план запроса, найдите оператор сбора. Это связано с предложением maxdop выше. Рассмотрите возможность использования подробного подробного подзапроса, который принимает количество строк. Затем другой запрос, который разбивает ваши поля на конечные результаты.

 select F1, sum(case when A = 1 then ItemCount else 0 end) SummaryField
from 
(
  select F1, A, count(*) ItemCount
  from T1
  group by F1, A
) T2
group by F1

Если все остальное не удается, рассмотрите возможность кэширования совокупных результатов в таблице, которая содержит только набор результатов из вашего совокупного запроса. Исходный агрегированный запрос может выполняться каждые два часа. Но запрос к таблице кэша будет содержать меньше записей, и запрос будет намного проще.