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

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   ‘  
     AND   采集时间 <=  ‘  2014-1-11   ‘ 
     --  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   ‘   AND  采集时间 <=  ‘  2014-1-11   ‘  
) 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   ‘   AND   ‘  2014-1-11   ‘ 
 drop   table  #table2

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

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

解决方法，在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   ‘  
     AND   采集时间 <  ‘  2013-11-7   ‘  
     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   ‘  
     AND   采集时间 <  ‘  2013-11-7   ‘ 
     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

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

标签：

查看更多关于SQL Server性能优化（3）使用SQL Server Profiler查询性能瓶颈的详细内容...