Улучшение производительности рекурсивного CTE для расчета итогов

У меня есть база данных SQL Server с таблицей Employee, содержащей следующую структуру:

CREATE TABLE Employee 
(
    EmployeeID INT PRIMARY KEY,
    ManagerID INT,
    EmployeeName NVARCHAR(50),
    Salary DECIMAL(18, 2)
);

Я использую рекурсивное выражение Common Table Expression (CTE) для расчета общей заработной платы каждого сотрудника и их подчиненных. Однако при большом количестве сотрудников выполнение запросов становится вялым.

WITH RecursiveSalaryCTE AS 
(
    SELECT 
        EmployeeID, ManagerID, EmployeeName, Salary
    FROM 
        Employee
    WHERE 
        ManagerID IS NULL

    UNION ALL

    SELECT 
        e.EmployeeID, e.ManagerID, e.EmployeeName, e.Salary
    FROM 
        Employee e
    JOIN 
        RecursiveSalaryCTE r ON e.ManagerID = r.EmployeeID
)
SELECT 
    EmployeeID, EmployeeName, Salary,
    (SELECT SUM(Salary) FROM RecursiveSalaryCTE 
     WHERE ManagerID = e.EmployeeID) AS TotalSubordinateSalaries
FROM 
    RecursiveSalaryCTE e;

Есть ли лучший способ оптимизировать этот запрос? Будем очень признательны за любые альтернативные подходы или модификации запроса, которые могут обеспечить более быстрые результаты.

Пример данных для таблицы Employee:

INSERT INTO Employee (EmployeeID, ManagerID, EmployeeName, Salary)
VALUES (1, NULL, 'John', 10000.00),
       (2, 1, 'Alice', 7500.00),
       (3, 1, 'Bob', 8000.00),
       -- ... more data ...
       (1000, 999, 'Eve', 6000.00);

Пожалуйста, обновите свой вопрос, указав вывод EXPLAIN PLAN для вашего запроса.

NickW 10.08.2023 19:46

Проблемы с производительностью SQL часто лучше всего анализировать, начиная с фактического плана выполнения. См. вставьте план, чтобы узнать, как включить план выполнения в свой вопрос. Есть ли индексы в таблице?

HABO 10.08.2023 20:26

Этот вопрос требует подробной информации о ваших индексах/ключах/ограничениях в дополнение к схеме таблицы и фактическому плану выполнения (вставьте план)

Stu 10.08.2023 21:18
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
1
3
50
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Как насчет этого... https://dbfiddle.uk/NwF09y4y

--manager list
with managers as (
  select distinct ManagerID
  from Employee
)
--sum sub salaries  
, TotalSubordinateSalaries as (
  select ManagerID, sum(Salary) TotalSubordinateSalaries
  from Employee
  group by ManagerID
)
select EmployeeID, EmployeeName, Salary ,ts.TotalSubordinateSalaries
from Employee e
--identify the manager
left join managers m on m.ManagerID=e.EmployeeID
--join their subs
left join TotalSubordinateSalaries ts on ts.ManagerID = m.ManagerID

Вам нужно объяснить, что вы сделали для возможного повышения производительности, иначе это не поможет OP разработать лучшие запросы.

Dale K 11.08.2023 01:52
Ответ принят как подходящий

Избегайте использования подзапроса как части окончательного SELECT, это должно улучшить производительность, и если вы действительно хотите получить общее количество людей, зависящих от менеджера на любом уровне глубины, вы должны сгруппировать по общему выражению пути (таким образом, вы должны собрать путь в рекурсивный CTE) ..., группировка по managerID дает вам общее количество только прямых подчиненных.

with RecursiveSalaryCTE(EmployeeID, managerid, salary, path) as (
    SELECT EmployeeID, managerid, Salary, cast(concat('/', EmployeeID) as varchar(1000))
    FROM employee
    WHERE managerid IS NULL
    
    UNION ALL
    
    SELECT e.EmployeeID, e.managerid, e.salary, cast(concat(r.path, '/', e.EmployeeID) as varchar(1000))
    FROM employee e
    JOIN RecursiveSalaryCTE r ON e.managerid = r.EmployeeID

)
select EmployeeID, managerid, salary, Total_all_sub, 
    sum(salary) over(partition by managerid) as total_same_manager
from (
    select r.EmployeeID, r.managerid, r.salary,
        SUM(r1.Salary) Total_all_sub
    from RecursiveSalaryCTE r
    join RecursiveSalaryCTE r1 on substring(r1.path,1,len(r.path)) = r.path 
    group by r.EmployeeID, r.managerid, r.salary
) d
order by employeeid
;

https://dbfiddle.uk/O1pMAy3p

Вам нужно объяснить, что вы сделали для возможного повышения производительности, иначе это не поможет OP разработать лучшие запросы.

Dale K 11.08.2023 01:52

Другие вопросы по теме