如何简化/优化此union语句
如何简化/优化此union语句
提问于 2012-10-24 05:40:14
我有这段代码，这是我为客户需要的查询编写的代码。结果给了我我们需要的东西，但是我得到了一些非常长的响应时间。查询的第一部分比第二部分花费更长的时间，而查询本身并没有太大的不同。基本上，它们是两个不同的查询，我使用UNION ALL将它们组合成一个查询。
任何反馈都可以简化这一过程。谢谢
Select * from
(
Select b.Storage_Loc_Nbr as locNbr, b.CurrentLoadCount,b.MMM_Id_Nbr,
(SELECT count(Load_ID) as LoadCount
FROM t_load c
WHERE b.MMM_Id_Nbr=c.MMM_Id_Nbr
and
QC_Status_Code like 'R%' and mmm_Facility_Code ='MC' and Active_Status_Ind='A'
and
b.Storage_Loc_Nbr <> c.Storage_Loc_Nbr)as OtherLocCount
FROM
(
SELECT * FROM
(
SELECT t_load.Storage_Loc_Nbr,
Count(t_Load.Load_Id) AS CurrentLoadCount,
t_load.MMM_Id_Nbr,
t_Storage_Location.Storage_Loc_Type_Code
FROM t_Load LEFT JOIN t_Storage_Location ON t_Load.Storage_Loc_Nbr = t_Storage_Location.Storage_Loc_Nbr
WHERE (
((t_load.MMM_Id_Nbr) between '702004%' and '702011%')
AND ((t_Load.Active_Status_Ind)='A')
AND ((t_Load.QC_Status_Code) Like 'R%')
AND ((t_Load.MMM_Facility_Code)='MC')
AND ((t_Storage_Location.Storage_Loc_Type_Code)='CD')
)
GROUP BY t_load.MMM_Id_Nbr,
t_load.Storage_Loc_Nbr,
t_Storage_Location.Storage_Loc_Type_Code
HAVING
Count(t_Load.Load_Id)<=1
)
as a
)
as b
)
as c
Group By
locNbr,CurrentLoadCount, MMM_Id_Nbr, OtherLocCount
having OtherLocCount>0
UNION ALL
Select * from
(
Select b.Storage_Loc_Nbr as locNbr, b.CurrentLoadCount,b.MMM_Id_Nbr,
(SELECT count(Load_ID) as LoadCount
FROM t_load c
WHERE b.MMM_Id_Nbr=c.MMM_Id_Nbr
and
QC_Status_Code like 'R%' and mmm_Facility_Code ='MC' and Active_Status_Ind='A'
and
b.Storage_Loc_Nbr <> c.Storage_Loc_Nbr)as OtherLocCount
FROM
(
SELECT * FROM
(
SELECT t_load.Storage_Loc_Nbr,
Count(t_Load.Load_Id) AS CurrentLoadCount,
t_load.MMM_Id_Nbr,
t_Storage_Location.Storage_Loc_Type_Code
FROM t_Load LEFT JOIN t_Storage_Location ON t_Load.Storage_Loc_Nbr = t_Storage_Location.Storage_Loc_Nbr
WHERE (
((t_load.MMM_Id_Nbr) between '702004%' and '702011%')
AND ((t_Load.Active_Status_Ind)='A')
AND ((t_Load.QC_Status_Code) Like 'R%')
AND ((t_Load.MMM_Facility_Code)='MC')
AND ((t_Storage_Location.Storage_Loc_Type_Code)<>'CD') AND
((t_load.Storage_Loc_Nbr)<>'clc' AND (t_load.Storage_Loc_Nbr)<>'WHLEAD') AND
(
((t_load.Storage_Loc_Nbr) Like '%A')
OR((t_load.Storage_Loc_Nbr) Like '%B')
OR ((t_load.Storage_Loc_Nbr) Like '%C')
OR ((t_load.Storage_Loc_Nbr) Like '%D')
OR ((t_load.Storage_Loc_Nbr) Like '%E'))
)
GROUP BY t_load.MMM_Id_Nbr,
t_load.Storage_Loc_Nbr,
t_Storage_Location.Storage_Loc_Type_Code
HAVING
Count(t_Load.Load_Id)<=1
)
as a
)
as b
)
as c
Group By
locNbr,CurrentLoadCount, MMM_Id_Nbr, OtherLocCount
having OtherLocCount>0
Order by
LocNbr回答 2
Stack Overflow用户
发布于 2012-10-24 07:50:43
我只看了联盟的第一部分
http://sqlfiddle.com/#!3/72b6d/9/0与原始查询非常相似，只是做了一些细微的简化，优化器可能会自己进行简化。
http://sqlfiddle.com/#!3/72b6d/11/0使用我编写的测试数据生成相同的结果。你可以用更具代表性的东西来扩展它。它使用CTE(复制如下)
-- CTE Version
With T As (
Select
l.Storage_Loc_Nbr,
l.MMM_Id_Nbr,
Count(Load_ID) As LoadCount
From
t_load l
Where
l.QC_Status_Code like 'R%' And
l.mmm_Facility_Code ='MC' And
l.Active_Status_Ind='A' And
l.MMM_Id_Nbr between '702004%' and '702011%'
Group By
l.Storage_Loc_Nbr,
l.MMM_Id_Nbr
)
Select
t1.Storage_Loc_Nbr LocNbr,
t1.LoadCount CurrentLoadCount,
t1.MMM_Id_Nbr,
t2.TotalCount - t1.LoadCount OtherLocCount
From
T t1
Inner Join (
Select
MMM_Id_Nbr,
Sum(LoadCount) TotalCount
From
T
Group By
MMM_Id_Nbr
) t2
On t1.MMM_Id_Nbr = t2.MMM_Id_Nbr
Left Outer Join
t_Storage_Location sl
On t1.Storage_Loc_Nbr = sl.Storage_Loc_Nbr
Where
sl.Storage_Loc_Type_Code = 'CD' and
t1.LoadCount <= 1 And
t2.TotalCount - t1.LoadCount > 0http://sqlfiddle.com/#!3/72b6d/6/0是另一个使用窗口函数的版本，同时也是一个免费的CTE：
-- Window Function Version
With T As (
Select
l.MMM_Id_Nbr,
l.Storage_Loc_Nbr,
Count(Load_ID) Over (Partition By MMM_ID_Nbr, Storage_Loc_Nbr) As LoadCount,
Count(Load_ID) Over (Partition By MMM_ID_Nbr) As TotalCount
From
t_load l
Where
QC_Status_Code like 'R%' And
mmm_Facility_Code ='MC' And
Active_Status_Ind='A' And
l.MMM_Id_Nbr between '702004%' and '702011%'
)
Select
t1.Storage_Loc_Nbr LocNbr,
t1.LoadCount CurrentLoadCount,
t1.MMM_Id_Nbr,
t1.TotalCount - t1.LoadCount OtherLocCount
From
T t1
Left Outer Join
t_Storage_Location sl
On t1.Storage_Loc_Nbr = sl.Storage_Loc_Nbr
Where
sl.Storage_Loc_Type_Code = 'CD' and
t1.LoadCount <= 1 And
t1.TotalCount - t1.LoadCount > 0Stack Overflow用户
发布于 2012-10-24 05:45:14
(SELECT count(Load_ID) as LoadCount
FROM t_load c
WHERE b.MMM_Id_Nbr=c.MMM_Id_Nbr
and
QC_Status_Code like 'R%' and mmm_Facility_Code ='MC' and Active_Status_Ind='A'
and
b.Storage_Loc_Nbr <> c.Storage_Loc_Nbr)as OtherLocCount这是一个相关子查询。这意味着它逐行运行。这是一种你应该避免使用的编程技术。改为将其转换为派生表。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13039735
复制相关文章
相似问题
腾讯云开发者
