sql - 提高 CTE 的性能
问题描述
我需要一份报告,计算不同表格中的一些值。
我正在使用 CTE 和子查询进行计算,但查询速度很慢。
我尝试了一些尝试来提高性能,但没有成功。
实际查询有更多列,但我复制了其中的一部分以了解我的查询结构。
感谢帮助。
begin
with CTE_FirmList
(
FirmId,
FirmName,
StartDate,
EndDate,
TgId,
TgName,
TgCity,
ProjectName
)
as
(
select
distinct f.Id FirmId,
f.Name FirmName,
f.StartDate,
f.EndDate,
t.Id TgId,
t.Name TgName,
c.Name TgCity,
p.Name ProjectName
from Firm f
left join Tg t on t.Id = f.TgId
left join City c on c.Id=t.CityId
left join Country co on co.Id=f.CountryId
left join FirmProject p on p.FirmId=f.Id
)
select
FirmId,
FirmName,
(select COUNT(0)
from FirmPersonnel p
inner join CTE_FirmList f on f.FirmId=p.FirmId
where f.FirmId=qq.FirmId
) TotalPersonnelCount,
(select COUNT(0)
from FirmPersonnel p
inner join CTE_FirmList f on f.FirmId=p.FirmId
where f.FirmId=qq.FirmId and p.PersonnelType in (1)
) PersonnelCount1,
(select COUNT(0)
from FirmPersonnel p
inner join CTE_FirmList f on f.FirmId=p.FirmId
where f.FirmId=qq.FirmId and p.PersonnelType in (2)
) PersonnelCount2,
(select count(p.Id)
from FirmProject p
where p.FirmId=qq.FirmId
) TotalProjectCount,
(select count(p.Id)
from FirmProject p
where p.FirmId=qq.FirmId
and p.ProjeStatus in (1)
) ProjectCount1,
(select count(p.Id)
from FirmProject p
where p.FirmId=qq.FirmId
and p.ProjeStatus in (2)
) ProjectCount2
from CTE_FirmList qq
where ProjectName like '%search condition%'
order by TgCity,TgName,FirmName
end
这是示例数据和所需的输出:
解决方案
您绝对可以简化查询。这有点难以理解,但是像这样的东西应该有更好的性能:
select f.FirmId, f.FirmName,
p.TotalPersonnelCount, p.PersonnelCount1, p.PersonnelCount2
count(distinct ProjectId) as TotalProjectCount,
count(distinct case when ProjectStatus = 1 then ProjectId end) as ProjecctCount1,
count(distinct case when ProjectStatus = 2 then ProjectId end) as ProjecctCount2
from (select f.Id as FirmId, f.Name as FirmName, f.StartDate, f.EndDate,
t.Id TgId, t.Name as TgName, c.Name as TgCity,
p.Name as ProjectName, p.ProjectId, p.status
from Firm f left join
Tg t
on t.Id = f.TgId join
City c
on c.Id = t.CityId left join
Country co
on co.Id = f.CountryId left join
FirmProject p
on p.FirmId = f.Id
where p.name like '%search condition%'
) fp join
(select p.FirmId, count(*) as TotalPersonnelCount,
sum(case when PersonnelType in (1) then 1 else 0 end) as PersonnelCount1,
sum(case when PersonnelType in (2) then 1 else 0 end) as PersonnelCount2
from FirmPersonnel p
group by fp.FirmId
) p
on fp.FirmId = p.FirmId
group by FirmId, FirmName
order by FirmName
推荐阅读
- mysql - MYSQL 中的多个条目中选择一个单词
- javascript - 如何创建一个多重过滤功能来过滤掉多个属性?
- java - 为什么一些垃圾收集和 OOP 编程语言没有析构函数?
- reactjs - 允许任何 html 属性的打字稿类型
- linux - 在探测 MDIO 总线之前初始化第二个以太网 PHY 的时钟
- c++ - 如何使用带有 C++ 扩展名的 VSCode 中显示的参数获取调用 g++ 的命令?
- linux - 如何在 Heroku 测功机上安装 htop?
- javascript - 使用拖放时 appendChild 不起作用
- c# - Http-Server,监控路径/文件夹
- automation - 当一个应用程序想要使用 MacOS 10.14 中的 AppleEvent 与其他应用程序通信时,我们的自动化系统面临问题