SQL Server性能优化（3）使用SQL Server Profiler查询性能瓶颈

浏览数：31 / 时间：2015年06月12日

关于SQL Server Profiler的使用，网上已经有很多教程，比如这一篇文章：SQL Server Profiler：使用方法和指标说明。微软官方文档：https://msdn.microsoft.com/zh-cn/library/ms179428(v=sql.105).aspx有更详细的介绍。

经过使用Profiler进行监视，得到监视结果。

==================================以下是和文章标题无关的内容，举例说明优化语句=========================================

一个查询耗时非常大的语句

select t.表地址,t.当前热量,t.热功率,t.瞬时流量,t.累计流量,t.供水温度,t.回水温度,t.温差,t.累计工作时间,t.采集时间,t.社区编号,t.楼房编号,t.房间号
from measure_heat t
where 采集时间 = (select max(采集时间) from measure_heat where 表地址 = t.表地址 and 采集时间 >= ‘2013-11-11‘  and 采集时间 <= ‘2014-4-11‘)

这是一个用max函数查询大范围数据的语句。经过查询计划发现，采集时间列没有索引，导致查询时会有扫描表的操作。后来给采集时间列加上索引，再采用不同的查询语句进行分析。以下是分析结果

--如果查询很大范围的数据库，发现使用max函数是效率最高的，其他的排序函数效率一般。
--因为采集时间是有序的
------------------------使用max函数--------------------------
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS    --关闭缓存
DBCC FREEPROCCACHE        --关闭缓存
SELECT t.表地址, t.当前热量, t.热功率, t.瞬时流量, t.累计流量, 
    t.供水温度, t.回水温度, t.温差, t.累计工作时间, t.采集时间,
    t.社区编号,t.楼房编号,t.房间号 
FROM Measure_heat t 
WHERE 采集时间 = 
    (
    ---很多时间花在这个地方，也就是如何能获得一个表的最近的采集时间。
    select max(采集时间)     
    from measure_heat 
    where 表地址 = t.表地址 
    and 采集时间 >= ‘2013-11-11‘  
    and 采集时间 <= ‘2014-1-11‘
    )    

---------------------使用ROW_NUMBER函数-----------------------
--create nonclustered index testMeasure_heat on Measure_heat  (采集时间, 表地址)
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS    --关闭缓存
DBCC FREEPROCCACHE        --关闭缓存
--如果存在，删除缓存表
--IF exists(SELECT * FROM #TableID)
    
--存入数据库内存表
SELECT * INTO #TableID FROM 
(
    SELECT ROW_NUMBER() OVER (PARTITION BY 房间号 ORDER BY 采集时间 DESC) as rowID, id  
    FROM Measure_heat 
    WHERE 采集时间>=‘2013-11-1 0:00:00‘ 
    AND  采集时间<=‘2014-1-11 0:00:00‘
    --AND 房间号 = 119    
) T
WHERE T.rowID = 1
SELECT * FROM #TableID
SELECT t.表地址, t.当前热量, t.热功率, t.瞬时流量, t.累计流量, 
    t.供水温度, t.回水温度, t.温差, t.累计工作时间, t.采集时间,
    t.社区编号,t.楼房编号,t.房间号 
FROM  Measure_heat T RIGHT JOIN #TableID b ON t.id = b.id
ORDER BY t.id
DROP table #TableID

------------------------使用rank函数----------------------
DBCC DROPCLEANBUFFERS    --关闭缓存
DBCC FREEPROCCACHE        --关闭缓存
select * into #table2 from 
(
    SELECT 表地址, RANK() OVER (PARTITION BY 表地址 ORDER BY 采集时间) AS RankTest
    FROM Measure_heat
    WHERE 采集时间>=‘2013-11-1 0:00:00‘ AND 采集时间<=‘2014-1-11 0:00:00‘
) t
where ranktest = 1
--SELECT * FROM #table2
SELECT t.表地址, t.当前热量, t.热功率, t.瞬时流量, t.累计流量, 
    t.供水温度, t.回水温度, t.温差, t.累计工作时间, t.采集时间,
    t.社区编号,t.楼房编号,t.房间号 
FROM  Measure_heat T RIGHT JOIN  #table2 a on a.表地址 = t.表地址
AND T.采集时间 BETWEEN ‘2013-11-1 0:00:00‘ AND ‘2014-1-11 0:00:00‘
drop table #table2

另一个使用Min函数的语句，性能也非常差

select t.* 
from  Measure_heat t 
where 社区编号=‘13‘ and 采集时间 =(select min(采集时间 ) from  Measure_heat where 房间号 = t.房间号 AND 采集时间>=‘2013-11-6 0:00:00‘ AND  采集时间<‘2013-11-7 0:00:00‘ )

解决方法，在sql中建立内存表，先查出一部分，再利用第一部分的结果查询最终结果。最终可以秒查。

SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS    --关闭缓存
DBCC FREEPROCCACHE        --关闭缓存
SELECT t.*
FROM  Measure_heat t 
WHERE 社区编号=‘13‘ and 采集时间 =
(
    SELECT min(采集时间 ) 
    FROM  Measure_heat 
    where 
    采集时间>=‘2013-11-6 0:00:00‘ 
    AND  采集时间<‘2013-11-7 0:00:00‘ 
    AND 房间号 = t.房间号 
)
ORDER BY t.id

------------使用内存表------------
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS    --关闭缓存
DBCC FREEPROCCACHE        --关闭缓存
--如果存在，删除缓存表
--IF exists(SELECT * FROM #TableID)
DROP table #TableID

--存入数据库内存表
SELECT * INTO #TableID FROM 
(
    SELECT ROW_NUMBER() OVER (PARTITION BY 房间号 ORDER BY 采集时间 desc) as rowID, id  
    FROM Measure_heat 
    WHERE 采集时间>=‘2013-11-6 0:00:00‘ 
    AND  采集时间<‘2013-11-7 0:00:00‘
    AND 社区编号=‘13‘    
) T
WHERE T.rowID = 1
--SELECT * FROM #TableID
SELECT a.*
FROM  Measure_heat a RIGHT JOIN #TableID b ON a.id = b.id
ORDER BY A.id