SQL 查询语句 超时已过期,如何优化????????????
SELECT FinDT, RoadNetID, RoadID, SubRoadID, StationID, CheckStation, InRoadNetID,
InRoadID, InSubRoadID, InStationID,
(SELECT COUNT(*)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight <= LimitLoad AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruCnt0,
(SELECT COUNT(*)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad AND Weight <= LimitLoad * 1.3 AND
A.FinDT = FinDT AND A.StationID = StationID AND
A.RoadNetID = RoadNetID AND RoadID = A.RoadID AND
A.SubRoadID = SubRoadID AND A.CheckStation = CheckStation AND
A.InRoadNetID = InRoadNetID AND A.InSubRoadID = InSubRoadID AND
InStationID = A.InStationID) AS OvTruCnt1,
(SELECT COUNT(*)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad * 1.3 AND
Weight <= LimitLoad * 1.5 AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruCnt2,
(SELECT COUNT(*)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad * 1.5 AND
Weight <= LimitLoad * 2 AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruCnt3,
(SELECT COUNT(*)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad * 2 AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruCnt4,
(SELECT SUM(PayMoney)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight <= LimitLoad AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruMny0,
(SELECT SUM(PayMoney)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad AND Weight <= LimitLoad * 1.3 AND
A.FinDT = FinDT AND A.StationID = StationID AND
A.RoadNetID = RoadNetID AND RoadID = A.RoadID AND
A.SubRoadID = SubRoadID AND A.CheckStation = CheckStation AND
A.InRoadNetID = InRoadNetID AND A.InSubRoadID = InSubRoadID AND
InStationID = A.InStationID) AS OvTruMny1,
(SELECT SUM(PayMoney)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad * 1.3 AND
Weight <= LimitLoad * 1.5 AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruMny2,
(SELECT SUM(PayMoney)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad * 1.5 AND
Weight <= LimitLoad * 2 AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruMny3,
(SELECT SUM(PayMoney)
FROM TC_OutList
WHERE IsTruck = 1 AND Weight > LimitLoad * 2 AND A.FinDT = FinDT AND
A.StationID = StationID AND A.RoadNetID = RoadNetID AND
RoadID = A.RoadID AND A.SubRoadID = SubRoadID AND
A.CheckStation = CheckStation AND A.InRoadNetID = InRoadNetID AND
A.InSubRoadID = InSubRoadID AND InStationID = A.InStationID)
AS OvTruMny4
FROM TC_OutList A
WHERE (StationID = 501) AND (FinDT = '2007-9-21 0:00:00')
GROUP BY FinDT, StationID, RoadNetID, RoadID, SubRoadID, CheckStation, InRoadNetID,
InRoadID, InSubRoadID, InStationID
ORDER BY FinDT, StationID, RoadNetID, RoadID, SubRoadID, CheckStation, InRoadNetID,
InRoadID, InSubRoadID, InStationID
[解决办法]
欧,买告滴
[解决办法]
建立相关的主键,索引,优化查询.
[解决办法]
够复杂的,那些两个连接用到的字段都可以做索引
[解决办法]
用查詢分析器看 執行計劃! 用索引微調向導 看怎麽建立索引! 這個你自己要分析的阿!!