首页 新闻 赞助 找找看

两个查询,执行计划一样,但是查询时间相差很多

0
[已关闭问题] 关闭于 2016-05-30 10:27

我现在有一个查询如下,把CAST(OB.OrderExtendedXml AS VARCHAR(MAX)) OrderExtendedXml去掉之后,时间从7秒降到了1秒,但是我看执行计划,两个是一模一样的,而且查询消耗也各占50% ,这是为啥啊~~~
还有一个疑问,键查找预估是1行(主键),实际是2W多行,这个可以解决么?这一个查询就占了41%(执行计划太长了,截了一部分)

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY tempcolumn ) temprownumber, *
          FROM      ( SELECT TOP 15
                                tempcolumn = 0, CAST(OB.OrderExtendedXml AS VARCHAR(MAX)) OrderExtendedXml, CASE WHEN DicDestionation.CountryId = 0 THEN DicDestionation.ProvinceName
                                                                                                                 ELSE DicDestionation.CountryName
                                                                                                            END AS Destinations, PB.LineType, MAX(Dis.ProvinceName) + '-' + MAX(Dis.CityName) + '-' + MAX(Dis.DistrictName) AS AgencyDistrict, OB.RedPackageId, OB.StatusModifyDate, OB.SubmitDate, OB.ConfirmDate, OB.FinishVerifyDate, OB.VerifyOrderDate, g.Username AS PMUsername, g.RealName AS PMname, OB.DockingPersonName, OB.DockingPersonContact, OB.OrderAmount, OB.AgencyOrderCode, DicDestionation.CityName AS Destination, RS.MainPart AS SubstationInstanceName, RS.Name AS SubstationName, OB.AgencyId, OB.SupplierId, PB.Id AS ProductId, OB.IsFromPC, OB.IsRemainingSale, OB.GroupNotification, OB.Id, PB.ProductName, PB.PlayDays, PB.CoverPhoto, PB.ProductCode, OB.IsPrint, OB.OrderCode, OB.OrderDate, OB.GroupCode, RS2.DicName AS PreStatusStr, OB.VerifyStatus, MP.PlatformRuleName PaymentMethod, OB.PreStatus, OB.IsCanceled, RS3.DicName AS VerifyStatusStr, CancelEnterpriseType, RS4.DicName AS ConfirmStatusStr, ConfirmStatus, Supplier.EnterpriseName AS SuppliersName, Agent.EnterpriseName AS AgencyName, AgencyContacter.QQNum AS AgentQQ, SupplierContacter.QQNum AS SuppliersQQ, e.RealName AS SupplierDockingPersonName, e.Username AS SupplierDockingPersonUserName, e.CellPhone AS SuppliersCellPhone, RS6.DicName AS RecieptStatusStr, RS5.DicName AS PaymentStatusStr, CASE WHEN OB.RecieptRuleId <= 0 THEN '月结'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       ELSE MP1.PlatformRuleName
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  END AS RecieptMethod
                      FROM      Order_BasicInfo OB WITH ( NOLOCK )
                                LEFT JOIN Product_BasicInfo PB WITH ( NOLOCK ) ON OB.ProductId = PB.Id
                                                                                  AND PB.LineClassification = 0
                                LEFT JOIN Marketing_PaymentRules MP WITH ( NOLOCK ) ON OB.PaymentRuleId = MP.id
                                LEFT JOIN Resource_SysDictionary RS2 WITH ( NOLOCK ) ON OB.PreStatus = RS2.DicValue
                                                                                        AND RS2.DicType = '订单父状态'
                                                                                        AND RS2.IsDelete = 0
                                LEFT JOIN Account_UserInfo SupplierContacter WITH ( NOLOCK ) ON PB.DockingPersonnelId = SupplierContacter.Id
                                LEFT JOIN Account_UserInfo AgencyContacter WITH ( NOLOCK ) ON OB.Creator = AgencyContacter.Id
                                LEFT JOIN Account_UserInfo g WITH ( NOLOCK ) ON g.Id = PB.ProductManagerId
                                LEFT JOIN Account_UserInfo e WITH ( NOLOCK ) ON e.Id = PB.DockingPersonnelId
                                LEFT JOIN Order_TouristInfo OT WITH ( NOLOCK ) ON OT.OrderId = OB.Id
                                LEFT JOIN Resource_SysDictionary RS3 WITH ( NOLOCK ) ON OB.VerifyStatus = RS3.DicValue
                                                                                        AND RS3.DicType = '双方确认状态'
                                                                                        AND RS3.IsDelete = 0
                                LEFT JOIN Order_CancelOrderExtention OC WITH ( NOLOCK ) ON OB.Id = OC.OrderId
                                LEFT JOIN Resource_SysDictionary RS4 WITH ( NOLOCK ) ON OC.ConfirmStatus = RS4.DicValue
                                                                                        AND RS4.DicType = '双方确认状态'
                                                                                        AND RS4.IsDelete = 0
                                LEFT JOIN Enterprise_BaseInfo Supplier WITH ( NOLOCK ) ON Supplier.Id = OB.SupplierId
                                LEFT JOIN Enterprise_BaseInfo Agent WITH ( NOLOCK ) ON Agent.Id = OB.AgencyId
                                LEFT JOIN Resource_District Dis WITH ( NOLOCK ) ON Dis.DistrictId = Agent.DistrictId
                                LEFT JOIN Resource_SubstationInfo RS WITH ( NOLOCK ) ON OB.SubstationId = RS.Id
                                LEFT JOIN Resource_City DicDestionation WITH ( NOLOCK ) ON DicDestionation.CityId = PB.DestinationCityId
                                LEFT JOIN Account_UserInfo AUS WITH ( NOLOCK ) ON AUS.Id = PB.ProductManagerId
                                LEFT JOIN Resource_SysDictionary RS6 WITH ( NOLOCK ) ON OB.ReceivableStatus = RS6.DicValue
                                                                                        AND RS6.DicType = '订单收款状态'
                                                                                        AND RS6.IsDelete = 0
                                LEFT JOIN Resource_SysDictionary RS5 WITH ( NOLOCK ) ON OB.PaymentStatus = RS5.DicValue
                                                                                        AND RS5.DicType = '订单付款状态'
                                                                                        AND RS5.IsDelete = 0
                                LEFT JOIN Marketing_PaymentRules MP1 WITH ( NOLOCK ) ON OB.RecieptRuleId = MP1.id
                      WHERE     OB.PreStatus != 1
                                AND PB.LineClassification = 0
                                AND OB.IsDelete = 0
                                AND IsCanceled = 0
                                AND PB.LineType = 0
                                AND ( EXISTS ( SELECT   NULL
                                               FROM     dbo.Sys_DataAuthority aa WITH ( NOLOCK )
                                               WHERE    aa.UserId = 4073
                                                        AND aa.IsDelete = 0
                                                        AND PB.LineClassification = aa.LineClassifcation
                                                        AND aa.SubstationId = OB.SubstationId
                                                        AND ( aa.DestinationId = 0
                                                              OR ( aa.DestinationType = 2
                                                                   AND PB.DestinationCityId = aa.DestinationId
                                                                 )
                                                            ) )
                           
                                    )
                      GROUP BY  CAST(OB.OrderExtendedXml AS VARCHAR(MAX)), CASE WHEN DicDestionation.CountryId = 0 THEN DicDestionation.ProvinceName
                                                                                ELSE DicDestionation.CountryName
                                                                           END, PB.LineType, OC.CancelDate, Agent.EnterpriseLocation, OB.RedPackageId, e.Username, e.RealName, e.CellPhone, OB.StatusModifyDate, OB.SubmitDate, OB.ConfirmDate, OB.FinishVerifyDate, OB.VerifyOrderDate, g.Username, g.RealName, OB.DockingPersonName, OB.DockingPersonContact, OB.OrderAmount, OB.AgencyOrderCode, DicDestionation.CityName, RS.MainPart, RS.Name, OB.AgencyId, OB.SupplierId, PB.Id, OB.Id, OB.IsFromPC, OB.Id, PB.ProductName, PB.PlayDays, PB.CoverPhoto, PB.ProductCode, OB.VerifyStatus, OB.IsPrint, OB.OrderCode, OB.OrderDate, OB.GroupCode, RS2.DicName, MP.PlatformRuleName, OB.PreStatus, OB.IsCanceled, RS3.DicName, OB.IsRemainingSale, OB.GroupNotification, CancelEnterpriseType, RS4.DicName, ConfirmStatus, Supplier.EnterpriseName, Agent.EnterpriseName, AgencyContacter.QQNum, SupplierContacter.QQNum, RS6.DicName, RS5.DicName, CASE WHEN OB.RecieptRuleId <= 0 THEN '月结'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           ELSE MP1.PlatformRuleName
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      END
                      ORDER BY  OB.StatusModifyDate DESC
                    ) t
        ) tt
WHERE   temprownumber > 0
ORDER BY temprownumber;

二洋的主页 二洋 | 菜鸟二级 | 园豆:267
提问于:2016-05-24 10:58
< >
分享
所有回答(2)
0

看io,read统计信息

czd890 | 园豆:14292 (专家六级) | 2016-05-24 12:17

也是一样的哦

支持(0) 反对(0) 二洋 | 园豆:267 (菜鸟二级) | 2016-05-24 13:13

@二洋: 是本地还是服务器,在看下客户端统计信息。是不是xml字段太大,导致网络传输慢了

支持(0) 反对(0) czd890 | 园豆:14292 (专家六级) | 2016-05-24 13:29

@calvinK: 用远程桌面在服务器上跑的,看IO read都是一样的,执行计划也一样,就是时间不一样,真的是好奇怪

支持(0) 反对(0) 二洋 | 园豆:267 (菜鸟二级) | 2016-05-24 13:31

@二洋: 客户端统计信息也一样么

支持(0) 反对(0) czd890 | 园豆:14292 (专家六级) | 2016-05-24 14:20

@calvinK: 同一个查询分析器里面执行的呀   怎么会不一样~

支持(0) 反对(0) 二洋 | 园豆:267 (菜鸟二级) | 2016-05-24 14:22
0

既然其他一样,多花的时间不就是cast消耗的时间吗

wsh3166Sir | 园豆:202 (菜鸟二级) | 2024-01-07 09:50
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册