Sqlserver 使用CTE如何按子查询排序?

最新推荐文章于 2022-05-18 20:42:39 发布

马拉森

最新推荐文章于 2022-05-18 20:42:39 发布

阅读量133

点赞数

本文针对一个特定场景下的SQL查询性能问题展开讨论，提出了通过优化SQL语句和改进查询逻辑的方法来提升性能。具体包括如何避免重复查询，如何合理使用JOIN操作，以及如何利用窗口函数提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求:查出最近有更改的客户信息(按最后更改时间排序,来自SystemLog表LogDateTime字段)

说明：

Customer:客户信息表
SystemLog:系统日志表,记录所有表信息的增,删,改

自己公司开发的行业软件,不仅自己公司有在用,其他公司也在用,本公司进行软件维护和提供升级服务
由于之前的设计客户信息表并没有更改时间字段,而现在该系统已经在N个不同的数据库里运行
导致不能随意更改数据库结构(虽然可以升级,但代价太大)

不能使用视图及存储过程只能使用Sql语句

我写的Sql及效果,虽然可以实现效果,但Sqlsrever的执行计划和IO情况很糟糕

(个人理解SystemLog查询了再次)

--客户信息按照最后更改时间排序：

 
        
             WITH 
             _temp 
             AS
             (
             SELECT
            
 
             CustomerId
            
 
             ,CompanyName
            
 
             ,(
             SELECT 
             TOP 
             1 LogDateTime 
             FROM 
             SystemLog 
             WHERE 
             LogType=
             '更改' 
             AND 
             LogTable=
             'Customer' 
             AND 
             Rid=CustomerId 
             ORDER 
             BY 
             ID 
             DESC 
             )
             AS 
             ed 
            
 
             ,ROW_NUMBER() OVER ( 
             ORDER 
             BY 
             (
             SELECT 
             TOP 
             1 LogDateTime 
             FROM 
             SystemLog 
             WHERE 
             LogType=
             '更改'
             AND 
             LogTable=
             'Customer' 
             AND 
             Rid=CustomerId 
             ORDER 
             BY 
             ID 
             DESC 
             ) 
             DESC 
             ) 
             as 
             RowNumber
            
 
             FROM 
             Customer
            
 
             WHERE 
             CustomerStatus=18 
            
 
             )
             SELECT 
             * 
             FROM 
             _temp 
             WHERE 
             RowNumber 
             BETWEEN 
             0 
             AND 
             10 
             ORDER 
             BY 
             ed 
             DESC
            
 
      

查询客户信息的系统日志

SELECT TOP 10 s.Rid AS CustomerId, CompanyName , s.LogDateTime 
FROM SystemLog s 
LEFT JOIN Customer c ON s.Rid=CustomerId 
WHERE LogType='更改'AND LogTable='Customer' 
AND c.CustomerStatus=18
ORDER BY s.id DESC

set statistics io on;

(10 行受影响)
表 'SystemLog'。扫描计数 2，逻辑读取 1697 次，物理读取 0 次，预读 7 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 155，逻辑读取 50916 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Customer'。扫描计数 1，逻辑读取 19 次，物理读取 0 次，预读 1 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(10 行受影响)
表 'Customer'。扫描计数 0，逻辑读取 157 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'SystemLog'。扫描计数 1，逻辑读取 73 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

请问各位有何优化方法？万分感谢

标签: Sql Server

绿色通道：好文要顶关注我收藏该文与我联系

HTL
关注 - 16
粉丝 - 1

+加关注

(请您对文章做出评价)

« 上一篇： C# 将Dictionary,StringDictionary等集合数据绑定到如comboBox等控件数据源中将获取健值
» 下一篇： C# DataGridView在单元格提示里(ToolTip)显示完整的单元格内容

posted @ 2014-09-17 17:07 HTL 阅读( 201) 评论( 2) 编辑收藏

评论列表

#1楼 [ 楼主] 2014-09-17 17:51 HTL

汗，人品这么差！

支持(0) 反对(0)

#2楼 2014-09-17 19:27 罗里罗嗦夫斯基

这个要从SQL语句上优化。当然，最好的方式是在customer中添加LogDateTime字段。

建议把那两个表JOIN起来，再按字段排序。

由于没有测试环境，伪代码如下（没有测试过）：

 
            WITH 
            CTE 
            AS
            (
           
            SELECT 
            *, 
           
            ROW_NUMBER() OVER ( 
            ORDER 
            BY 
            B.LogDateTime 
            DESC 
            ) 
            AS 
            RowNumber
           
            FROM 
            Customer A
           
            OUTER 
            APPLY 
            JOIN 
            (
           
            SELECT 
            TOP 
            1
           
            LogDateTime
           
            FROM   
            SystemLog
           
            WHERE  
            LogType = 
            '更改'
           
            AND 
            LogTable = 
            'Customer'
           
            AND 
            Rid = A.CustomerId
           
            ORDER 
            BY 
            ID 
            DESC
           
            ) 
            AS 
            B
           
            )
           
            SELECT  
            *
           
            FROM    
            CTE
           
            WHERE   
            RowNumber 
            BETWEEN 
            0 
            AND 
            10
           
            ORDER 
            BY 
            ed 
            DESC