У меня есть база данных 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);
Проблемы с производительностью SQL часто лучше всего анализировать, начиная с фактического плана выполнения. См. вставьте план, чтобы узнать, как включить план выполнения в свой вопрос. Есть ли индексы в таблице?
Этот вопрос требует подробной информации о ваших индексах/ключах/ограничениях в дополнение к схеме таблицы и фактическому плану выполнения (вставьте план)
Как насчет этого... 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 разработать лучшие запросы.
Избегайте использования подзапроса как части окончательного 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
;
Вам нужно объяснить, что вы сделали для возможного повышения производительности, иначе это не поможет OP разработать лучшие запросы.
Пожалуйста, обновите свой вопрос, указав вывод EXPLAIN PLAN для вашего запроса.