20

mssql 视图语句优化 很慢

这个视图只要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 毫秒。

查看全部
senlin20
csdn_senlin
2015/09/19 13:59
  • 优化
  • cpu
  • sql
  • 点赞
  • 收藏
  • 回答
    私信

4个回复