这个视图只要count(1)就很慢，同时CPU也会很高，需要怎么样来写会快些
ALTER VIEW [dbo].[V_SynthesizeAsyn]
AS
SELECT a.Sysnumber ,
a.OrderId ,
a.OrderTime ,
a.MemberId ,
a.AvType ,
a.AvObject ,
a.AvDescription ,
CASE a.AvObject
WHEN 'gs' THEN '金牌供应商'
WHEN 'etp' THEN '企业诚信通'
WHEN 'itp' THEN '海外TP'
WHEN 'zg' THEN '中供'
ELSE a.AvObject
END AS AvObjectCn ,
a.CompanyNameCn ,
a.MemberNameCn ,
a.DangerDetail ,
a.IfRenew ,
CASE a.IfRenew
WHEN 'y' THEN '是'
ELSE '否'
END AS IfRenewCn ,
b.WarningGrade ,
d.CompanyProvince ,
d.CompanyProvince AS Province ,
d.CompanyCity ,
d.CompanyCity AS city ,
rs.Name AS CompanyCityCN ,
Ps.Name AS CompanyProvinceCN ,
b.CurrentNodeID ,
b.CurrentNodeName ,
b.IfProveCheck ,
b.IfBusiCheck ,
b.IfRenew_Again ,
CASE b.IfRenew_Again
WHEN 'y' THEN '是'
ELSE '否'
END AS IfRenew_AgainCn ,
b.IsFeedBack ,
CASE b.IsFeedBack
WHEN '1' THEN '是'
ELSE '否'
END AS IsFeedBackCn ,
b.FeedBackTime ,
e.Operator ,
d.CompanyName ,
e.OperatorName ,
d.CeoName ,
d.Tel ,
f.Result ,
( CASE UPPER(ISNULL(f.Result, ''))
WHEN 'YES' THEN '完全正确'
WHEN 'NO' THEN '完全不正确'
WHEN 'OTHER' THEN '部分正确'
ELSE ''
END ) AS Result_Name ,
b.ErrorDot ,
a.MakerID ,
a.Maker ,
d.CompanyAddr ,
d.LicenseNumber ,
b.IfTranslate ,
b.TranslateAudit ,
f.Content ,
d.CompanyEntityType ,
z.Name AS EntityTypeName ,
y.USERNAME ,
x.Maker AS LastVerify ,
a.VeriScope ,
a.ExpireDate ,
a.Priority
FROM dbo.T_SendAVInfo AS a WITH ( NOLOCK )
INNER JOIN dbo.T_OrderStateInfo AS b WITH ( NOLOCK ) ON a.Sysnumber = b.SendAVInfoSysnumber
AND b.IfRemove = '0'
LEFT JOIN dbo.T_DataCollectInfo AS d WITH ( NOLOCK ) ON a.Sysnumber = d.SendAVInfoSysnumber
LEFT JOIN dbo.T_DataDispense AS e WITH ( NOLOCK ) ON e.OperateState = '1'
AND a.Sysnumber = e.SendAVInfoSysnumber
LEFT JOIN dbo.T_SideVerifyRecords AS f WITH ( NOLOCK ) ON a.Sysnumber = f.SendAVInfoSysnumber
LEFT JOIN ( SELECT Name ,
Number
FROM dbo.Base_DDTree WITH ( NOLOCK )
WHERE ( PareNumber = 'ST0906100004' )
) AS z ON z.Number = d.CompanyEntityType
LEFT JOIN ( ( SELECT Name ,
Number
FROM dbo.Base_DDTree
WHERE DataType = 'hzst_tree_area'
AND DELETEED = '0'
AND IfLeaf = 0
) ) AS rs ON rs.Number = d.CompanyCity
LEFT JOIN ( ( SELECT Name ,
Number
FROM dbo.Base_DDTree
WHERE DataType = 'hzst_tree_area'
AND DELETEED = '0'
) ) AS Ps ON Ps.Number = d.CompanyProvince
LEFT JOIN ( SELECT USERID ,
USERNAME
FROM dbo.T_IA_USERS WITH ( NOLOCK )
) AS y ON e.Operator = y.USERID
--获取审核人
LEFT JOIN ( SELECT loga.SendAVInfoSysnumber ,
logb.Maker
FROM ( SELECT SendAVInfoSysnumber ,
MAX(CreateTime) AS CreateTime
FROM dbo.T_Operationlog WITH ( NOLOCK )
WHERE CurrentNodeID = '111' AND T_Operationlog.SendAVInfoSysnumber='111'
GROUP BY SendAVInfoSysnumber
) loga
LEFT JOIN ( SELECT SendAVInfoSysnumber ,
CreateTime ,
Maker ,
CurrentNodeID
FROM dbo.T_Operationlog WITH ( NOLOCK )
) logb ON loga.SendAVInfoSysnumber = logb.SendAVInfoSysnumber
AND loga.CreateTime = logb.CreateTime
) x ON a.Sysnumber = x.SendAVInfoSysnumber;
LEFT JOIN ( SELECT TOP 1
SendAVInfoSysnumber ,
Maker
FROM T_Operationlog
ORDER BY CreateTime DESC
) x ON a.Sysnumber = x.SendAVInfoSysnumber;
GO
SQL Server 分析和编译时间:
CPU 时间 = 3656 毫秒，占用时间 = 3691 毫秒。
(1 行受影响)
表 'Base_DDTree'。扫描计数 19，逻辑读取 22 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T_OrderStateInfo'。扫描计数 9，逻辑读取 6138 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T_SendAVInfo'。扫描计数 9，逻辑读取 4747 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T_DataCollectInfo'。扫描计数 9，逻辑读取 24325 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T_DataDispense'。扫描计数 9，逻辑读取 5456 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'T_SideVerifyRecords'。扫描计数 9，逻辑读取 5960 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 25026 毫秒，占用时间 = 3613 毫秒。
