有时候子查询比左连接查询速度快

最近几天在优化数据库，有些数据表因为建立时候不合理导致查询的时候速度比较慢，比如三个表，三个表中数据最少的都是十万条，这些表在左联或者右联的时候速度可能需要几秒钟，再加上where条件，条件中再加or，这时候速度是非常的慢的，往往需要10秒以上，这时候可以用子查询或者union 或者union all 代替，根据情况而定

 

比如这个语句用子查询速度就比较快

 

原来的语句：

select
K.EmployeeNumber,
K.PositionName,
K.Name,
K.SkillWages,
k.AchievementsWages,
K.TotleIncome as Income,
K.TotleCashIncome as CashIncome,
(ISNULL(M0.Val2,0)+ISNULL(M1.Val2,0))TurnoverMoney,
(ISNULL(N0.Val2,0)+ISNULL(N1.Val2,0)+ISNULL(N2.Val2,0)+ISNULL(N3.Val2,0)+ISNULL(N4.Val2,0))LadderMoney
from (SELECT
A.EmployeeNumber
,A.Name
,B.PositionName
,A.SkillWages
,A.AchievementsWages
,A.ExtText2
,ISNULL((select SUM(Performance) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=0 and CreateTime>='{0}' and CreateTime<'{1}'),0)SerPerformance
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=1 and CreateTime>='{0}' and CreateTime<'{1}'),0)ProPerformance_Cash
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=4 and CreateTime>='{0}' and CreateTime<'{1}'),0)PlanPerformance_Cash
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=2 and CreateTime>='{0}' and CreateTime<'{1}'),0)OpenPerformance_Cash
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=3 and CreateTime>='{0}' and CreateTime<'{1}'),0)RechargePerformance_Cash
,ISNULL((select SUM(Performance) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotlePerformance
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotlePerformance_Cash
,ISNULL((select SUM(ISNULL(Income,0)) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotleIncome
,ISNULL((select SUM(ISNULL(CashIncome,0)) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotleCashIncome
from
[User] A left join t_UserLevel B on A.ExtText2=B.Id where A.OrgCode='{2}' and A.[Status]!=0 and A.DeleFlag=1 and A.OrgType=6 and A.DeleFlag=1 {4}
)K
left join
(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopTurnoverPercentageSet A inner join t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=57 and a.ShopCode='{3}')M0 ON K.ExtText2=M0.RoleCode AND M0.BeginVal<=K.TotlePerformance AND M0.EndVal>=K.TotlePerformance
left join
(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopTurnoverPercentageSet A INNER JOIN t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=55 and a.ShopCode='{3}')M1 ON K.ExtText2=M1.RoleCode AND M1.BeginVal<=K.TotlePerformance_Cash AND M1.EndVal>=K.TotlePerformance_Cash
left join
(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=59 and a.ShopCode='{3}')N0 ON K.ExtText2=N0.RoleCode AND N0.BeginVal<=K.SerPerformance AND N0.EndVal>=K.SerPerformance
left join
(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=60 and a.ShopCode='{3}')N1 ON K.ExtText2=N1.RoleCode AND N1.BeginVal<=K.ProPerformance_Cash AND N1.EndVal>=K.ProPerformance_Cash
left join
(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=61 and a.ShopCode='{3}')N2 ON K.ExtText2=N2.RoleCode AND N2.BeginVal<=K.OpenPerformance_Cash AND N1.EndVal>=K.OpenPerformance_Cash
left join
(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=62 and a.ShopCode='{3}')N3 ON K.ExtText2=N3.RoleCode AND N3.BeginVal<=K.RechargePerformance_Cash AND N3.EndVal>=K.RechargePerformance_Cash
left join
(select A.[Type],A.RoleCode,B.BeginVal,B.EndVal,B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=63 and a.ShopCode='{3}')N4 ON K.ExtText2=N4.RoleCode AND N4.BeginVal<=K.PlanPerformance_Cash AND N4.EndVal>=K.PlanPerformance_Cash ）

 

 

修改过的语句

 

select
K.EmployeeNumber,
K.PositionName,
K.Name,
K.SkillWages,
k.AchievementsWages,
K.TotleIncome as Income,
K.TotleCashIncome as CashIncome,
(ISNULL((select B.Val2 from t_ShopTurnoverPercentageSet A inner join t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=57 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.TotlePerformance AND B.EndVal>=K.TotlePerformance),0)
+
ISNULL((select B.Val2 from t_ShopTurnoverPercentageSet A inner join t_ShopTurPerDetailSet B ON A.ID=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=55 and a.ShopCode='{3}'and K.ExtText2=A.RoleCode AND B.BeginVal<=K.TotlePerformance_Cash AND B.EndVal>=K.TotlePerformance_Cash),0)
)TurnoverMoney,
(ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=59 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.SerPerformance AND B.EndVal>=K.SerPerformance),0)
+
ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=60 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.ProPerformance_Cash AND B.EndVal>=K.ProPerformance_Cash),0)
+
ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=61 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.OpenPerformance_Cash AND B.EndVal>=K.OpenPerformance_Cash),0)
+
ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=62 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.RechargePerformance_Cash AND B.EndVal>=K.RechargePerformance_Cash),0)
+
ISNULL((select B.Val2 from t_ShopLadderPercentageSet A INNER JOIN t_ShopLaPerDetailSet B ON A.Id=B.SetCode WHERE A.DeleFlag=1 AND B.DeleFlag=1 AND A.[Type]=63 and a.ShopCode='{3}' AND K.ExtText2=A.RoleCode AND B.BeginVal<=K.PlanPerformance_Cash AND B.EndVal>=K.PlanPerformance_Cash),0))LadderMoney
from (SELECT
A.EmployeeNumber
,A.Name
,B.PositionName
,A.SkillWages
,A.AchievementsWages
,A.ExtText2
,ISNULL((select SUM(Performance) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=0 and CreateTime>='{0}' and CreateTime<'{1}'),0)SerPerformance
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=1 and CreateTime>='{0}' and CreateTime<'{1}'),0)ProPerformance_Cash
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=4 and CreateTime>='{0}' and CreateTime<'{1}'),0)PlanPerformance_Cash
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=2 and CreateTime>='{0}' and CreateTime<'{1}'),0)OpenPerformance_Cash
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and SaleType=3 and CreateTime>='{0}' and CreateTime<'{1}'),0)RechargePerformance_Cash
,ISNULL((select SUM(Performance) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotlePerformance
,ISNULL((select SUM(Performance_Cash) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotlePerformance_Cash
,ISNULL((select SUM(ISNULL(Income,0)) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotleIncome
,ISNULL((select SUM(ISNULL(CashIncome,0)) from t_ShopSerWaiter where [Status]=1 and DeleFlag=1 and UserCode=A.UserCode and CreateTime>='{0}' and CreateTime<'{1}'),0)TotleCashIncome
from
(SELECT EmployeeNumber,ExtText2,Name,SkillWages,AchievementsWages,UserCode FROM dbo.[User] where OrgCode='{2}' and [Status]!=0 and DeleFlag=1 and OrgType=6 and DeleFlag=1 {4}) A left join t_UserLevel B on A.ExtText2=B.Id
)K