SQL语句优化请教

「已注销」 2012-11-30 06:19:22

视图又包括视图。执行返回几千条数据都很慢了。。故来请教如何优化

SELECT PKID, FlowID, UrgencyLevel, TypeNumber, FileTitle, FileZiHao, FileType, CurUser, CurID, ZeRenZe, CurNodeName, Lock, Status, Url, LastUser,
LastTime, LastActionName
FROM dbo.vToDoFaWen(视视图名)
UNION
SELECT PKID, FlowID, UrgencyLevel, TypeNumber, FileTitle, FileZiHao, FileType, CurUser, CurID, ZeRenZe, CurNodeName, Lock, Status, Url, LastUser,
LastTime, LastActionName
FROM dbo.vToDoShouWen(视图名)
UNION
SELECT PKID, FlowID, UrgencyLevel, TypeNumber, FileTitle, FileZiHao, FileType, CurUser, CurID, ZeRenZe, CurNodeName, Lock, Status, Url, LastUser,
LastTime, LastActionName
FROM dbo.vToDoLianHeFaWen(视图名)
UNION
SELECT PKID, FlowID, UrgencyLevel, TypeNumber, FileTitle, FileZiHao, FileType, CurUser, CurID, ZeRenZe, CurNodeName, Lock, Status, Url, LastUser,
LastTime, LastActionName
FROM dbo.vToDoNeiFaWen(视图名) --------以上是视图

----vToDoFaWen
SELECT O.PKID, F.FlowID, U.ShowName AS UrgencyLevel, U.TypeNumber, F.Title AS FileTitle, F.FileZi + '[' + LTRIM(STR(F.FileYear))
+ ']' + LTRIM(STR(F.FileNumber)) AS FileZiHao, '发文' AS FileType, O.CurUser, O.CurID, F.Remark2 AS ZeRenZe, O.CurNodeName, O.Lock, O.Status,
'FaWen.aspx?id=' + LTRIM(STR(O.PKID)) AS Url,
(SELECT TOP (1) CurUserName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastUser,
(SELECT TOP (1) CurTreatTime
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastTime,
(SELECT TOP (1) ActionsName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastActionName
FROM dbo.FlowControl AS O INNER JOIN
dbo.FaWen AS F ON O.PKID = F.ParentID INNER JOIN
dbo.FileUngencys AS U ON U.TypeName = F.UrgencyLevel
WHERE (O.SysStatus = 1) AND (O.TypeID = 1) ----- vToDoFaWen 视图

------vToDoShouWen 视图

SELECT O.PKID, S.FlowID, U.ShowName AS UrgencyLevel, U.TypeNumber, S.Title AS FileTitle, S.FileZi AS FileZiHao, '收文' AS FileType, O.CurUser, O.CurID,
S.ComeOrg AS ZeRenZe, O.CurNodeName, O.Lock, O.Status, 'ShouWen.aspx?id=' + LTRIM(STR(O.PKID)) AS Url,
(SELECT TOP (1) CurUserName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastUser,
(SELECT TOP (1) CurTreatTime
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastTime,
(SELECT TOP (1) ActionsName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastActionName
FROM dbo.FlowControl AS O INNER JOIN
dbo.ShouWen AS S ON O.PKID = S.ParentID INNER JOIN
dbo.FileUngencys AS U ON U.TypeName = S.UrgencyLevel
WHERE (O.SysStatus = 1) ----vToDoShouWen 视图

------vToDoLianHeFaWen 视图
SELECT O.PKID, F.FlowID, U.ShowName AS UrgencyLevel, U.TypeNumber, F.Title AS FileTitle, F.FileZi + '[' + LTRIM(STR(F.FileYear))
+ ']' + LTRIM(STR(F.FileNumber)) AS FileZiHao, '联合发文' AS FileType, O.CurUser, O.CurID, F.Remark2 AS ZeRenZe, O.CurNodeName, O.Lock,
O.Status, 'LianHeFaWen.aspx?id=' + LTRIM(STR(O.PKID)) AS Url,
(SELECT TOP (1) CurUserName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastUser,
(SELECT TOP (1) CurTreatTime
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastTime,
(SELECT TOP (1) ActionsName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastActionName
FROM dbo.FlowControl AS O INNER JOIN
dbo.FaWen AS F ON O.PKID = F.ParentID INNER JOIN
dbo.FileUngencys AS U ON U.TypeName = F.UrgencyLevel
WHERE (O.SysStatus = 1) AND (O.TypeID = 2)------vToDoLianHeFaWen 视图

---vToDoNeiFaWen 视图

SELECT O.PKID, F.FlowID, U.ShowName AS UrgencyLevel, U.TypeNumber, F.Title AS FileTitle, F.FileZi + '[' + LTRIM(STR(F.FileYear))
+ ']' + LTRIM(STR(F.FileNumber)) AS FileZiHao, '内部发文' AS FileType, O.CurUser, O.CurID, F.Remark2 AS ZeRenZe, O.CurNodeName, O.Lock,
O.Status, 'NeiFaWen.aspx?id=' + LTRIM(STR(O.PKID)) AS Url,
(SELECT TOP (1) CurUserName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastUser,
(SELECT TOP (1) CurTreatTime
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastTime,
(SELECT TOP (1) ActionsName
FROM dbo.FlowConceit AS N
WHERE (O.PKID = ParentID)
ORDER BY PKID DESC) AS LastActionName
FROM dbo.FlowControl AS O INNER JOIN
dbo.FaWen AS F ON O.PKID = F.ParentID INNER JOIN
dbo.FileUngencys AS U ON U.TypeName = F.UrgencyLevel
WHERE (O.SysStatus = 1) AND (O.TypeID = 3)

...全文