语句A:
SELECT RIGHT(BB.ServerID,1) AS BssID,BB.BtsId
,COUNT(*) AS CNT
FROM tempdb.dbo.GetMaxTimeBtsDoErl AS AA,
(SELECT BeginTime,SERVERID,BTSID
,SUM(FwdNinthLevel+FwdTenthLevel) AS FwdEighthToTenth
,COUNT(*) AS E1Num
FROM WY_CAS_BSS3G_DB.DBO.H_P_IPE1Flux
WHERE BeginTime>='2009-11-12' AND BeginTime<'2009-11-19' --BeginTime>=@Date-6 AND BeginTime<@Date+1--
GROUP BY BeginTime,ServerID,BtsID ) AS BB
WHERE AA.BeginTime=BB.BeginTime AND AA.BSSID=RIGHT(BB.SERVERID,1) AND AA.BTSID=BB.BTSID
AND FwdEighthToTenth>720*E1Num --平均每条前向占用带宽在80-100%的时间(s)超过720s,即20%
GROUP BY RIGHT(BB.ServerID,1),BB.BtsID语句B:
UPDATE A SET A.[COUNT]=B.CNT
FROM tempdb.dbo.ExpansionPlanBtsE1 AS A,
(SELECT RIGHT(BB.ServerID,1) AS BssID,BB.BtsId
,COUNT(*) AS CNT
FROM tempdb.dbo.GetMaxTimeBtsDoErl AS AA,
(SELECT BeginTime,SERVERID,BTSID
,SUM(FwdNinthLevel+FwdTenthLevel) AS FwdEighthToTenth
,COUNT(*) AS E1Num
FROM WY_CAS_BSS3G_DB.DBO.H_P_IPE1Flux
WHERE BeginTime>='2009-11-12' AND BeginTime<'2009-11-19' --BeginTime>=@Date-6 AND BeginTime<@Date+1--
GROUP BY BeginTime,ServerID,BtsID ) AS BB
WHERE AA.BeginTime=BB.BeginTime AND AA.BSSID=RIGHT(BB.SERVERID,1) AND AA.BTSID=BB.BTSID
AND FwdEighthToTenth>720*E1Num --平均每条前向占用带宽在80-100%的时间(s)超过720s,即20%
GROUP BY RIGHT(BB.ServerID,1),BB.BtsID) AS B
WHERE A.BSSID=B.BSSID AND A.BTSID=B.BTSID为什么语句A很快就运行完,而将语句A放入到语句B中就相当慢,如果将A语句得出的结果放入到一张表中,将更新tempdb.dbo.ExpansionPlanBtsE1数据就很快,难道SQL自己不会先运行A语句,再赋值???
请高手指教,非常感谢!
SELECT RIGHT(BB.ServerID,1) AS BssID,BB.BtsId
,COUNT(*) AS CNT
FROM tempdb.dbo.GetMaxTimeBtsDoErl AS AA,
(SELECT BeginTime,SERVERID,BTSID
,SUM(FwdNinthLevel+FwdTenthLevel) AS FwdEighthToTenth
,COUNT(*) AS E1Num
FROM WY_CAS_BSS3G_DB.DBO.H_P_IPE1Flux
WHERE BeginTime>='2009-11-12' AND BeginTime<'2009-11-19' --BeginTime>=@Date-6 AND BeginTime<@Date+1--
GROUP BY BeginTime,ServerID,BtsID ) AS BB
WHERE AA.BeginTime=BB.BeginTime AND AA.BSSID=RIGHT(BB.SERVERID,1) AND AA.BTSID=BB.BTSID
AND FwdEighthToTenth>720*E1Num --平均每条前向占用带宽在80-100%的时间(s)超过720s,即20%
GROUP BY RIGHT(BB.ServerID,1),BB.BtsID语句B:
UPDATE A SET A.[COUNT]=B.CNT
FROM tempdb.dbo.ExpansionPlanBtsE1 AS A,
(SELECT RIGHT(BB.ServerID,1) AS BssID,BB.BtsId
,COUNT(*) AS CNT
FROM tempdb.dbo.GetMaxTimeBtsDoErl AS AA,
(SELECT BeginTime,SERVERID,BTSID
,SUM(FwdNinthLevel+FwdTenthLevel) AS FwdEighthToTenth
,COUNT(*) AS E1Num
FROM WY_CAS_BSS3G_DB.DBO.H_P_IPE1Flux
WHERE BeginTime>='2009-11-12' AND BeginTime<'2009-11-19' --BeginTime>=@Date-6 AND BeginTime<@Date+1--
GROUP BY BeginTime,ServerID,BtsID ) AS BB
WHERE AA.BeginTime=BB.BeginTime AND AA.BSSID=RIGHT(BB.SERVERID,1) AND AA.BTSID=BB.BTSID
AND FwdEighthToTenth>720*E1Num --平均每条前向占用带宽在80-100%的时间(s)超过720s,即20%
GROUP BY RIGHT(BB.ServerID,1),BB.BtsID) AS B
WHERE A.BSSID=B.BSSID AND A.BTSID=B.BTSID为什么语句A很快就运行完,而将语句A放入到语句B中就相当慢,如果将A语句得出的结果放入到一张表中,将更新tempdb.dbo.ExpansionPlanBtsE1数据就很快,难道SQL自己不会先运行A语句,再赋值???
请高手指教,非常感谢!
用临时表相对还会慢点。。我经常在UPDATE语句中嵌套子查询的。
不知道能不能一个sql 搞定。等高手。。